Replace values in a multivalue column with other text WITHOUT transforming the data in the tables

[yorick1973]

I have a table with a column that has multiple values stored as numbers separated by commas.  How I can replace the numbers with designated text without transforming the data.  I have seen other posts with solutions using transform data.  Below is an example.

FILENUM	GEOGRAPHIES
ABC18234	1001,1003,1004
ABC12346	1001,1002,1004
ABC34256	1002

The numerical values in GEOGRAPHIES are mapped as below:

1001 = West

1002 = Mountain

1003 = Central

1004 = East

I would like to show the table like below:

FILENUM	GEOGRAPHIES
ABC18234	West,Central,East
ABC12346	West,Mountain,East
ABC34256	Mountain

[Anonymous · Accepted solution]

Hi  @yorick1973 ,

Here are the steps you can follow：

1. Create calculated column.

GEOGRAPHIES_replace =
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE (
        SUBSTITUTE ('Table'[GEOGRAPHIES],"1001","West" ),
        "1002",
        "Mountain"
    ) 
    ,
    "1003",
    "Central"),
    "1004",
    "East"
)

2. Create calculated table.

Table 2 =
SUMMARIZE('Table','Table'[FILENUM],'Table'[GEOGRAPHIES_replace])

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@yorick1973 , without power query transformation, I doubt you can do

The only thing you do is use all list operation

this new column code, you can use that  transform current column

Text.Combine(List.ReplaceValue(List.ReplaceValue(List.ReplaceValue(List.ReplaceValue(
Text.Split(GEOGRAPHIES, ","), "1001","West"), "1002","Mountain"), "1003","Central"),"1004","East"),",")