Supertricky modelling..

[ErikPettersson]

Hi!

I'm encountered a bit of trick problem and would be open to suggestions how to work around it. I have 2 tables (see below) that I need to match. However, the only common denominater contains multiples on both sides. However, in the first table I have an unique ID that I would like to add to the second table (I can do this exercise in Excel of course but I would like to keep it clean in the queary). See below for what I'm looking for:

[v-frfei-msft · Accepted solution]

Hi @ErikPettersson,

Based on my test, we can merget the two tables in power query based on the column ID B.

Here is the M code for your reference.

let
    Source = Table.NestedJoin(Table1,{"ID B"},Table2,{"ID B"},"Table2",JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"ID A"}, {"Table2.ID A"})
in
    #"Expanded Table2"

For more details, please check the pbix as attached.

https://www.dropbox.com/s/f0d3fd6xaafy2n6/merge.pbix?dl=0

Regards,

Frank

[v-frfei-msft]

Hi @ErikPettersson,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,
Frank

[Anonymous]

You could get a unique list of values from the column you want by creating a new query.

Heres a video

https://www.youtube.com/watch?v=vAvQ8pCnWDk

Hope this helps 🙂

[ErikPettersson]

Well, I need to create the C-column because I need it when connecting other tables

[Anonymous]

Can you post an image of your data model? Will be easier to understand.