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/

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