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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
m2oquestions
Regular Visitor

How to pivot multiple rows to columns

Hi all,

I'm working with an Excel data source that shows a series of payments that should be applied to a single invoice.

(source file below)

m2oquestions_0-1715800924212.png

 

For each invoice [Bill ID] there is a single corresponding service date [Service Week Ending]. There is also at least one payment date [Posted Week Ending], but often multiple payments made over time to  the same [Bill ID].

 

I'd like to pivot the payment [Payment Amount] & payment date [Posted Week Ending] so that all activity shows on a single line for each [Bill ID]. 

 

(Output goal below)

 

m2oquestions_1-1715801161816.png

 

I'm new to PowerBI so trying to learn, any tips here would be greatly appreciated.

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Payment Amount", type number}, {"Bill ID", type text}, {"Service Week Ending", type date}, {"Posted Week Ending", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Bill ID", "Service Week Ending","Index"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Index", type text}}, "en-IN"),{"Attribute", "Index"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value")
in
    #"Pivoted Column"

Hope this helps.

Ashish_Mathur_0-1715914849851.png

 


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

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Payment Amount", type number}, {"Bill ID", type text}, {"Service Week Ending", type date}, {"Posted Week Ending", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Bill ID", "Service Week Ending","Index"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Index", type text}}, "en-IN"),{"Attribute", "Index"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value")
in
    #"Pivoted Column"

Hope this helps.

Ashish_Mathur_0-1715914849851.png

 


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

Hi @m2oquestions ,

Does the output like below meet your requirement? 

DataNinja777_0-1715871440207.png

I used my favorite function ConcatenateX to summarize the information in one row per Bill ID.  

I attach an example pbix file.  

m2oquestions
Regular Visitor

I guess I don't know whether the term for what I'm trying to do is pivot vs unpivot the data. Either way, I'm trying to learn how to get my dataset into the output format so that we can track each [Bill ID] through the series of payments.

m2oquestions,

 

No worries! For what it's worth, you are using the correct term. It's just generally less of a hassle if you have fewer columns and more rows, for multiple reasons. (Definitely don't take it as gospel since this response is from a LLM, but check out this response from perplexity.ai.)


----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

 

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Wilson_
Super User
Super User

m2oquestions,

 

Frankly, there are very few situations (and I can't think of any of them) where I'd want to pivot the data, as opposed to unpivoting it. It usually makes it much harder to do analytics. Why do you want to pivot your data?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors