Forum Discussion
Extract multiple values from a merged column in a query
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
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
9 Replies
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
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
Hi Pete,
thanks for the reply, I have inserted the example in answer to v-jingzhang.
Hope i will find a way 🙂
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.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
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
