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

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
4 years ago

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