cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vesuviogirl
Regular Visitor

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:

vesuviogirl_1-1669328109731.png

 

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

vesuviogirl_2-1669328425438.png

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

vesuviogirl_3-1669328524630.png

 

Thanks Vesuviogirl

1 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

View solution in original post

9 REPLIES 9
vesuviogirl
Regular Visitor

v-jingzhang
Community Support
Community Support

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:

vesuviogirl_0-1669630557709.png

vesuviogirl_1-1669631063812.png

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

vesuviogirl_2-1669631172358.png

 

Thanks a lot,

Vesuviogirl

Hi @vesuviogirl 

 

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

vjingzhang_0-1669710924058.png

 

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)

vjingzhang_1-1669711551079.png

 

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.

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!!

BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hi Pete, 

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

Hope i will find a way 🙂

Luis98
Resolver II
Resolver II

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:

Luis98_3-1669329530760.png

and then write what you want replace

Luis98_1-1669329484141.png

 

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors