Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mmayer
Frequent Visitor

Unpivot Multiple Sections of Data

I used this solution to attempt to unpivot four (4) sections of data instead of two (2) as in the example. I end up getting an error message saying that "Merge2" doesn't exist. Can anyone clarify where I'm going wrong? (See Screenshot of code below)

 

Query1.png

7 REPLIES 7
v-chuncz-msft
Community Support
Community Support

@mmayer,

 

You may also use SELECTCOLUMNS Function (DAX) and UNION Function to add a calculated table.

https://community.powerbi.com/t5/Desktop/Column-Header-names-should-contain-current-month-dynamicall...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Sam,

 

I’ve actually already built the table I’m after using DAX. However, the next step would be to get BI's word cloud to break on the commas within the Key Word column, which doesn’t seem possible. I therefore need to add rows that duplicate all other columns for each new key word, which isn’t possible in DAX. SO I’m trying to redo all the work in M in order to take the next step of breaking out the key words into multiple rows – though I am worried that that will somehow blow-up my data and I’ll have to figure out some min or max fixed calculation in order to have my other charts remain as they are.

 

So in short, I need to figure out what's wrong with the M code I shared previously

 

 

ImkeF
Super User
Super User

Hi @mmayer, I'd recommend to use this function instead: https://www.thebiccountant.com/2017/06/19/unpivot-by-number-of-columns-and-rows-in-powerbi-and-power...

 

If you still get errors, please paste code as text and not as image.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

mmayer
Frequent Visitor

