Unpivot columns based on other table that define the group

[Anonymous]

I have two tables where one has the base data that needs to unpivot the columns (close to 1000 different columns) into different set of grouping. The other table that i have identify the groups that I'd like to unpivot into. Is there a way we can unpivot the columns based on the definition defined in different table? See below for a simplified example:

Table 1:

Record ID	Question 1	Question 1-comment	Name	Question 2	Question 2-Comment	Question 3	Question 3-comment
A	yes		Erik	no	hello world	no
B	yes	test comment	Adam	no		no	comment 1
C	yes		Adam	yes		no	comment2
D	no		Erik	yes		yes

Table 2:

Columns	Type
Question 1	yes/no
Question 1-comment	comment
Name	name
Question 2	yes/no
Question 2-Comment	comment
Question 3	yes/no
Question 3-comment	comment

Desire Result after unpivot:

Record ID	Attribue	Value	Name	Attribute 1	Value
A	Question 1	yes	Erik	Question 1-comment
A	Question 2	no	Erik	Question 2-comment	hello world
A	Question 3	no	Erik	Question 3-comment
B	Question 1	yes	Adam	Question 1-comment	test comment
B	Question 2	no	Adam	Question 2-comment
B	Question 3	no	Adam	Question 3-comment	comment 1
C	Question 1	yes	Adam	Question 1-comment
C	Question 2	yes	Adam	Question 2-comment
C	Question 3	no	Adam	Question 3-comment	comment2
D	Question 1	no	Erik	Question 1-comment
D	Question 2	yes	Erik	Question 2-comment
D	Question 3	no	Erik	Question 3-comment

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Record ID", type text}, {"Question 1", type text}, {"Question 1-comment", type text}, {"Name", type text}, {"Question 2", type text}, {"Question 2-Comment", type text}, {"Question 3", type text}, {"Question 3-comment", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Record ID", "Name"}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Record ID", Order.Ascending}, {"Name", Order.Ascending}, {"Attribute", Order.Ascending}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Sorted Rows", "Attribute", "Attribute - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Attribute - Copy", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Attribute - Copy.1", "Attribute - Copy.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Attribute - Copy.2"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each #"Added Index" [#"Attribute - Copy.1"] {[Index] + 1}),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Value.1", each if [#"Attribute - Copy.1"]=[Custom] then #"Added Index" [Value] {[Index] + 1} else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each not Text.Contains([Attribute], "-")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Attribute - Copy.1", "Index", "Custom"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns1", "Attribute 1", each [Attribute]&"-Comment"),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom2",{"Record ID", "Attribute", "Value", "Name", "Attribute 1", "Value.1"})
in
    #"Reordered Columns"

Hope this helps.

[v-alq-msft]

Hi, @Anonymous 

You may click 'Edit Query', go to Query Editor, go to 'Transform' ribbon, click 'Merge Columns', put the question and its corresponding comment together.

After three 'Merge Columns' steps, here is the result.

Then you may make three merged column selected and unpivot them.

Finally you need to do a 'Split Column' to Attribute and Value columns.

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for the reply. This works only if the number of columns are not that much and it's managable; however, my main database table has close to 1000 columns that needs to be merge like this case. Furthermore, additional columns may be added in the future, it's hard to merge it manually one by one.