Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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.
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.
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.
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.
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |