Forum Discussion

Trebor84's avatar
Trebor84
Helper II
5 years ago
Solved

Transpose or Unpivot?

Hi, hoping someone may be able to help me.


I have a dataset that has 5 columns of items and quantities i.e. item 1, quantity 1, item 2, quantity 2, item 3, quantity 3 etc.

 

I have tried various ways to transpose columns to rows and also Unpivot within Power Query but I am unable to get it right.

Original table:

 

What I am looking for:

 

Someone on another forum has attempted to help me but I haven't been able to achieve what I am looking for: https://www.mrexcel.com/board/threads/transpose-or-unpivot.1165557/#post-5661218

 

Thanks

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi Trebor84 

    You could try below codes. I have added some comments in it to make it clearer.

     

    let
    Source = Excel.Workbook(File.Contents("D:\TestData\Book - Copy.xlsx"), null, true),
    Table5_Table = Source{[Item="Table5",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table5_Table,{{"Date", type date}, {"Contract", type text}, {"Job Ref", Int64.Type}, {"Model", type text}, {"Contractor", type text}, {"Acc No", Int64.Type}, {"Case Ref", Int64.Type}, {"Incident Ref", Int64.Type}, {"Item 1", type text}, {"Quantity 1", Int64.Type}, {"Item 2", type text}, {"Quantity 2", Int64.Type}, {"Item 3", type text}, {"Quantity 3", Int64.Type}, {"Item 4", type text}, {"Quantity 4", Int64.Type}, {"Item 5", type text}, {"Quantity 5", Int64.Type}, {"Payment Ref", type text}, {"Job Ref Unique", Int64.Type}, {"Type", type text}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Quantity 5", "Item 5", "Quantity 4", "Item 4", "Quantity 3", "Item 3", "Quantity 2", "Item 2", "Quantity 1", "Item 1"}, "Attribute", "Value"),
    //Add an Index column which starts from 0
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Only Selected Columns", "Index", 0, 1, Int64.Type),
    //Add a custom column which gets the value of the next row in Value column. If there is no next row, return null
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try #"Added Index" [Value] {[Index] + 1} otherwise null),
    //Split Attribute column by space delimiter
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    //Filter rows to remain rows with Item value in Attribute.1 column
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Attribute.1] = "Item")),
    //Remove unwanted columns
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute.1", "Attribute.2", "Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Item"}, {"Custom", "Quantity"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Date", "Contract", "Job Ref", "Model", "Contractor", "Acc No", "Case Ref", "Incident Ref", "Item", "Quantity", "Payment Ref", "Job Ref Unique", "Type"})
in
    #"Reordered Columns"

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

5 Replies

  • Trebor84's avatar
    Trebor84
    Helper II
    5 years ago

    Hi, thanks for the link but that is a little beyond my skill set at this stage.

     

    I have tried various ways of grouping, transposing and unpivoting put can't get it right.

    • mahoneypat's avatar
      mahoneypat
      Microsoft Employee
      5 years ago

      If you share some example data as a table to enable copy/paste or a link to a pbix or Excel file with mock data, I (or someone in the community) can provide M code for that specific transformation (using the ribbon buttons and/or an approach like in that video).

       

      Regards,

      Pat

      • Trebor84's avatar
        Trebor84
        Helper II
        5 years ago

        Hi, appreciate any help I can get with this, original table on first sheet and what I am trying to acomplish on the second sheet. https://easyupload.io/xhvalk 

         

        This is as far as I have got so far.

         

         

        let
        Source = Excel.Workbook(File.Contents("C:\Users\User\OneDrive\Documents\Book.xlsx"), null, true),
        Table5_Table = Source{[Item="Table5",Kind="Table"]}[Data],
        #"Unpivoted Only Selected Columns" = Table.Unpivot(Table5_Table, {"Item 1", "Quantity 1", "Item 2", "Quantity 2", "Item 3", "Quantity 3", "Item 4", "Quantity 4", "Item 5", "Quantity 5"}, "Attribute", "Value"),
        #"Added Custom" = Table.AddColumn(#"Unpivoted Only Selected Columns", "Custom", each if Text.Length([Attribute]) = 6 then "Item"
        else "Quantity"),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute"}),
        #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Date", "Contract", "Job Ref", "Model", "Contractor", "Acc No", "Case Ref", "Incident Ref", "Payment Ref", "Job Ref Unique", "Type", "Custom", "Value"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Custom", type text}})
        in
        #"Changed Type"

         

        Thanks