Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Replace multiple values in the same column in one step

Hello everybody,   I am looking to a solution to replace several values in a column, in a single step, to have something like this:   for now, here is what my query looks like:  let Sourc...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Anonymous ,

     

    Here I created a sample for your reference.M code for your reference. 

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcslMzyxJzFFwzUstSq9UCClKzCtOTC7JzM9LzFGK1YlW8sjPTVVIzEtRcMksLinKTCoFyYFlfPLLFcLyc0oS01Ox6Av1cwwO9nT3c3WBcoMDXJ093TxB/FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Current Value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Current Value", type text}}),
    #"AllReplace" = [#"Digital Energy Transactional" = "BMP", #"Home and Distribution" = "BMP", #"Low Voltage Transactional" = "BMP",#"UNASSIGNED" = "BMP",#"UNSPECIFIED" = "BMP"],
    #"Replaced Value" = Table.TransformColumns(#"Changed Type",{{"Current Value",each Record.FieldOrDefault(AllReplace,_,_)}})
in
    #"Replaced Value"

     

    BTW, Pbix as attached.

     

AntoineAB1's avatar
AntoineAB1
Frequent Visitor
1 year ago

Best answer I have found. I adapted it to my problem, and it worked very well:

Table.ReplaceValue(Source, each [GL_MONTH], each if [GL_MONTH] = "03" then "Mar" else if [GL_MONTH] = "11" then "Nov" else if [GL_MONTH] = "12" then "Dec" else if [GL_MONTH] = "04" then "Apr" else if [GL_MONTH] = "01" then "Jan" else if [GL_MONTH] = "07" then "Jul" else if [GL_MONTH] = "06" then "Jun" else if [GL_MONTH] = "10" then "Oct" else if [GL_MONTH] = "09" then "Sep" else if [GL_MONTH] = "02" then "Feb" else if [GL_MONTH] = "08" then "Aug" else if [GL_MONTH] = "05" then "May" else null,Replacer.ReplaceValue,{"GL_MONTH"})

 

Thank you!!

AnnOminous's avatar
AnnOminous
Microsoft Employee
1 year ago

Agreed, and I would have marked it as a solution if someone hadn't already marked v-frfei-msft's response as the solution.