Create new column with VLOOKUP in Power Querry

[JeroenCB]

Hello there, I am fairly new to Power BI so maybe there is a different issue than my question, please be critical!

I have a data set in an Excel that I load into Power BI, one of my columns(lets call it column A) has a very low data quality but I need to make sense of it. It has different values in 1 cell seperated with a ";", the data is not always in the same order and often missing (or parts missing) example:

Stuff_Word_####; Other_Word; just text

Just text

Stuff_Word_####; just text

In a few cases it can also be the same type of data but with slightly different values:

Stuff_Word_###1; Stuff_Word###2; Stuff_Word###3

I have a unique list (in a seperate column in the same table) of all the unique Stuff_Word (column B) and another unique list of all the unique Other_Word (column C).

Now I want to create a new column that checks if the data in column A contains the same data as any row in column B and then the same for column C. Something like this:

It's basically a VLOOKUP from Excel in a new Power BI column. 

How can I do this?

Hopefully it makes sense, if it doesn't please let me know!

Kind regards,

Jeroen

[Anonymous]

Hi @JeroenCB,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[Anonymous]

Hi @JeroenCB,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[Anonymous]

Hi @JeroenCB,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[danextian]

Hi @JeroenCB 

You will need to transform your columns a bit as M is case-sensitive and will not return a result with even just a letter with a different case. Please see sample code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc1NCoAgEAXgq8i4beN/0A26goibimgTlNH1EyGadHbO+L033sOZrmWJ935MkXNu2MD2tM5H2eRhu85U3hq6yoq8Gd9vAaHzsH1jrSXWsug/cPiabfIK5xWRFzlfnWw2qqnVuFaX2sYYbAxtLDaWNg4bR5semx5CeAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Messed up data" = _t, #"Stuff_word Key" = _t, #"Just word Key" = _t]),
    #"Lowercased Text" = Table.TransformColumns(Source,{{"Messed up data", Text.Lower, type text}, {"Stuff_word Key", Text.Lower, type text}, {"Just word Key", Text.Lower, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Lowercased Text",{{"Messed up data", Text.Clean, type text}, {"Stuff_word Key", Text.Clean, type text}, {"Just word Key", Text.Clean, type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Cleaned Text",{{"Messed up data", Text.Trim, type text}, {"Stuff_word Key", Text.Trim, type text}, {"Just word Key", Text.Trim, type text}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "messed_up_data", each List.Transform( Text.Split([Messed up data], ";"), Text.Trim)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "stuffed_word_key", each #"Trimmed Text"[Stuff_word Key]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom1", "Just_word_key", each #"Trimmed Text"[Just word Key]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom3", "messed_up_intersect", each let 
_intersect = List.Intersect({[messed_up_data],[stuffed_word_key]})
in Text.Combine(_intersect)),
    #"Added Custom4" = Table.AddColumn(#"Added Custom2", "Just word intersect", each let 
_intersect = List.Intersect({[messed_up_data],[Just_word_key]})
in Text.Combine(_intersect)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom4",{"messed_up_data", "stuffed_word_key", "Just_word_key"})
in
    #"Removed Columns"

[pankajnamekar25]

Hello @JeroenCB 

First, split "Column A" by the delimiter ";" to create multiple rows from a single cell.

Then, add a custom column using the formula = if List.NonNullCount(List.Select(MyTable[Column B], each Text.Contains([Column A], _))) > 0 then "Match in Column B" else if List.NonNullCount(List.Select(MyTable[Column C], each Text.Contains([Column A], _))) > 0 then "Match in Column C" else "No Match", which checks if the value in "Column A" contains any values from "Column B" or "Column C" and returns the corresponding match status.

 Since the column was split into rows, group the data back by the original key column and aggregate the results. Finally, click "Close & Apply" to load the transformed data back to Power BI.

 Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[JeroenCB]

Wouldn't splitting by delimiter make columns instead of rows? Or do you mean like this:

So all the data in the other rows gets duplicated and the problem data gets seperated?

I'm sorry but I think I understand about half of what you said 😅.

[Anonymous · Accepted solution]

Hi @JeroenCB,

Power Query has a special 'split into rows' option — it duplicates the row for each separated value while keeping the rest of the data intact. Perfect for cleaning and matching multi-value fields.

Regards,

Vinay Pabbu