Create unique Id column use GroupBy (Table.Group) with conditions for other columns

[Anonymous]

Hi, 

I need to create a table with three columns where one column contains unique IDs and the two other columns contain (combined or filtered) info about the id.  

The original data contains duplicate values for the id column (called 'SAP number') because there can be several dates, several article descriptions or departments.

I need to apply the next rules to create my unique list of Ids:

Column Article Description: If there are several article descriptions for one SAP number then remove duplicate article descriptions and combine the text.

Column Department: If there are several Departments for one SAP number then only keep the Department with the latest date. 

Sample data:

Report date	SAP Number	Article Description	Department
1/02/2022	567	Aardbei	Fruits
5/08/2022	567	Aardbei	Fruits
4/08/2022	567	Fraise	Fruits
18/07/2021	897	Peer	Vegetables
28/07/2022	897	Peer	Fruits

Expected Result:

SAP Number	Article Description	Department
567	Aardbei, Fraise	Fruits
897	Peer	Fruits

For the department column I probably need to edit the Table.Group function. Like if distinctcount Department > 1 then List.max or so 

= Table.Group(#"Filtered Rows", {"SAP Number"}, {{"CountDistinct", each Table.RowCount(Table.Distinct(_)), Int64.Type}, {"MyTable", each _, type table [Report date=nullable date, SAP Number=nullable number, Article Description=nullable text, Department=nullable text]}})

For the combined article description the below code is working

Table.AddColumn(#"Extracted Values1", "Article Description(s)", each Table.Column([#"MyTable"], "Article Description")),

Table.TransformColumns(#"Added Custom ARTICLE", {"Article Description(s)", each Text.Combine(List.Transform(_, Text.From), "/ "), type text}),

Can anyone help? 

Kind regards, 

Julie

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ30jcyMDJS0lEyNTMHko6JRSlJqZlAlltRaWZJsVKsTrSSqb4FEapM0FW5FSVmFqeiKjK00DcwBykzBIpbWIKUBaSmFgGpsNT01JLEpJxUiEIjmEIjdIUw02IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Report date" = _t, #"SAP Number" = _t, #"Article Description" = _t, Department = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Report date", type date}, {"SAP Number", Int64.Type}, {"Article Description", type text}, {"Department", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"SAP Number", "Article Description"}, {{"MaxDate", each List.Max([Report date]), type nullable text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"SAP Number", "Article Description", "Report date"}, #"Grouped Rows", {"SAP Number", "Article Description", "MaxDate"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"SAP Number"}, {"SAP Number.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Grouped Rows", each ([SAP Number.1] <> null)),
    #"Grouped Rows1" = Table.Group(#"Filtered Rows", {"SAP Number"}, {{"Article Description", each Text.Combine([Article Description],", "), type nullable text}, {"Temp", each _{0}[Department]}})
in
    #"Grouped Rows1"