Forum Discussion
creating a key based on another column's value
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
Hi Anonymous ,
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.
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