Multiple columns to Multiple Rows in power query

[ak77]

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:

Col1	Col2	Col3	Col6
a1	a2	a3	a6
a4	a5	a3	a6
a7	a8	a3	a6
b1	b2	b3	b6
b4	b5	b3	b6
b7	b8	b3	b6
c1	c2	c3	c6
c4	c5	c3	c6
c7	c8	c3	c6
d1	d2	d3	d6
d4	d5	d3	d6
d7	d8	d3	c6

[ak77]

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

[Ashish_Mathur]

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]

Thanks Ashish. i will check and get back to u 

[ak77]

@Ashish_Mathur , can u please share the pbix file

[Ashish_Mathur · Accepted solution]

In our file itself, just paste the M code.  It will work fine.

[foodd]

Just paste the powerquery-m Ashish has listed as a solution into a new blank query.  It runs without issue.

[ak77]

Hi @Ashish_Mathur ,

I need a help please.

for the attached dxp i tried the same logic.. but its failing with extra null values. can you please help.

i need a 2 new columns for Client_Returns_Data Table

1."Benchmark"--> which has 3 transposed rows from table columns class_scheme_node_name,class_scheme_node_name_benchmark,ExcessReturnsHeader
2."Returns"-->which has 3 transposed rows from table columns log_portfolio_base,log_benchmark_base,Excess_Return

all the other colums values should be repeated. Thanks again

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

[Ashish_Mathur]

I am clueless about your question.  May be some one else can help you.