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: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 🙂

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

8 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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'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.