Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

creating a key based on another column's value

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 characte...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Anonymous 

     

    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's avatar
DataMinistry
New Member
4 years ago

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.