Forum Discussion

KennT's avatar
KennT
Frequent Visitor
4 years ago
Solved

UNION + UNIQUE does not return UNIQUE table

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:m...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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.

     

  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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's avatar
KennT
Frequent Visitor
4 years ago

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's avatar
v-kkf-msft
Icon for Community Support rankCommunity Support
4 years ago

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's avatar
    KennT
    Frequent Visitor
    4 years ago

    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's avatar
      v-kkf-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      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's avatar
        KennT
        Frequent Visitor
        4 years ago

        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.