Forum Discussion
Solved
Multiple columns to Multiple Rows in power query
hi Team, I have 3 million records with 8 columns coming from DB table . i need help to get the fast way in power query to transpose 1 record to 3 rows as below: Col4,Col7 should be transposed un...
In our file itself, just paste the M code. It will work fine.
Hi,
This M code works
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcqpEcAwFEPBXj42yZ1ePAaW1H8NyRuTRdt7za1azR0OOOGCGx54a7ReYooppphiiimm1jTTTDPNNNNMM71mmGGGGWaYYYaZf44P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t, Col4 = _t, Col5 = _t, Col6 = _t, Col7 = _t, Col8 = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Col3", "Col6"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if [Attribute]="Col1" or [Attribute]="Col4" or [Attribute]="Col7" then "Col1" else "Col2"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute"}),
Partition = Table.Group(#"Removed Columns", {"Custom"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Col3", "Col6", "Value", "Index"}, {"Col3", "Col6", "Value", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Partition", List.Distinct(#"Expanded Partition"[Custom]), "Custom", "Value"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
#"Removed Columns1"Hope this helps.
Thanks Ashish. i will check and get back to u
Ashish_Mathur , can u please share the pbix file
Just paste the powerquery-m Ashish has listed as a solution into a new blank query. It runs without issue.
