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/