M transform data formula minimize

[Analitika]

How to minimize this, as i need do same thing with more that 100 columns

#"Merged Queries" = Table.NestedJoin(#"Sorted Rows1", {"Custom"}, tbl_PL, {"Attribute"}, "Table", JoinKind.LeftOuter),
#"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"Column1", "Value"}, {"Table.Column1", "Table.Value"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Table", each ([Table.Column1] = "4Q 1")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Table.Column1"}),

#"Merged Queries1" = Table.NestedJoin(#"Removed Columns", {"Custom"}, Table, {"Attribute"}, "Table", JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries1", "Table", {"Column1", "Value"}, {"Table.Column1", "Table.Value.1"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Table1", each ([Table.Column1] = "4Q 22")),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Table.Column1"}),

#"Merged Queries2" = Table.NestedJoin(#"Removed Columns1", {"Custom"}, Table, {"Attribute"}, "Table", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries2", "Table", {"Column1", "Value"}, {"Table.Column1", "Table.Value.2"}),
#"Filtered Rows2" = Table.SelectRows(#"Expanded Table2", each ([Table.Column1] = "EBITDA")),
#"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows2",{"Table.Column1"}),

#"Merged Queries3" = Table.NestedJoin(#"Removed Columns2", {"Custom"}, Table, {"Attribute"}, "Table", JoinKind.LeftOuter),
#"Expanded Table3" = Table.ExpandTableColumn(#"Merged Queries3", "Table", {"Column1", "Value"}, {"Table.Column1", "Table.Value.3"}),
#"Filtered Rows3" = Table.SelectRows(#"Expanded Table3", each ([Table.Column1] = "FCF")),
#"Removed Columns3" = Table.RemoveColumns(#"Filtered Rows3",{"Table.Column1"}),

#"Merged Queries4" = Table.NestedJoin(#"Removed Columns3", {"Custom"}, Table, {"Attribute"}, "Table", JoinKind.LeftOuter),
#"Expanded Table4" = Table.ExpandTableColumn(#"Merged Queries4", "Table", {"Column1", "Value"}, {"Table.Column1", "Table.Value.4"}),
#"Filtered Rows4" = Table.SelectRows(#"Expanded Table4", each ([Table.Column1] = "RRR")),
#"Removed Columns4" = Table.RemoveColumns(#"Filtered Rows4",{"Table.Column1"})

[v-deddai1-msft]

Hi @Analitika ,

You'd better show us sample data in your pbix file: 

Please do mask sensitive data before uploading.

Thanks for your understanding and support.

Best Regards,

Dedmon Dai

[v-deddai1-msft]

Hi @Analitika ,

Would you please show us the full M scripting in your advanced editor? Or show us the pbix file by onedrive for business, even sample data and expected output.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Analitika]

@v-deddai1-msft 

@mahoneypat 

https://easyupload.io/wc8065

[mahoneypat]

Please provide example/mock data (even better in pbix form).  The approach above will have bad performance and as you've seen be tedious to build.  There is likely a much simpler way.  Please show your inputs and desired output.

Regards,

Pat