Converting row elements into columns based on unique id

Hi hmeegada ,

 

To do this you need to do two steps:

• Add and Index column that restarts at each invoice number
  • Follow this post: https://www.excelguru.ca/blog/2018/06/27/number-rows-by-group-using-power-query/
• Pivot by the index column you get in the last step.

Check the M code that I have done with your data with above steps.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Rcq5DQAgDATBXhw7uacb5P7bwFgCstFq1wpEBqiovPaYbYJjtUWM3bb8LPP389QG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Invoice no" = _t, Site = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice no", Int64.Type}, {"Site", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Invoice no"}, {{"Sites", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Sites], "Index", 1, 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Sites"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Site", "Index"}, {"Site", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-GB")[Index]), "Index", "Site")
in
    #"Pivoted Column"

Regards,

MFelix

Hi hmeegada 

Is this problem sloved?

If not, please let me know.

If it is sloved, could you kindly accept any useful reply as a solution so others may find it quicklly?

 

Best Regards

Maggie