Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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:

Capture.PNG

 

for now, here is what my query looks like: 

let
Source = Excel.Workbook(File.Contents("C:\Users\SESA539111\Desktop\Elvina training powerquery and pivot\POSfiles\POS file Elvina v3.xlsx"), null, true),
BMPTest_Table = Source{[Item="BMPTest",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(BMPTest_Table,{{"BMP", type text}, {"PM0_BUSINESS UNIT", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","Digital Energy Transactional","Transactional",Replacer.ReplaceText,{"BMP"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Home and Distribution","Transactional",Replacer.ReplaceText,{"BMP"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Low Voltage Transactional","Transactional",Replacer.ReplaceText,{"BMP"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","UNASSIGNED","Transactional",Replacer.ReplaceText,{"BMP"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","UNSPECIFIED","Transactional",Replacer.ReplaceText,{"BMP"})
in
#"Replaced Value4"

 

I would like, if it's possible, to gather all these "replace value" steps into one single step in my query. I already saw some similar posts on the community but I haven't been able to solve my issue with one of them.

 

I am really looking forward for your feeback!

Thanks for your help,

 

Elvi

 

 

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

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"

Capture.PNG

 

BTW, Pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

5 REPLIES 5
tackytechtom
Super User
Super User

Hello y'all,

 

Just wanted to drop off another way on how to solve this namely by utilising a function together with a cleansing table. The idea is to make the whole approach a bit more scalable, by just adding a new rule to the cleansing table. The function would then automatically clean the target table accordng to that new row.

 

Feel free to check this out in the following blog post:
how to replace several characters in a column in just one power query step. (tackytech.blog)

 

Hope this helps 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

 



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

v-frfei-msft
Community Support
Community Support

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"

Capture.PNG

 

BTW, Pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hello, i dont see the attachment

Hello,

You can use the if else statements to replace multiple categories in a single power query replace value formula.
For example, If you have a status column with the values A,I,T and wish to replace them with Active, Inactive, and Terminated use the formula provided below.

= Table.ReplaceValue(
#"Filtered Rows", 
each [Status],
each if [Status] = "A" then "Active" else if [Status] = "I" then "Inactive" else "Terminated",
Replacer.ReplaceValue,{"Status"}
)

Below is how I was able to achieve it

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcslMzyxJzFFwzUstSq9UCClKzCtOTC7JzM9LzFGK1YlW8sjPTVVIzEtRcMksLinKTCoFyYFlfPLLFcLyc0oS01Ox6Av1cwwO9nT3c3WBcoMDXJ093Tyh/MSkZKXYWAA=", 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}}),
    Custom1 = Text.Combine({"Digital Energy Transactional","Home and Distribution","Low Voltage Transactional","UNASSIGNED","UNSPECIFIED"},","),
    Custom2 = Table.TransformColumns(#"Changed Type",{{"Current Value",each if Text.PositionOf(Custom1,_) <> -1 then "Transactional" else _}})
in
    Custom2

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors