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 sure if this can be done in power query? 

link to file (I cant attach a file it seems): 

https://1drv.ms/x/s!AkvCjNvcBhpQiPRSwgYzmL3mCYepgQ?e=kFLRKu

 

 

 Please see sample file, screen shot below as well

 

thank you very much.

Tips and Tricks

14 Replies

  • 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 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 Rocco, I think that file is for Power BI (.pbix)?  I do not have that software.  Would you possibly be able to save it as an excel file?  thank you. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

         

        let
    Source = Excel.Workbook(File.Contents("C:\Users\sprmn\OneDrive\Documents\Power BI Desktop\FCall.xlsx"), null, true),
    FCall_20220325_DefinedName = Source{[Item="FCall_20220325",Kind="DefinedName"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(FCall_20220325_DefinedName, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"LCID", Int64.Type}, {"dte", type date}, {"Amt", type text}, {"ProjName", type text}, {"CompanyName", type text}, {"LCNo", type text}, {"ProjID", Int64.Type}, {"LCName", type text}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"dte", type text}}, "it-IT"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"dte", type text}}, "it-IT")[dte]), "dte", "Amt"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"LCID", "ProjName", "LCNo", "ProjID", "LCName"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"CompanyName", Order.Ascending}})
in
    #"Sorted Rows"