Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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"
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"
Check out the July 2025 Power BI update to learn about new features.