Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ak77
Post Patron
Post Patron

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

ak77_0-1690237514580.png

 

required output:

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

 

 

1 ACCEPTED SOLUTION

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
ak77
Post Patron
Post Patron

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
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks Ashish. i will check and get back to u 

@Ashish_Mathur , can u please share the pbix file

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.