Merge & Expand in a New Column

Power Query
1

Hello Forum,

I am trying to merge these 2 tables and obtain only one additional column which contains ‘Yes’ when there’s a match and ‘No’ when there is no match

image

With the code I am using I just obtain Yes and null:

Table.ExpandTableColumn(Table.NestedJoin(Source, {“ORDER NUMBER”}, Table.AddColumn(#“OTHER TABLE”, “inOTHERTABLE?”, each if [ORD NUM] is null then “No” else “Yes”), {“ORD NUM”},“OTHER TABLE”, JoinKind.LeftOuter), “OTHER TABLE”, {“inOTHERTABLE?”}, {“inOTHERTABLE?”})

image

Can you please let me know where is the mistake and how to rework in order to have the desired outcome?

Thanks a lot in advance for your precious help
PQ forum.xlsx (21.3 KB)

2

Hello @Anferrig ,

You could just replace null with No value to have desire outcome.

You merge this 2 tables - but since it found no record it display null value.

1 Like
4

Try replacing the “is null” part of your measure with “is {}” - because your match is actually returning an empty set (the curly brackets) and not a null value.

5

thanks, this is an option but I would like not to add additional steps :wink:

6

Hello Heather,

it’s not working, maybe I am doing something wrong :frowning:

image

7

Hi Antonio,
first recommendation to go for replacing null with “No” makes sense. But I am not convinced about the nesting you are doing. The very first step about the additional column doesn’t help as you are merging afterwords. So there is no effect on this. I wouldn’t nest, as it makes it difficult to find the issue.

I linke the idea of Yes/No though. Go for True/False:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "inOtherTable?", each List.ContainsAny({[ORDER NUMBER]}, #"OTHER TABLE"[ORD NUM]))
in
    #"Added Custom"
1 Like
8

The problem of adding first the custom column and then merging is not solveable, the lower rows will always remain empty. Means you need to replace null after the expansion (s. first suggestion you received).

Btw, nesting does only make the code more dense / complex. The number of steps is not really reduced. If you don’t like TRUE/FALSE, go for merge plus expand and replace null with “No” and <>null with “Yes”. I left your nesting “to reduce steps”, but you better make separate steps for merge and expand to make it easer to debug and adjust:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = Table.ExpandTableColumn( Table.NestedJoin(Source, {"ORDER NUMBER"}, #"OTHER TABLE", {"ORD NUM"},"OTHER TABLE", JoinKind.LeftOuter), "OTHER TABLE" , {"ORD NUM"}, {"ORD NUM"} ),
    #"Replaced Value" = Table.ReplaceValue(Custom1,each [ORD NUM],each if [ORD NUM]=null then "No" else "Yes",Replacer.ReplaceValue,{"ORD NUM"})
in
    #"Replaced Value"
1 Like
9

Hello Matthias,

thanks a lot for your help on this and sorry for the delayed reply, I was off for some days

I have been trying different solutions on this problem, including using List.Contains and at the end am pending toward the use of Merge as both the lists of Order Numbers contain thousands of rows and the first approach (List.Contains) is extremely slow compared to the Merge one which looks laser fast

And, as I need to apply this type of judgement more than once (as part of a decision tree), speed is very important

For sure will use your suggestion and replace the values directly in the new column obtained from the the Merge, possibly I will at first perform all the Merge Steps and then Replace the values in the new columns all in one step (using the magic Table.TransformColumns :wink: )

Thanks a lot for your suggestions and have a nice day