Forum Discussion

ak77's avatar
ak77
Post Patron
3 years ago
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 under Col1....

Col5,Col8 should be transposed under Col2......

Col3 and Col6 should not be transposed .

pleaese let me know if there a way to achieve it.

 

Link to PBIX with sample data is attached.Please help

 

https://drive.google.com/file/d/1j-snDUUjFdeefu01XVyNsaa51HxOlEkW/view?usp=sharing

 

sample input data

 

required output:

Col1Col2Col3Col6
a1a2a3a6
a4a5a3a6
a7a8a3a6
b1b2b3b6
b4b5b3b6
b7b8b3b6
c1c2c3c6
c4c5c3c6
c7c8c3c6
d1d2d3d6
d4d5d3d6
d7d8d3c6

 

 

Need Help
Show and Tell

8 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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.

  • ak77's avatar
    ak77
    Post Patron
    3 years ago

    Thanks Ashish_Mathur . some syntax issue when i  tried before .. got it fixed and running fine when i copy pasted your M code again .Thanks again