Group by a column with expansion

Hi Anonymous

Steps below:

1.Add an index column based on “Count” column group by the “OrderNum” column.

2.Merge “PartNum”, “Count”, “Item Name” columns

3.Add conditional columns

4.Fill up null values

5.Remove Duplicates

You could click the icon on the APPLIED STEPS pane, then you can see how to set the steps.

Code in Advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY8/D8IgEMW/imGujXDQP2N1MDFxqlvTgSCxpCgGO+i39ziWjrrce3nc7+UYBsZBsoJ13r5RTuFhX6hS4RC85KIUO15vKAJKL9t+cnFBB2wsfufrSgLKQT/TE6FQoe31Hecx2g+KIq5ec0rmtcWZ2UZ01T9s26Q17wyhPKMt2rMzk/XU4W/6GtLRIhU06wIACvchzPnD4xc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ClientID = _t, Name = _t, Surname = _t, OrderNum = _t, OrderDate = _t, PartNum = _t, #"Item Name" = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClientID", Int64.Type}, {"Name", type text}, {"Surname", type text}, {"OrderNum", Int64.Type}, {"OrderDate", type text}, {"PartNum", Int64.Type}, {"Item Name", type text}, {"Count", Int64.Type}}),
    Partition = Table.Group(#"Changed Type", {"OrderNum"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"ClientID", "Name", "Surname", "OrderNum", "OrderDate", "PartNum", "Item Name", "Count", "Index"}, {"ClientID", "Name", "Surname", "OrderNum.1", "OrderDate", "PartNum", "Item Name", "Count", "Index"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Expanded Partition", "Merged", each Text.Combine({Text.From([PartNum], "en-US"), Text.From([Count], "en-US"), [Item Name]}, " "), type text),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Merged Column", "1-st item", each if [Index] = 1 then [Merged] else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "2-nd item", each if [Index] = 2 then [Merged] else null),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "3-rd item", each if [Index] = 3 then [Merged] else null),
    #"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "4-th item", each if [Index] = 4 then [Merged] else null),
    #"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "5-th item", each if [Index] = 5 then [Merged] else null),
    #"Filled Up" = Table.FillUp(#"Added Conditional Column4",{"2-nd item", "3-rd item", "4-th item"}),
    #"Removed Duplicates" = Table.Distinct(#"Filled Up", {"OrderNum"})
in
    #"Removed Duplicates"

Best Regards

Maggie