Forum Discussion
Extract multiple values from a merged column in a query
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
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
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!!