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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
SBPFA
Helper I
Helper I

How to flatten a table into one unique row

Is it possible to convert the table from the left to the table on the right with PowerQuery?

Dont´t mind the labels except Table1, because the already exist in the destination.

In real life Table1 would be an Excel sheet and I have to flatten many of them (Table1 to Table50), each one into a single row:

 

flatten-table.png

Thanks for your help!

1 ACCEPTED SOLUTION
edhans
Super User
Super User

Not exactly, because you have multi-row column headings, which cannot be done in Power Query or Power BI. But You can get this:

edhans_0-1634081499159.png

See my table here. I did it in Excel. 

Basically, I did this:

  1. Unpivoted the Aspect1/Aspect2 columns.
  2. Merged the Attribute column with Aspect1/2 with the CAT1-5 column.
  3. Transposed the table.
  4. Promoted it as headers.
  5. Then got the Table1 name using Table.ColumnNames() function and added that as a column, them moved it to the first column.

If you need this for Excel, this works. I would NOT use this in a Power BI data model. It is not a good model to work with. The DAX will be very difficult. But as an Excel table it will work for a lot of things.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

6 REPLIES 6
SBPFA
Helper I
Helper I

Thanks @edhans , I thought you got it right, it was close enough.

Instead of TableName; Aspect1:Cat1; Aspect2:Cat1; Aspect2:Cat1; Aspect2:Cat2;...

I needed to be: TableName; Aspect1:Cat1; Aspect1:Cat2; ... Aspect2:Cat1; Aspect2;...

To clarify: Aspect1 with all the categories, then Aspect2 with all the categories, and so on.

So I reordered the columns and then it worked, but I give you the credit.

 

Also, I was able to reproduce almost everything but step 5. I get an error using Table.ColumnNames(). Can you clarify on that?

 

Thanks for your help.

 

Yeah, I kinda glossed over that. See this image:

edhans_0-1634140486106.png

  1. the Added Custom step uses Table.ColumnNames(Source){0} function.
  2. The function Table.ColumnNames(Source) returns a list of all column names. It is a Power Query list. The {0} on the end says get the first one. PQ starts numbers at 0, not 1. So the first column in the source data was "Table1"
  3. The "Source" step is the original unmodified table being pulled into Power Query, so that is the name of the table I used in the Table.ColumnNames() function.

make sense now?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

It makes perfect sense. Thank you, again!

Glad to help!



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

Not exactly, because you have multi-row column headings, which cannot be done in Power Query or Power BI. But You can get this:

edhans_0-1634081499159.png

See my table here. I did it in Excel. 

Basically, I did this:

  1. Unpivoted the Aspect1/Aspect2 columns.
  2. Merged the Attribute column with Aspect1/2 with the CAT1-5 column.
  3. Transposed the table.
  4. Promoted it as headers.
  5. Then got the Table1 name using Table.ColumnNames() function and added that as a column, them moved it to the first column.

If you need this for Excel, this works. I would NOT use this in a Power BI data model. It is not a good model to work with. The DAX will be very difficult. But as an Excel table it will work for a lot of things.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thank you @edhans. You're right, this is not for PowerBI, but for Excel.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.