Forum Discussion
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)
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)
I'm new to PowerBI so trying to learn, any tips here would be greatly appreciated.
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.
5 Replies
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.
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?
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.
Hi m2oquestions ,
Does the output like below meet your requirement?
I used my favorite function ConcatenateX to summarize the information in one row per Bill ID.
I attach an example pbix file.
