Reply
avatar user
Anonymous
Not applicable
Partially syndicated - Outbound

reformat a table using Power QUERY

Hi- All ideas welcome:
I have a table like so
 
Part NumbersSupplier NameTotal Cost

ABC1

SUP1£X
ABC2SUP1£X
ABC2SUP2£X
ABC3SUP3£X
.........
...
 
I need to create a new table where data shows in this relationship:
Part NumberSUP1SUP2SUP3...
ABC1£X£X£X...
ABC2£X£X£X...
ABC2£X£X£X...
ABC3£X£X£X...
 
 
 
This needs to be done in power query- does anyone have any Ideas how I can do this ?
 kgiboin_0-1642173278125.gif

 

 
Kent
2 ACCEPTED SOLUTIONS
AlexisOlson
Super User
Super User

Syndicated - Outbound

If you pivot the Supplier Name column using Total Cost as the values, you'll get this:

AlexisOlson_0-1642174040502.png

View solution in original post

BA_Pete
Super User
Super User

Syndicated - Outbound

Hi @Anonymous ,

 

The direct answer is to select your [Supplier Name] column, then go to Transform tab > Pivot Column and select [Total Cost] as the values to get this output:

BA_Pete_0-1642173954088.png

 

However, the real question is WHY you want to do this in PQ?

You're essentially making your data structure WORSE for reporting by doing this.

If you want to report in the format that you've outlined, you should really keep the original format as it is, then report using a matrix visual in PBI.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

7 REPLIES 7
avatar user
Anonymous
Not applicable

Syndicated - Outbound

Thanks guys! appreciate the help!

I am replicating another reporting output and this is how it is displayed- hence the weird request!

Syndicated - Outbound

@Anonymous ,

 

Ok, fair enough.

If you are replicating something else, I recommend you update it while you're there to make it more efficient.

Keeping the original data structure as it is and using the matrix visual to change the report/display format is the efficient way to go.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




BA_Pete
Super User
Super User

Syndicated - Outbound

Hi @Anonymous ,

 

The direct answer is to select your [Supplier Name] column, then go to Transform tab > Pivot Column and select [Total Cost] as the values to get this output:

BA_Pete_0-1642173954088.png

 

However, the real question is WHY you want to do this in PQ?

You're essentially making your data structure WORSE for reporting by doing this.

If you want to report in the format that you've outlined, you should really keep the original format as it is, then report using a matrix visual in PBI.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




avatar user
Anonymous
Not applicable

Syndicated - Outbound

could you elaborate? an example of what the output you suggest is in power BI?

Syndicated - Outbound

@Anonymous ,

Keep your data in its original format in Power Query, then use a matrix visual like this to display it in the format you're looking for:

BA_Pete_0-1642174623876.png

Significantly more efficient and 'useable' than the pivot operation in Power Query (that is an especially expensive PQ function).

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




AlexisOlson
Super User
Super User

Syndicated - Outbound

If you pivot the Supplier Name column using Total Cost as the values, you'll get this:

AlexisOlson_0-1642174040502.png

Syndicated - Outbound

JINX! lol



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

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

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)