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.

Wilson_
Memorable Member
Memorable Member

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?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.