Power Query, If merged Column has null value Concatenate Col [B] space Col [J]

[ddander54]

Wondering if you can point me in the right direction? I have a 16k row Query (Query1) in PQ that I have merged an 88 row table (Table1) to add 1 additional column to the Query. In the new column in the Query there are some null values where the Merge didn't find the match. Where there is a null value in that new column I would like to 'Concatenate' the value in that row from 'ColB' sp 'ColJ' (underscore would be ok to replace sp if needed).
Existing Query1:
ColA.......ColB.......ColJ......New Column
1...............ABC........DEF......MATCH
2...............GHI.........EFG......null
3...............QRS........XYZ......null

Want Query1:
ColA.......ColB.......ColJ......New Column
1...............ABC........DEF......MATCH
2...............GHI.........EFG......GHI EFG
3...............QRS........XYZ......QRS XYZ

Hope that makes sense.

TIA

Don

 

[SullyWYO]

I would change the null values in "New Column" to "NO MATCH", using Replace Values.
Then, use "Add Column" to combine ColB with ColJ, using space as the delimiter. Call it "COMBINED" (or whatever you please).

Then, create a Conditional Column called "FINAL RESULT":
If New Column = "MATCH" then "MATCH" else if "NO MATCH" then "COMBINED".

Then you can delete the helper columns (COMBINED & New Column)

There is probably a more efficient way that can save a step or two, but this should get it done.

 

[ddander54]

Thank you for your guidance....sometimes I get stuck looking for the one step process, that could be available and forget that there is always more than one way to do things in Excel. It's that 'can't see the trees for the forest' thing.

Thx,
Don