Merge 2 tables contais

Power Query
1

HI DNA Team,

merge contains.xlsx (171.9 KB)

I will appreciate your help if this is possible,

I would like to merge both tables together in other to achieve the result below

image

The description column contains invoice number in the text string there that can be found at the beginning of the text, at the end, or in the middle.

image

The second table contains invoice numbers and the codes that I would like to add to my data.

Is it possible to add the column “Code” to the first table in PQ Model?

2

Hi @Matty,

You can use: Text.Split([Description], " ")
To split up the text by spaces into a List

And filter Table4 down to the row that contains a matching value in that List
With: List.Contains( Text.Split([Description], " "), IT[Invoice] )

Retrieve the value from the first row in the column [Code] from the filtered nested table
Using: {0}[Code]

image

Here’s your sample file. merge contains.xlsx (180.8 KB)
I hope this is helpful.

2 Likes
3

Hello, @Matty .

@Melissa 's approach is very good, but I also have another way of solving it.
image

find the power bi file with the steps
Merge 2 tables contais.pbix (27.6 KB)
merge contains.xlsx (172.9 KB)

Best regards
Gifted

1 Like
4

Thank you both for help @Gifted @Melissa!