Extract multiple values from a merged column in a query

[vesuviogirl]

Hi,

i am starting to use power BI and i i have 3 columns with different values.

Ex:

I merged them as I have the surname Smith sometime wrongly written, and I am able to consolidate all surnames and name them correctly in the conditional column

but I cannot manage to insert correctly the Country as well near the surname, can somebody help?

Thanks Vesuviogirl

[vesuviogirl]

@v-jingzhang i replied to your message.

[v-jingzhang]

Hi @vesuviogirl 

You don't need to merge the Country column at the first time. After you extract correct names in a custom column, you can then merge the custom column with Country column to get the result you want. 

If I don't understand your desired result correctly, can you provide some sample data to show the desired result you want to get?

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[vesuviogirl]

Hallo, thanks for the reply,

my example was not so good. I have to merge different columns to extract the name of the shop and the specific country. I managed to change the name all in upper case and extract the shop name, but not the country. When I select contains "MEDIAMARKT" than I would like to add the country as well.

Here the actual data's example:

Can I add here also the country? I tried adding a comma near the value, and inserting the country but does not work.

Thanks a lot,

Vesuviogirl

[v-jingzhang]

Hi @vesuviogirl 

You can use & to concatenate "Country" column and "Custom" column. You don't need to extract country from "Merged" column. 

Another option is that you can extract the last two characters from "Merged" column and concatenate them with "Custom" column. This is due to the country code is always at the end in "Merged" column. 

[Custom] & " " & Text.End([Merged],2)

This will make it easier. You don't need to set up many conditions for a conditional column. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[jbwtp · Accepted solution]

Hi @vesuviogirl ,

Yes, you can use the condition in your post, just with a bit of manual polishing.

When you create this conditional step, check the code that PQ wrote for it. Itl will look very similar to this:

Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Merged], "Mediamarkt, ES") then "MEDIAMKT ES" else null)

It does not work as you want at this stage and you will need to make the following changes:

This bit in the if statement Text.Contains([Merged], "Mediamarkt, ES") needs to be changed to this Text.Contains(Text.Lower([Merged]), "mediamarkt" /* we address case variants at the same time */) and Text.Contains([Merged], "ES").

This checks for both name and country in the text string. You may need to change it to Text.Contains(Text.Lower([Merged]), "mediamarkt" /* we address case variants at the same time */) and Text.EndsWith([Merged], "ES") if you want to use the trailing country code as a country trigger.

Or to Text.Contains(Text.Lower([Merged]), "mediamarkt" /* we address case variants at the same time */) and Text.Contains([Merged], ".ES") if you want to ignore the trailing country code. I.e. MEDIAMARKT.CH,ES is CH, and not ES.

It resulted to be more cpmplex than I wanted in the beginning, but hopefully the idea is still clear.

Cheers,

John

[vesuviogirl]

Hallo John, thanks so much for your response. I need it to solve it in the conditional column and you help me out! It took me several trials but I managed to fix it. Example in the file I have with this string for each different country:

else if Text.Contains([Full Name.1], "MEDIAMARKT") and Text.EndsWith([Full Name.1], "BE") then "MEDIAMARKT BE"

It worked!!

[BA_Pete]

Hi @vesuviogirl ,

At first glance, it looks like the third condition in your [Custom] column doesn't acually match anything. You may need to use 'contains' in the operator dropdown instead of 'equals' to check for a partial match.

If this doesn't work for you, then if you can provide an example of exactly what you want each of your [Custom] column values to be based on each row of your original data I can see if there's a more dynamic way for you to get there.

Pete

[vesuviogirl]

Hi Pete, 

thanks for the reply, I have inserted the example in answer to v-jingzhang.

Hope i will find a way 🙂

[Luis98]

Hi @vesuviogirl ,

Would be easier if you replace values Smit by Smith:

You have to click in the column Merged, then click in replace values:

and then write what you want replace