It looks like I cannot access the link supplied from my office (it's likely blocked).

 

If it helps below is sample before and after, and I'll follow up with my code in chunks as it exceeds the character limits:

 

Before

SampleRaw2.PNG

 

After

CleanedUp3.PNG

 

mmayer
Frequent Visitor

let
    Source = Csv.Document(File.Contents("U:\OSD\Survey\Results\CSV\OperationsServicesDepartmentCustomerSurvey12312018_002.csv"),[Delimiter=",", Columns=63, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Source2 = Csv.Document(File.Contents("U:\OSD\Survey\Results\CSV\OperationsServicesDepartmentCustomerSurvey12312018_002.csv"),[Delimiter=",", Columns=63, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Source3 = Csv.Document(File.Contents("U:\OSD\Survey\Results\CSV\OperationsServicesDepartmentCustomerSurvey12312018_002.csv"),[Delimiter=",", Columns=63, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Source4 = Csv.Document(File.Contents("U:\OSD\Survey\Results\CSV\OperationsServicesDepartmentCustomerSurvey12312018_002.csv"),[Delimiter=",", Columns=63, Encoding=1252, QuoteStyle=QuoteStyle.None]),

    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Respondent ID", type number}, {"Collector ID", Int64.Type}, {"Start Date", type datetime}, {"End Date", type datetime}, {"IP Address", type text}, {"Email Address", type text}, {"First Name", type text}, {"Last Name", type text}, {"Custom Data 1", type text}, {"How often do you use PA fuel sites and/or PA fuel cards at retail gas stations?", type text}, {"Please rate the quality of your fueling experience(s)", Int64.Type}, {"Fuel Open Ended", type text}, {"Fuel Quality Key Words", type text}, {"How often do you use PA auto shops for vehicle/equipment repairs and maintenance?", type text}, {"Please rate the quality of your vehicle maintenance and repair experience(s)", Int64.Type}, {"Auto Shop Open Ended", type text}, {"Auto Shop Key Words", type text}, {"How often do you request SEMAC construction support (repairs, plumbing, paving, painting, etc.)?", type text}, {"Please rate the quality of your SEMAC construction support experience(s)", Int64.Type}, {"SEMAC Open Ended", type text}, {"SEMAC Key Words", type text}, {"How often do you request COMSEG (Radio Shop) support for radio communication equipment?", type text}, {"Please rate the quality of your COMSEG (Radio Shop) support experience(s)", Int64.Type}, {"COMSEG Open Ended", type text}, {"COMSEG Key Words", type text}, {"How often do you request roadway signage, pavement markings or delineators from the Sign Shop?", type text}, {"Please rate the quality of your experience(s) with the Sign Shop's services", Int64.Type}, {"Sign Shop Open Ended", type text}, {"Sign Shop Key Words", type text}, {"How often do you request graphics and creative design work?", type text}, {"Please rate the quality of your experience(s) with graphic and creative design", Int64.Type}, {"Creative Design Open Ended", type text}, {"Creative Design Key Words", type text}, {"How often do you request printed materials from the print shop?", type text}, {"Please rate the quality of your experience(s) with the Print Shop", Int64.Type}, {"Print Shop Open Ended", type text}, {"Print Shop Key Words", type text}, {"How often do you use PA's internal mail services or external mail services via a PA mail center?", type text}, {"Please rate the quality of your PA mail center experience(s)", Int64.Type}, {"PA Mail Open Ended", type text}, {"PA Mail Key Words", type text}, {"How often do you dine at a PA cafeteria (PATC, HT, LT or GWB)?", type text}, {"Please rate the quality of your PA cafeteria dining experience(s)", Int64.Type}, {"PA Cafeteria Open Ended", type text}, {"PA Cafeteria Key Words", type text}, {"How often do you participate in safety coordinator meetings?", type text}, {"Please rate the quality of your safety coordinator meeting experience(s)", Int64.Type}, {"Safety Coordinator Open Ended", type text}, {"Safety Coordinator Key Words", type text}, {"How often have you received OSHA or other safety training?", type text}, {"Please rate the quality of your OSHA and/or other safety training experience(s)", Int64.Type}, {"Safety Training Open Ended", type text}, {"Safety Training Key Words", type text}, {"How often have you relocated to a different office space and/or requested new furniture?", type text}, {"Please rate the quality of your relocation and/or new furniture acquisition experience(s)", Int64.Type}, {"Office Move Open Ended", type text}, {"Office Move Key Words", type text}, {"How often have you requested an office heating/cooling, and/or pest or plumbing issue be addressed?", type text}, {"Please rate the quality of your experience(s) with heating/cooling, and/or pest or plumbing issues", Int64.Type}, {"Office Issue Open Ended", type text}, {"Office Issue Key Words", type text}, {"", type text}, {"Are there any other services or issues that you feel need improvement?", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"How often do you use PA fuel sites and/or PA fuel cards at retail gas stations?", "Fuel sites/card frequency"}, {"Please rate the quality of your fueling experience(s)", "Fueling Quality"}, {"Fuel Quality Key Words", "Fuel Key Words"}, {"How often do you use PA auto shops for vehicle/equipment repairs and maintenance?", "Auto Shop Frequency"}, {"Please rate the quality of your vehicle maintenance and repair experience(s)", "Auto Shop Quality"}, {"Are there any other services or issues that you feel need improvement?", "Comments"}, {"How often do you request SEMAC construction support (repairs, plumbing, paving, painting, etc.)?", "SEMAC Frequency"}, {"Please rate the quality of your SEMAC construction support experience(s)", "SEMAC Quality"}, {"How often do you request COMSEG (Radio Shop) support for radio communication equipment?", "COMSEG Frequency"}, {"Please rate the quality of your COMSEG (Radio Shop) support experience(s)", "COMSEG Quality"}, {"How often do you request roadway signage, pavement markings or delineators from the Sign Shop?", "Sign Shop Frequency"}, {"Please rate the quality of your experience(s) with the Sign Shop's services", "Sign Shop Quality"}, {"How often do you request graphics and creative design work?", "Creative Frequency"}, {"Please rate the quality of your experience(s) with graphic and creative design", "Creative Quality"}, {"How often do you request printed materials from the print shop?", "Print Shop Frequency"}, {"Please rate the quality of your experience(s) with the Print Shop", "Print Shop Quality"}, {"How often do you use PA's internal mail services or external mail services via a PA mail center?", "PA Mail Frequency"}, {"Please rate the quality of your PA mail center experience(s)", "PA Mail Quality"}, {"How often do you dine at a PA cafeteria (PATC, HT, LT or GWB)?", "PA Cafeteria Frequency"}, {"Please rate the quality of your PA cafeteria dining experience(s)", "PA Cafeteria Quality"}, {"How often do you participate in safety coordinator meetings?", "Safety Coordinator Frequency"}, {"Please rate the quality of your safety coordinator meeting experience(s)", "Safety Coordinator Quality"}, {"How often have you received OSHA or other safety training?", "Safety Training Frequency"}, {"Please rate the quality of your OSHA and/or other safety training experience(s)", "Safety Training Quality"}, {"How often have you relocated to a different office space and/or requested new furniture?", "Office Move Frequency"}, {"Please rate the quality of your relocation and/or new furniture acquisition experience(s)", "Office Move Quality"}, {"How often have you requested an office heating/cooling, and/or pest or plumbing issue be addressed?", "Office Issues Frequency"}, {"Please rate the quality of your experience(s) with heating/cooling, and/or pest or plumbing issues", "Office Issue Quality"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns", {"Fueling Quality", "Auto Shop Quality", "SEMAC Quality", "COMSEG Quality","Sign Shop Quality","Creative Quality","Print Shop Quality","PA Mail Quality","PA Cafeteria Quality","Safety Coordinator Quality", "Safety Training Quality","Office Move Quality","Office Issue Quality","Fuel Open Ended","Auto Shop Open Ended","SEMAC Open Ended","COMSEG Open Ended","Sign Shop Open Ended","Creative Design Open Ended","Print Shop Open Ended","PA Mail Open Ended","PA Cafeteria Open Ended","Safety Coordinator Open Ended","Safety Training Open Ended","Office Move Open Ended","Office Issue Open Ended","Fuel Key Words" ,"Auto Shop Key Words","SEMAC Key Words","COMSEG Key Words","Sign Shop Key Words","Creative Design Key Words","Print Shop Key Words","PA Mail Key Words","PA Cafeteria Key Words","Safety Coordinator Key Words","Safety Training Key Words","Office Move Key Words","Office Issue Key Words","Collector ID","Start Date","End Date","IP Address","Email Address","First Name","Last Name","Custom Data 1"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Respondent ID", "Comments"}, "Attribute", "Value"),
    #"Changed Type01" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Respondent ID", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type01", "Merge", each [Respondent ID] & [Comments] & List.First(Text.Split([Attribute],"_"))),

    #"Promoted Headers2" = Table.PromoteHeaders(Source2, [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers2",{{"Respondent ID", type number}, {"Collector ID", Int64.Type}, {"Start Date", type datetime}, {"End Date", type datetime}, {"IP Address", type text}, {"Email Address", type text}, {"First Name", type text}, {"Last Name", type text}, {"Custom Data 1", type text}, {"How often do you use PA fuel sites and/or PA fuel cards at retail gas stations?", type text}, {"Please rate the quality of your fueling experience(s)", Int64.Type}, {"Fuel Open Ended", type text}, {"Fuel Quality Key Words", type text}, {"How often do you use PA auto shops for vehicle/equipment repairs and maintenance?", type text}, {"Please rate the quality of your vehicle maintenance and repair experience(s)", Int64.Type}, {"Auto Shop Open Ended", type text}, {"Auto Shop Key Words", type text}, {"How often do you request SEMAC construction support (repairs, plumbing, paving, painting, etc.)?", type text}, {"Please rate the quality of your SEMAC construction support experience(s)", Int64.Type}, {"SEMAC Open Ended", type text}, {"SEMAC Key Words", type text}, {"How often do you request COMSEG (Radio Shop) support for radio communication equipment?", type text}, {"Please rate the quality of your COMSEG (Radio Shop) support experience(s)", Int64.Type}, {"COMSEG Open Ended", type text}, {"COMSEG Key Words", type text}, {"How often do you request roadway signage, pavement markings or delineators from the Sign Shop?", type text}, {"Please rate the quality of your experience(s) with the Sign Shop's services", Int64.Type}, {"Sign Shop Open Ended", type text}, {"Sign Shop Key Words", type text}, {"How often do you request graphics and creative design work?", type text}, {"Please rate the quality of your experience(s) with graphic and creative design", Int64.Type}, {"Creative Design Open Ended", type text}, {"Creative Design Key Words", type text}, {"How often do you request printed materials from the print shop?", type text}, {"Please rate the quality of your experience(s) with the Print Shop", Int64.Type}, {"Print Shop Open Ended", type text}, {"Print Shop Key Words", type text}, {"How often do you use PA's internal mail services or external mail services via a PA mail center?", type text}, {"Please rate the quality of your PA mail center experience(s)", Int64.Type}, {"PA Mail Open Ended", type text}, {"PA Mail Key Words", type text}, {"How often do you dine at a PA cafeteria (PATC, HT, LT or GWB)?", type text}, {"Please rate the quality of your PA cafeteria dining experience(s)", Int64.Type}, {"PA Cafeteria Open Ended", type text}, {"PA Cafeteria Key Words", type text}, {"How often do you participate in safety coordinator meetings?", type text}, {"Please rate the quality of your safety coordinator meeting experience(s)", Int64.Type}, {"Safety Coordinator Open Ended", type text}, {"Safety Coordinator Key Words", type text}, {"How often have you received OSHA or other safety training?", type text}, {"Please rate the quality of your OSHA and/or other safety training experience(s)", Int64.Type}, {"Safety Training Open Ended", type text}, {"Safety Training Key Words", type text}, {"How often have you relocated to a different office space and/or requested new furniture?", type text}, {"Please rate the quality of your relocation and/or new furniture acquisition experience(s)", Int64.Type}, {"Office Move Open Ended", type text}, {"Office Move Key Words", type text}, {"How often have you requested an office heating/cooling, and/or pest or plumbing issue be addressed?", type text}, {"Please rate the quality of your experience(s) with heating/cooling, and/or pest or plumbing issues", Int64.Type}, {"Office Issue Open Ended", type text}, {"Office Issue Key Words", type text}, {"", type text}, {"Are there any other services or issues that you feel need improvement?", type text}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type2",{{"How often do you use PA fuel sites and/or PA fuel cards at retail gas stations?", "Fuel sites/card frequency"}, {"Please rate the quality of your fueling experience(s)", "Fueling Quality"}, {"Fuel Quality Key Words", "Fuel Key Words"}, {"How often do you use PA auto shops for vehicle/equipment repairs and maintenance?", "Auto Shop Frequency"}, {"Please rate the quality of your vehicle maintenance and repair experience(s)", "Auto Shop Quality"}, {"Are there any other services or issues that you feel need improvement?", "Comments"}, {"How often do you request SEMAC construction support (repairs, plumbing, paving, painting, etc.)?", "SEMAC Frequency"}, {"Please rate the quality of your SEMAC construction support experience(s)", "SEMAC Quality"}, {"How often do you request COMSEG (Radio Shop) support for radio communication equipment?", "COMSEG Frequency"}, {"Please rate the quality of your COMSEG (Radio Shop) support experience(s)", "COMSEG Quality"}, {"How often do you request roadway signage, pavement markings or delineators from the Sign Shop?", "Sign Shop Frequency"}, {"Please rate the quality of your experience(s) with the Sign Shop's services", "Sign Shop Quality"}, {"How often do you request graphics and creative design work?", "Creative Frequency"}, {"Please rate the quality of your experience(s) with graphic and creative design", "Creative Quality"}, {"How often do you request printed materials from the print shop?", "Print Shop Frequency"}, {"Please rate the quality of your experience(s) with the Print Shop", "Print Shop Quality"}, {"How often do you use PA's internal mail services or external mail services via a PA mail center?", "PA Mail Frequency"}, {"Please rate the quality of your PA mail center experience(s)", "PA Mail Quality"}, {"How often do you dine at a PA cafeteria (PATC, HT, LT or GWB)?", "PA Cafeteria Frequency"}, {"Please rate the quality of your PA cafeteria dining experience(s)", "PA Cafeteria Quality"}, {"How often do you participate in safety coordinator meetings?", "Safety Coordinator Frequency"}, {"Please rate the quality of your safety coordinator meeting experience(s)", "Safety Coordinator Quality"}, {"How often have you received OSHA or other safety training?", "Safety Training Frequency"}, {"Please rate the quality of your OSHA and/or other safety training experience(s)", "Safety Training Quality"}, {"How often have you relocated to a different office space and/or requested new furniture?", "Office Move Frequency"}, {"Please rate the quality of your relocation and/or new furniture acquisition experience(s)", "Office Move Quality"}, {"How often have you requested an office heating/cooling, and/or pest or plumbing issue be addressed?", "Office Issues Frequency"}, {"Please rate the quality of your experience(s) with heating/cooling, and/or pest or plumbing issues", "Office Issue Quality"}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns2",{"Fuel sites/card frequency","Auto Shop Frequency","SEMAC Frequency", "COMSEG Frequency","Sign Shop Frequency","Creative Frequency","Print Shop Frequency","PA Mail Frequency","PA Cafeteria Frequency","Safety Coordinator Frequency","Safety Training Frequency","Office Move Frequency","Office Issues Frequency","Fuel Open Ended","Auto Shop Open Ended","SEMAC Open Ended","COMSEG Open Ended","Sign Shop Open Ended","Creative Design Open Ended","Print Shop Open Ended","PA Mail Open Ended","PA Cafeteria Open Ended","Safety Coordinator Open Ended","Safety Training Open Ended","Office Move Open Ended","Office Issue Open Ended","Fuel Key Words" ,"Auto Shop Key Words","SEMAC Key Words","COMSEG Key Words","Sign Shop Key Words","Creative Design Key Words","Print Shop Key Words","PA Mail Key Words","PA Cafeteria Key Words","Safety Coordinator Key Words","Safety Training Key Words","Office Move Key Words","Office Issue Key Words","Collector ID","Start Date","End Date","IP Address","Email Address","First Name","Last Name","Custom Data 1"}),
    #"Unpivoted Columns2" = Table.UnpivotOtherColumns(#"Removed Columns2", {"Respondent ID", "Comments"}, "Attribute", "Value"),
    #"Changed Type02" = Table.TransformColumnTypes(#"Unpivoted Columns2",{{"Respondent ID", type text}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type02", "Merge", each [Respondent ID] & [Comments] & List.First(Text.Split([Attribute],"_"))),
    

 

mmayer
Frequent Visitor

    #"Promoted Headers3" = Table.PromoteHeaders(Source3, [PromoteAllScalars=true]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Promoted Headers3",{{"Respondent ID", type number}, {"Collector ID", Int64.Type}, {"Start Date", type datetime}, {"End Date", type datetime}, {"IP Address", type text}, {"Email Address", type text}, {"First Name", type text}, {"Last Name", type text}, {"Custom Data 1", type text}, {"How often do you use PA fuel sites and/or PA fuel cards at retail gas stations?", type text}, {"Please rate the quality of your fueling experience(s)", Int64.Type}, {"Fuel Open Ended", type text}, {"Fuel Quality Key Words", type text}, {"How often do you use PA auto shops for vehicle/equipment repairs and maintenance?", type text}, {"Please rate the quality of your vehicle maintenance and repair experience(s)", Int64.Type}, {"Auto Shop Open Ended", type text}, {"Auto Shop Key Words", type text}, {"How often do you request SEMAC construction support (repairs, plumbing, paving, painting, etc.)?", type text}, {"Please rate the quality of your SEMAC construction support experience(s)", Int64.Type}, {"SEMAC Open Ended", type text}, {"SEMAC Key Words", type text}, {"How often do you request COMSEG (Radio Shop) support for radio communication equipment?", type text}, {"Please rate the quality of your COMSEG (Radio Shop) support experience(s)", Int64.Type}, {"COMSEG Open Ended", type text}, {"COMSEG Key Words", type text}, {"How often do you request roadway signage, pavement markings or delineators from the Sign Shop?", type text}, {"Please rate the quality of your experience(s) with the Sign Shop's services", Int64.Type}, {"Sign Shop Open Ended", type text}, {"Sign Shop Key Words", type text}, {"How often do you request graphics and creative design work?", type text}, {"Please rate the quality of your experience(s) with graphic and creative design", Int64.Type}, {"Creative Design Open Ended", type text}, {"Creative Design Key Words", type text}, {"How often do you request printed materials from the print shop?", type text}, {"Please rate the quality of your experience(s) with the Print Shop", Int64.Type}, {"Print Shop Open Ended", type text}, {"Print Shop Key Words", type text}, {"How often do you use PA's internal mail services or external mail services via a PA mail center?", type text}, {"Please rate the quality of your PA mail center experience(s)", Int64.Type}, {"PA Mail Open Ended", type text}, {"PA Mail Key Words", type text}, {"How often do you dine at a PA cafeteria (PATC, HT, LT or GWB)?", type text}, {"Please rate the quality of your PA cafeteria dining experience(s)", Int64.Type}, {"PA Cafeteria Open Ended", type text}, {"PA Cafeteria Key Words", type text}, {"How often do you participate in safety coordinator meetings?", type text}, {"Please rate the quality of your safety coordinator meeting experience(s)", Int64.Type}, {"Safety Coordinator Open Ended", type text}, {"Safety Coordinator Key Words", type text}, {"How often have you received OSHA or other safety training?", type text}, {"Please rate the quality of your OSHA and/or other safety training experience(s)", Int64.Type}, {"Safety Training Open Ended", type text}, {"Safety Training Key Words", type text}, {"How often have you relocated to a different office space and/or requested new furniture?", type text}, {"Please rate the quality of your relocation and/or new furniture acquisition experience(s)", Int64.Type}, {"Office Move Open Ended", type text}, {"Office Move Key Words", type text}, {"How often have you requested an office heating/cooling, and/or pest or plumbing issue be addressed?", type text}, {"Please rate the quality of your experience(s) with heating/cooling, and/or pest or plumbing issues", Int64.Type}, {"Office Issue Open Ended", type text}, {"Office Issue Key Words", type text}, {"", type text}, {"Are there any other services or issues that you feel need improvement?", type text}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Changed Type3",{{"How often do you use PA fuel sites and/or PA fuel cards at retail gas stations?", "Fuel sites/card frequency"}, {"Please rate the quality of your fueling experience(s)", "Fueling Quality"}, {"Fuel Quality Key Words", "Fuel Key Words"}, {"How often do you use PA auto shops for vehicle/equipment repairs and maintenance?", "Auto Shop Frequency"}, {"Please rate the quality of your vehicle maintenance and repair experience(s)", "Auto Shop Quality"}, {"Are there any other services or issues that you feel need improvement?", "Comments"}, {"How often do you request SEMAC construction support (repairs, plumbing, paving, painting, etc.)?", "SEMAC Frequency"}, {"Please rate the quality of your SEMAC construction support experience(s)", "SEMAC Quality"}, {"How often do you request COMSEG (Radio Shop) support for radio communication equipment?", "COMSEG Frequency"}, {"Please rate the quality of your COMSEG (Radio Shop) support experience(s)", "COMSEG Quality"}, {"How often do you request roadway signage, pavement markings or delineators from the Sign Shop?", "Sign Shop Frequency"}, {"Please rate the quality of your experience(s) with the Sign Shop's services", "Sign Shop Quality"}, {"How often do you request graphics and creative design work?", "Creative Frequency"}, {"Please rate the quality of your experience(s) with graphic and creative design", "Creative Quality"}, {"How often do you request printed materials from the print shop?", "Print Shop Frequency"}, {"Please rate the quality of your experience(s) with the Print Shop", "Print Shop Quality"}, {"How often do you use PA's internal mail services or external mail services via a PA mail center?", "PA Mail Frequency"}, {"Please rate the quality of your PA mail center experience(s)", "PA Mail Quality"}, {"How often do you dine at a PA cafeteria (PATC, HT, LT or GWB)?", "PA Cafeteria Frequency"}, {"Please rate the quality of your PA cafeteria dining experience(s)", "PA Cafeteria Quality"}, {"How often do you participate in safety coordinator meetings?", "Safety Coordinator Frequency"}, {"Please rate the quality of your safety coordinator meeting experience(s)", "Safety Coordinator Quality"}, {"How often have you received OSHA or other safety training?", "Safety Training Frequency"}, {"Please rate the quality of your OSHA and/or other safety training experience(s)", "Safety Training Quality"}, {"How often have you relocated to a different office space and/or requested new furniture?", "Office Move Frequency"}, {"Please rate the quality of your relocation and/or new furniture acquisition experience(s)", "Office Move Quality"}, {"How often have you requested an office heating/cooling, and/or pest or plumbing issue be addressed?", "Office Issues Frequency"}, {"Please rate the quality of your experience(s) with heating/cooling, and/or pest or plumbing issues", "Office Issue Quality"}}),
    #"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns3",{"Fueling Quality", "Auto Shop Quality", "SEMAC Quality", "COMSEG Quality","Sign Shop Quality","Creative Quality","Print Shop Quality","PA Mail Quality","PA Cafeteria Quality","Safety Coordinator Quality", "Safety Training Quality","Office Move Quality","Office Issue Quality","Fuel sites/card frequency","Auto Shop Frequency","SEMAC Frequency", "COMSEG Frequency","Sign Shop Frequency","Creative Frequency","Print Shop Frequency","PA Mail Frequency","PA Cafeteria Frequency","Safety Coordinator Frequency","Safety Training Frequency","Office Move Frequency","Office Issues Frequency","Fuel Key Words" ,"Auto Shop Key Words","SEMAC Key Words","COMSEG Key Words","Sign Shop Key Words","Creative Design Key Words","Print Shop Key Words","PA Mail Key Words","PA Cafeteria Key Words","Safety Coordinator Key Words","Safety Training Key Words","Office Move Key Words","Office Issue Key Words","Collector ID","Start Date","End Date","IP Address","Email Address","First Name","Last Name","Custom Data 1"}),
    #"Unpivoted Columns3" = Table.UnpivotOtherColumns(#"Removed Columns3", {"Respondent ID", "Comments"}, "Attribute", "Value2"),
    #"Changed Type03" = Table.TransformColumnTypes(#"Unpivoted Columns3",{{"Respondent ID", type text}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type03", "Merge2", each [Respondent ID] & [Comments] & List.First(Text.Split([Attribute],"_"))),

    
    #"Promoted Headers4" = Table.PromoteHeaders(Source4, [PromoteAllScalars=true]),
    #"Changed Type4" = Table.TransformColumnTypes(#"Promoted Headers4",{{"Respondent ID", type number}, {"Collector ID", Int64.Type}, {"Start Date", type datetime}, {"End Date", type datetime}, {"IP Address", type text}, {"Email Address", type text}, {"First Name", type text}, {"Last Name", type text}, {"Custom Data 1", type text}, {"How often do you use PA fuel sites and/or PA fuel cards at retail gas stations?", type text}, {"Please rate the quality of your fueling experience(s)", Int64.Type}, {"Fuel Open Ended", type text}, {"Fuel Quality Key Words", type text}, {"How often do you use PA auto shops for vehicle/equipment repairs and maintenance?", type text}, {"Please rate the quality of your vehicle maintenance and repair experience(s)", Int64.Type}, {"Auto Shop Open Ended", type text}, {"Auto Shop Key Words", type text}, {"How often do you request SEMAC construction support (repairs, plumbing, paving, painting, etc.)?", type text}, {"Please rate the quality of your SEMAC construction support experience(s)", Int64.Type}, {"SEMAC Open Ended", type text}, {"SEMAC Key Words", type text}, {"How often do you request COMSEG (Radio Shop) support for radio communication equipment?", type text}, {"Please rate the quality of your COMSEG (Radio Shop) support experience(s)", Int64.Type}, {"COMSEG Open Ended", type text}, {"COMSEG Key Words", type text}, {"How often do you request roadway signage, pavement markings or delineators from the Sign Shop?", type text}, {"Please rate the quality of your experience(s) with the Sign Shop's services", Int64.Type}, {"Sign Shop Open Ended", type text}, {"Sign Shop Key Words", type text}, {"How often do you request graphics and creative design work?", type text}, {"Please rate the quality of your experience(s) with graphic and creative design", Int64.Type}, {"Creative Design Open Ended", type text}, {"Creative Design Key Words", type text}, {"How often do you request printed materials from the print shop?", type text}, {"Please rate the quality of your experience(s) with the Print Shop", Int64.Type}, {"Print Shop Open Ended", type text}, {"Print Shop Key Words", type text}, {"How often do you use PA's internal mail services or external mail services via a PA mail center?", type text}, {"Please rate the quality of your PA mail center experience(s)", Int64.Type}, {"PA Mail Open Ended", type text}, {"PA Mail Key Words", type text}, {"How often do you dine at a PA cafeteria (PATC, HT, LT or GWB)?", type text}, {"Please rate the quality of your PA cafeteria dining experience(s)", Int64.Type}, {"PA Cafeteria Open Ended", type text}, {"PA Cafeteria Key Words", type text}, {"How often do you participate in safety coordinator meetings?", type text}, {"Please rate the quality of your safety coordinator meeting experience(s)", Int64.Type}, {"Safety Coordinator Open Ended", type text}, {"Safety Coordinator Key Words", type text}, {"How often have you received OSHA or other safety training?", type text}, {"Please rate the quality of your OSHA and/or other safety training experience(s)", Int64.Type}, {"Safety Training Open Ended", type text}, {"Safety Training Key Words", type text}, {"How often have you relocated to a different office space and/or requested new furniture?", type text}, {"Please rate the quality of your relocation and/or new furniture acquisition experience(s)", Int64.Type}, {"Office Move Open Ended", type text}, {"Office Move Key Words", type text}, {"How often have you requested an office heating/cooling, and/or pest or plumbing issue be addressed?", type text}, {"Please rate the quality of your experience(s) with heating/cooling, and/or pest or plumbing issues", Int64.Type}, {"Office Issue Open Ended", type text}, {"Office Issue Key Words", type text}, {"", type text}, {"Are there any other services or issues that you feel need improvement?", type text}}),
    #"Renamed Columns4" = Table.RenameColumns(#"Changed Type4",{{"How often do you use PA fuel sites and/or PA fuel cards at retail gas stations?", "Fuel sites/card frequency"}, {"Please rate the quality of your fueling experience(s)", "Fueling Quality"}, {"Fuel Quality Key Words", "Fuel Key Words"}, {"How often do you use PA auto shops for vehicle/equipment repairs and maintenance?", "Auto Shop Frequency"}, {"Please rate the quality of your vehicle maintenance and repair experience(s)", "Auto Shop Quality"}, {"Are there any other services or issues that you feel need improvement?", "Comments"}, {"How often do you request SEMAC construction support (repairs, plumbing, paving, painting, etc.)?", "SEMAC Frequency"}, {"Please rate the quality of your SEMAC construction support experience(s)", "SEMAC Quality"}, {"How often do you request COMSEG (Radio Shop) support for radio communication equipment?", "COMSEG Frequency"}, {"Please rate the quality of your COMSEG (Radio Shop) support experience(s)", "COMSEG Quality"}, {"How often do you request roadway signage, pavement markings or delineators from the Sign Shop?", "Sign Shop Frequency"}, {"Please rate the quality of your experience(s) with the Sign Shop's services", "Sign Shop Quality"}, {"How often do you request graphics and creative design work?", "Creative Frequency"}, {"Please rate the quality of your experience(s) with graphic and creative design", "Creative Quality"}, {"How often do you request printed materials from the print shop?", "Print Shop Frequency"}, {"Please rate the quality of your experience(s) with the Print Shop", "Print Shop Quality"}, {"How often do you use PA's internal mail services or external mail services via a PA mail center?", "PA Mail Frequency"}, {"Please rate the quality of your PA mail center experience(s)", "PA Mail Quality"}, {"How often do you dine at a PA cafeteria (PATC, HT, LT or GWB)?", "PA Cafeteria Frequency"}, {"Please rate the quality of your PA cafeteria dining experience(s)", "PA Cafeteria Quality"}, {"How often do you participate in safety coordinator meetings?", "Safety Coordinator Frequency"}, {"Please rate the quality of your safety coordinator meeting experience(s)", "Safety Coordinator Quality"}, {"How often have you received OSHA or other safety training?", "Safety Training Frequency"}, {"Please rate the quality of your OSHA and/or other safety training experience(s)", "Safety Training Quality"}, {"How often have you relocated to a different office space and/or requested new furniture?", "Office Move Frequency"}, {"Please rate the quality of your relocation and/or new furniture acquisition experience(s)", "Office Move Quality"}, {"How often have you requested an office heating/cooling, and/or pest or plumbing issue be addressed?", "Office Issues Frequency"}, {"Please rate the quality of your experience(s) with heating/cooling, and/or pest or plumbing issues", "Office Issue Quality"}}),
    #"Removed Columns4" = Table.RemoveColumns(#"Renamed Columns4",{"Fuel Open Ended","Auto Shop Open Ended","SEMAC Open Ended","COMSEG Open Ended","Sign Shop Open Ended","Creative Design Open Ended","Print Shop Open Ended","PA Mail Open Ended","PA Cafeteria Open Ended","Safety Coordinator Open Ended","Safety Training Open Ended","Office Move Open Ended","Office Issue Open Ended","Fueling Quality", "Auto Shop Quality", "SEMAC Quality", "COMSEG Quality","Sign Shop Quality","Creative Quality","Print Shop Quality","PA Mail Quality","PA Cafeteria Quality","Safety Coordinator Quality", "Safety Training Quality","Office Move Quality","Office Issue Quality","Fuel sites/card frequency","Auto Shop Frequency","SEMAC Frequency", "COMSEG Frequency","Sign Shop Frequency","Creative Frequency","Print Shop Frequency","PA Mail Frequency","PA Cafeteria Frequency","Safety Coordinator Frequency","Safety Training Frequency","Office Move Frequency","Office Issues Frequency","Collector ID","Start Date","End Date","IP Address","Email Address","First Name","Last Name","Custom Data 1"}),
    #"Unpivoted Columns4" = Table.UnpivotOtherColumns(#"Removed Columns4", {"Respondent ID", "Comments"}, "Attribute", "Value2"),
    #"Changed Type04" = Table.TransformColumnTypes(#"Unpivoted Columns4",{{"Respondent ID", type text}}),
    #"Added Custom4" = Table.AddColumn(#"Changed Type04", "Merge2", each [Respondent ID] & [Comments] & List.First(Text.Split([Attribute],"_"))),

    #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Merge"},#"Added Custom2",{"Merge"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Value"}, {"NewColumn.Value"}),

    #"Merged Queries2" = Table.NestedJoin(#"Added Custom3",{"Merge2"},#"Added Custom4",{"Merge2"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn2" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Value2"}, {"NewColumn.Value"}),

    #"Merged Queries3" = Table.NestedJoin(#"Expanded NewColumn1",{"Merge"},#"Expanded NewColumn2"{"Merge 2"},"NewColumn",JoinKind.LeftOuter),

    #"Removed Columns5" = Table.RemoveColumns(#"Expanded NewColumn1",{"Merge", "Merge2"})

in
    #"Removed Columns5"

The error-message should disappear if you adjust the last step of your query from this:

 

#"Removed Columns5" = Table.RemoveColumns(#"Expanded NewColumn1",{"Merge", "Merge2"})

 

to this:

 

#"Removed Columns5" = Table.RemoveColumns(#"Expanded NewColumn1",{"Merge"})

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.