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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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
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!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.