Forum Discussion

m2oquestions's avatar
m2oquestions
Regular Visitor
2 years ago
Solved

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 ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    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's avatar
m2oquestions
Regular Visitor
2 years ago

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.

  • Wilson_'s avatar
    Wilson_
    Memorable Member
    2 years ago

    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.