Merging on Multiple Feilds where a Feild is Missing

Power Query Data Setup
1

This involves looking up Dropdown ID numbers to get the name of the Dropdown option.

I have managed to get a solution for this to work… BUT
its appears quite inefficient - and I need to repeat the process, not twice as in the attached, but about twelve times… Gender, Language, Country, Age Bracket… etc

Can someone suggest a more efficient way to do this
NB the real Employees Table has about 5M rows and 20 Columns

Thank You

Allister

Merge2.xlsx (19.8 KB)

2

Hi @AllisterB

Try below M code:

let
    Source = Employees,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Employee Code"}, "Attribute", "Value"),
    #"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns", {"Attribute", "Value"}, DropdownData, {"Dropdown Field", "Dropdown Code"}, "DropdownData", JoinKind.LeftOuter),
    #"Expanded DropdownData" = Table.ExpandTableColumn(#"Merged Queries", "DropdownData", {"Dropdown Name"}, {"Dropdown Name"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded DropdownData",{"Value"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Dropdown Name")
in
    #"Pivoted Column"

Merge2.xlsx (23.3 KB)

3

Thank You @SoftwareTrain
This is a nice clean solution :slight_smile:

There are other types of fields in my real employees table that do not involve dropdowns - so I chose not to complicate the request by including them.

However the solution needs to include them.

I have updated the file. Is there a way to include them?

Thanks again

Allister

Merge2.xlsx (21.8 KB)

4

Just replace second step with below code:

    #"Unpivoted Only Selected Columns" = Table.Unpivot(Source, {"Gender", "Language"}, "Attribute", "Value"),

Merge2 (1).xlsx (22.1 KB)

1 Like
5

Hi hope you dont mind, I thought this was an interesting problem so had a go as well, just to add to this discussion, I duplicated the lines so there are 500,000 and added a new made up category, a few things to note, if you change unpivot to unpivot other columns, and the aggregation to Max it can handle multiple records the same, and the beauty of SoftwareTrains approach is it only keeps distinct rows.

I was playing around with trying to make it dynamic, my approach was to try to create a table of all possible combinations of category and merge based off the row that matched all the dropdown codes, I thought it would be an interesting take, and an opportunity to learn.
Merge Kyle.xlsx (34.6 MB)

1 Like
6

Hello @AllisterB

Did the responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

7

Nice solution Mahmoud :slight_smile: