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,

 

Nice.  Would I be able to apply that to another data source?   The example I posted was a simple and my production file if slightly different. I pasted the code and changed the names accordingly and the data seemed to be the same data meaning that it didnt update to my production data that has several hundred records.  For example, my production file has many different company names but I only still see "Co A" as the grouping.  Not sure if I did something wrong.  I am a novice user of power query.  thank you very much.  

    • 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/