UNION + UNIQUE does not return UNIQUE table

[KennT]

Hi

I have 3 tables (imported from 3 different files). I need Part number (UNIQUE) and a Partnumber description from those 3 tables merged into 1 table, which I plan to use as a support table for 1:many relationships for the rest of the data model.

I have tried a UNION + UNIQUE code (as sample file) to generate a new table but since the descriptions vary from file to file, the table naturally ends up with multiple part numbers with the associated description. Like this:

I need the part numbers to be UNIQUE with ANY description from either file. So a single line for PN 11 with either A1 OR A2 as description. I have shared a sample data set here: https://1drv.ms/u/s!ArDn3OiIpCOZh9k9zES3jqjX6zPsUA?e=E4CqaW. IF the description could be prioritized between the 3 files it would be an added bonus, but I am happy just to get any description as long as the partnumber is unique.

Unsure if the solution should be found in PowerQuery as a join or as a DAX code.

Hope someone can help me out 🙂

[v-kkf-msft · Accepted solution]

Hi @KennT ,

If you want to get the description of the highest count, please try the following code.

let
    Source = Table.Combine({Stock2, Backlog2, Sales2}),
    #"Removed Columns" = Table.RemoveColumns(Source,{"QTY"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Part Number", "Description"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Part Number"}, {{"MaxCount", each List.Max([Count]), type number}, {"All", each _, type table [Part Number=nullable number, Description=nullable text, Count=number]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows1", "All", {"Description", "Count"}, {"Description", "Count"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All", "Custom", each if [MaxCount] = [Count] then [Description] else null),
    #"Grouped Rows2" = Table.Group(#"Added Custom", {"Part Number"}, {{"Description", each List.Max([Custom]), type nullable text}})
in
    #"Grouped Rows2"

And this code gets the description of the maximum ordinal number when sorted by string.

let
    Source = Table.Combine({Stock2, Backlog2, Sales2}),
    #"Removed Columns" = Table.RemoveColumns(Source,{"QTY"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Part Number"}, {{"Description", each List.Max([Description]), type nullable text}})
in
    #"Grouped Rows"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@KennT , Try like

distinct(union(distinct(Table1[Part Number]),distinct(Table2[Part Number])))

if there are two description then use summarize

union(

summarize(Table1, Table1[Part Number],"Desc" ,Table1[Part Desc]) ,

summarize(Table2, Table1[Part Number],"Desc" ,Table2[Part Desc]) )

[KennT]

Thank you for your suggestion @amitchandak , however not sure I follow.

"distinct(union(distinct(Table1[Part Number]),distinct(Table2[Part Number])))" would just create a unique list of PNs but WITHOUT any description?

Also the summarize is not working - assume it is because it is text fields.

Would appreciate it if you could demonstrate in the sample data.

[v-kkf-msft]

Hi @KennT ,

Please create the new column:

Column = 
CALCULATE (
    CONCATENATEX ( 'PN Table', [PN Descr], " or " ),
    ALLEXCEPT ( 'PN Table', 'PN Table'[Part Number] )
)

Or try the code in Power Query.

let
    Source = Table.Combine({Stock2, Backlog2, Sales2}),
    #"Removed Columns" = Table.RemoveColumns(Source,{"QTY"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Part Number"}, {{"Description", each Text.Combine([Description]," Or "), type nullable text}})
in
    #"Grouped Rows"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KennT]

Thank you @v-kkf-msft. Absolutely a step towards the desired solution🙂 Prefer the Power Query approach.

Any way to approach - "Description", each Text.Combine([Description] - a bit more dynamically, as data is updated weekly with new duplicates appearing from time to time?

I could add "replace value" for each combined description I encounter, but an automated replacement for future duplet descriptions would be the preferred scenario. 

Could the query be coded to return the description with the highest count or at least just return 1 description at random or first/last encounter?

[v-kkf-msft · Accepted solution]

Hi @KennT ,

If you want to get the description of the highest count, please try the following code.

let
    Source = Table.Combine({Stock2, Backlog2, Sales2}),
    #"Removed Columns" = Table.RemoveColumns(Source,{"QTY"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Part Number", "Description"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Part Number"}, {{"MaxCount", each List.Max([Count]), type number}, {"All", each _, type table [Part Number=nullable number, Description=nullable text, Count=number]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows1", "All", {"Description", "Count"}, {"Description", "Count"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All", "Custom", each if [MaxCount] = [Count] then [Description] else null),
    #"Grouped Rows2" = Table.Group(#"Added Custom", {"Part Number"}, {{"Description", each List.Max([Custom]), type nullable text}})
in
    #"Grouped Rows2"

And this code gets the description of the maximum ordinal number when sorted by string.

let
    Source = Table.Combine({Stock2, Backlog2, Sales2}),
    #"Removed Columns" = Table.RemoveColumns(Source,{"QTY"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Part Number"}, {{"Description", each List.Max([Description]), type nullable text}})
in
    #"Grouped Rows"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KennT]

@v-kkf-msft my sincere apologies for not answering sooner, but have been absolutely swamped with work for the past week.

Your solution worked perfectly! Pure magic. I have applied it to a larger data set and added more columns to the query you sent. Thank you @v-kkf-msft, been struggling with this for quite some time.

If you dont mind a last follow-up question. I have another dataset I want to apply this solution to, but instead a picking the description based on count, I want the query to pick the longest description (with the most characters) with duplets. Would appreciate your input if you have the time.

[v-kkf-msft · Accepted solution]

Hi @KennT ，

Please try the following code.  Take the description with the highest number of characters per Part Number, and if the strings are the same length then take maximum.

let
    Source = Table.Combine({Backlog2, Sales2, Stock2}),
    #"Added Custom" = Table.AddColumn(Source, "Length", each Text.Length([Description])),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Part Number"}, {{"MaxLength", each List.Max([Length]), type number}, {"Allrows", each _, type table [Part Number=nullable number, Description=nullable text, Length=number]}}),
    #"Expanded Allrows" = Table.ExpandTableColumn(#"Grouped Rows", "Allrows", {"Description", "Length"}, {"Description", "Length"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Allrows", each ([Length] = [MaxLength])),
    #"Grouped Rows1" = Table.Group(#"Filtered Rows", {"Part Number"}, {{"Description", each List.Max([Description]), type nullable text}})
in
    #"Grouped Rows1"

Best Regards,
Winniz

[KennT]

Thank you so much for your input @v-kkf-msft . That char count worked as intended too. 2 very useful Query lines (for me at least)😊