Forum Discussion
Stacking multiple columns into single column in a table
Hello Anonymous ,
Assuming you have no problem using power query,
Make duplicate of say original table 1 as table 2.
Remove column value 3 and value4 from table1. Rename value 3 and 4 in table 2 as value 1 and value 2.
Then append table 2 in table 1 and you are done.
I can post an example file if it is acceptable but you would like to see an example.
Hi Anonymous
I was able to get the result you wanted in Figure 2 shared in your question without creating a second or third table. Refer screenshot and the Power Query M code for more details.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKshMV9JRSkosygNS6fn5xalAOicxO1UpVidaKTm/HCGbllmcAZeMBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Column2]), "Column2", "Column1"),
#"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[Column4]), "Column4", "Column3"),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Pivoted Column1", {}, "Attribute", "Value")
in
#"Unpivoted Columns"Ultimately you should decide which solution is more optimum and easier to deploy for your use case.
I ended up using Pivoting the data two times to create this table:
After which you can simply Unpivot Column to get your ideal result.
Hope this helps.
Please give thumbs up, Thanks!
This looks like a great option. Thank you very much!