Forum Discussion

PowerBITesting's avatar
PowerBITesting
Icon for Helper IV rankHelper IV
6 years ago
Solved

Replacing comma separated values to texts with creating a New column

Trying to create a Column with below Logic  #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine( List.ReplaceMatchingItems(Text.ToList ([CommaSeparatedNumbersColumnName]),...
General Comment
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    PowerBITesting - You can use SUBSTITUTE in DAX:

    New Column = 
  SUBSTITUTE(
    SUBSTITUTE(
      SUBSTITUTE([Column],"100","A"),
      "200","B"
    ),
    "300","C"
  )

    Should be able to do the same thing in Power Query essentially nesting your replacer functions. ImkeF edhans  

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

edhans , There was a formula give with the result and I assumed it working and text.Split will convert comma-separated text string and that is what suggested. Seem like that is needed too. 

edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
6 years ago

That is why I was asking amitchandak . If I read PowerBITesting 's original post correctly, they were getting the column with the red (1) below, and wanted what shows up in column marked with the red (2).

But I still cannot get your solution to replace Text.ToList with Text.Split. It returns an error, and doesn't do the proper replacement of 100 with A, 200, with B, etc.

But I could be wrong. That is why I was asking if you tested it as I'd like to see your solution to better understand. I have been unable to get it to work.