Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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 dateSAP NumberArticle DescriptionDepartment
1/02/2022567AardbeiFruits
5/08/2022567AardbeiFruits
4/08/2022567FraiseFruits
18/07/2021897PeerVegetables
28/07/2022897PeerFruits

 

Expected Result:

SAP NumberArticle DescriptionDepartment
567Aardbei, FraiseFruits
897PeerFruits

 

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

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

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"

 

 

View solution in original post

1 REPLY 1
Vijay_A_Verma
Super User
Super User

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"

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors