Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
bo_wang5
Helper II
Helper II

creating a key based on another column's value

Hi I'm trying to replicate what I do in excel in power query.

 

bo_wang5_0-1630732243010.png


I have a column which contains country and zip code.  I'd like to create a key that will extract different amount of characters depending on the country column.  So for example if the country is germany, I'd like to extract the 1st 3 characters and combined it with the country for a key if the country is France the 1st 2 characters.  I'd like to do this for as many countries as there are in my dataset which is about 20 or so.  What is the M code that would allow me to do this?  Any help would be much appriciated.

1 ACCEPTED SOLUTION

Hi @bo_wang5 

 

You are close to what you want. When you merge two queries and get the Control column, click Expand icon and select LeftDigits column as you did in the image. Then click OK to expand it. You will have LeftDigits column added to this table. Just like below.

21090901.jpg

 

Then add a custom column and enter below code into the popup window. Note that you need to transform Zipcode column to Text type in advance for it to be used in the Text.Start function. Finally you will get the Key column.

[Country] & "-" & Text.Start([Zipcode],if [LeftDigits] is null then 0 else [LeftDigits])

21090902.jpg

 

Regards,
Community Support Team _ Jing

View solution in original post

7 REPLIES 7
v-jingzhang
Community Support
Community Support

Hi @bo_wang5 

 

Have you got a solution for this problem? If so, kindly accept a proper reply as Solution. This would help more people that have similar questions. Thank you!

 

Best Regards,
Community Support Team _ Jing

wdx223_Daniel
Super User
Super User

NewStep=Table.AddColumn(PreviousStep,"Key",each Text.Start(Text.Format("#[Country]-#[Zipcode]",_),List.PositionOf({{null,""},{"PL"},{"FR"},{"DE","ES"}},[Country],0,(x,y)=>List.Contains(x,y))+Text.Length([Country])+1))

mussaenda
Super User
Super User

Hi @bo_wang5 ,

 

What is the basis of extracting the Zipcode dynamically?

May we know the parameter when we need to extract 3 digits or 2 digits or 1 digit?

 

I like the idea  @DataMinistry suggested but can't seem to get it to work.  The logic to extract different amount of digits by zip code will be dependent on the country and can be maintained in a separate table.  whenI merged the data I can drill into the "control" and select the left digit.  I now want to use this as a the number of digits to extract from the zip code from the left.

bo_wang5_0-1630884535978.png

 

Hi @bo_wang5 

 

You are close to what you want. When you merge two queries and get the Control column, click Expand icon and select LeftDigits column as you did in the image. Then click OK to expand it. You will have LeftDigits column added to this table. Just like below.

21090901.jpg

 

Then add a custom column and enter below code into the popup window. Note that you need to transform Zipcode column to Text type in advance for it to be used in the Text.Start function. Finally you will get the Key column.

[Country] & "-" & Text.Start([Zipcode],if [LeftDigits] is null then 0 else [LeftDigits])

21090902.jpg

 

Regards,
Community Support Team _ Jing

DataMinistry
New Member

Create a control table with one row per country and 2 columns:

- Country

- LeftDigits

 

Merge the control table and the fact table on Country.

Add a custom column = Text.Start([Zipcode],if [LeftDigits] is null then 0 else [LeftDigits])

When you say "merge" the 2 tables did you mean "merge query"? when I created the "merged query" i linked the Country in my Control table with the "Ship to zip country".  When I did this, it created a column called "Control".  And then when I used the formula Text.Start([Zipcode],if [LeftDigits] is null then 0 else [LeftDigits]) it does not work.  Am I missing a step somewhere?  Thanks.

 

bo_wang5_1-1630778854819.png

 

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors