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!

Centaur's avatar
Centaur
Helper V
4 years ago

Hi Tom, I don't think you saw my post below. I have a link to my file. It has all the data actually. I think it's on the second sheet.  It was in my response to Rocco actually (today).  Thank you ver much.  

  • tackytechtom's avatar
    tackytechtom
    Most Valuable Professional
    4 years ago

    Hi Centaur ,

     

    Maybe this one?

     

    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 = Excel.Workbook(File.Contents("C:\Users\filepath.xlsx"), null, true),
    Sheet3_Sheet = Source{[Item="Sheet3",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet3_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"LCID", Int64.Type}, {"dte", type date}, {"Amt", type number}, {"ProjName", type text}, {"CompanyName", type text}, {"LCNo", type text}, {"ProjID", Int64.Type}, {"LCName", type text}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type", {"dte"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Errors", {{"dte", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Removed Errors", {{"dte", type text}}, "en-GB")[dte]), "dte", "Amt", List.Sum)
in
    #"Pivoted Column"

     

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

    • Centaur's avatar
      Centaur
      Helper V
      4 years ago

      Hi Tom / Rocco,

       

      I have looked at both solutions. 

      I think that since the source data and the output are both in the same file, I need 

       

      let
      Source = Excel.CurrentWorkbook(){[Name="table3"]}[Content],
      I tried to modify but I am getting errors that table3 doesnt exist but it does.  The name of the source data table is table3 so I am a little confused.

       

      Could you kindly assist with knowing that both the source and the output are in the same file?   thank you very much.