Forum Discussion
Create unique Id column use GroupBy (Table.Group) with conditions for other columns
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
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"
1 Reply
- Vijay_A_Verma
Most Valuable Professional
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"