Forum Discussion

Centaur's avatar
Centaur
Helper V
4 years ago
Solved

Transpose or Unpivot

Hello,   I have a file  Co Name, Amount and Date are all in separate columns.  I want: Date to be transposed to rows but keep the Co Name and Amount in rows But Group co name.    I am not sur...
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    try this (my) last guess!

tackytechtom's avatar
tackytechtom
Most Valuable Professional
4 years ago

Hi Centaur .

 

How about this:

 

 

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs5XcFTSUTI00DMwUAAz9A31jQyMDJVideCyRnBZQyyyxnBZIyyyJnBZiKSRUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CompanyName = _t, Amt = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CompanyName", type text}, {"Amt", Int64.Type}, {"Date", type date}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-GB")[Date]), "Date", "Amt", List.Sum)
in
    #"Pivoted Column"

 

Let me know, if this helped! 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

  • Centaur's avatar
    Centaur
    Helper V
    4 years ago

    Hi, I think it woudl be better that I provide the complete file.  

    here it is on my onedrive.  

    [removed link]

    sheet 2 has the data.  

     

    let me know if you have any questions.  

    If I could add a twist. 

    Grouping:

    parent:  

    ProjID (in 1 row)

    then by

    LCID (in same row as ProjID)

    Company Name (in same row as ProjID)

    Amount (in same row as ProjID)

    LCName (in same row as ProjID)

     

    then the dates along top 

    I hope that makes sense.  

    I think its a lot easier than how I am explaining. 

     

    CompanyName01/10/202101/11/202101/12/202101/01/2022

    Co A10203040

    much like the original but adding projID I guess the "parent" grouping.  I need this since there are same companies in different projects.  

    • ronrsnfld's avatar
      ronrsnfld
      Super User
      4 years ago

      Is this what you want for output from your posted file (obviously with more columns and rows)?

       

       

      If so, it's just a matter of 

      • delete the unwanted columns
      • Pivot on the Date column
        • Values = Amt
        • No aggregation
      • Centaur's avatar
        Centaur
        Helper V
        4 years ago

        Hi ronrsnfld, yes I think that is what I want.  I did not know it was that simple.  I was over thinking I think.  thank you very much for the explanation.