Generate a list of unique items Power Query excel

[10500438]

I have a list of sales order-sales item combinations, 

86562-1

25562-2

30563-1

I need to find a way to merge this information with another list of items, 

HSM
CPL

PLTC

so in essence I need to find a way to take a large list of sales orders and create a line for each with a mill area. Like this:

Sales Order     Sales Item            MillArea

25562                  2                         HSM

25562                  2                           CPL

25562                  2                         PLTC

I can do this in excel using VBA but it runs a long time because the list of sales orders are over 100k records a month, so in essence 100k record would become 300k records.

Any suggestions?

This may be a long shot, but I will ask this question too:

I have 5 Mill Areas called HSM, PLTC, CPL, HDG, and SLH. I need a record (row) for each, so I use a macro to create the list of sales order/item with a mill area represented in each row, then I compare the new list to my original list to determine which lines are missing.

For Example: Original List

Sales Order     Sales Item            MillArea

25562                  2                         HSM

25562                  2                           CPL

25562                  2                         PLTC

I have two other mill areas not listed, Mill Area "HDG" and Mill Area "SLH", is there a way to take a list of items and find records that are not there,  so it would generate a new list:

Sales Order     Sales Item            MillArea

25562                  2                         HDG

25562                  2                          SLH

[camargos88 · Accepted solution]

Hi @10500438 ,

Check this file: Download PBIX 

[10500438]

YES!!!!

I had to go the long way today, I created a column for each mill area, then unpivot, then I merged the two files and filtered on the null to determine which were missing, your way is better, THANK YOU!!!