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

Need Help

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.

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

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

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

v-jingzhang

Community Support

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.

Forum Discussion

Transpose or Unpivot?

Related content

Help Transposing

Transpose or Unpivot

Pivot / Unpivot?

Unpivot Column

Unpivot Question