creating a key based on another column's value

[bo_wang5]

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

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.

[v-jingzhang]

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]

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]

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?

[bo_wang5]

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.

[v-jingzhang · 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.

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])

Regards,
Community Support Team _ Jing

[DataMinistry]

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])

[bo_wang5]

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.