Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
I have a data feed coming from an external system into PowerBI. Over a number of months, several users selected incorrect data in that system and now I need to clean it up in PowerBI because unfortunately it is too late to make the changes in that system. Data will be correct going forward but I need to change the historical info.
Here is what I am trying to do in words
If [JOB] = "DJ-229" AND [PHASE_NAME] = "Flowback" AND [TYPE] = "Separators > 100-285psi Storage Separator" THEN change "LS-0033" in [NAME] to "ST-0033"
There are several other cleanup items but they are all very similar to the one above. Note that as you can see in the screenshot LS-0033 is not the full contents of the cell.
I've tried a few things but all have failed miserably. Any help would be appreciated.
Solved! Go to Solution.
HI @davidoz,
You can try to add a custom column with the below if statement expressions to do replace value operations:
#"Added Custom" =
Table.AddColumn(#"Changed Type","Replace",
each
if
[PHASE_NAME] = "Flowback" and [TYPE] = "Separators > 100-285psi Storage Separator"
then
if [JOB] = "DJ-229" then "ST-0033"
else
if [JOB] = "DJ-50" then "ST-0014"
else
if [JOB] = "DJ-85" then "ST-0098"
else
[NAME]
else
[NAME]
)
Full query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsvJL09KTM5W0lEKTi1ILEosyS8qVogpNTAwTlUwNDDQNbIwLSjOVAgGiiempyrAFQE1uHjpGhlZAhk+wbpA9cZKsToUG2hqADPPxIQa5lmYwswzNKSGeUaGVPawhakhsgtjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PHASE_NAME = _t, TYPE = _t, JOB = _t, NAME = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PHASE_NAME", type text}, {"TYPE", type text}, {"JOB", type text}, {"NAME", type text}}),
#"Added Custom" =
Table.AddColumn(#"Changed Type","Replace",
each
if
[PHASE_NAME] = "Flowback" and [TYPE] = "Separators > 100-285psi Storage Separator"
then
if [JOB] = "DJ-229" then "ST-0033"
else
if [JOB] = "DJ-50" then "ST-0014"
else
if [JOB] = "DJ-85" then "ST-0098"
else
[NAME]
else
[NAME]
)
in
#"Added Custom"
Regards,
Xiaoxin Sheng
so the issue with the "create a new column" solutions is that I need to run this 3 or 4 times. And I also only need to change part of the name, not the entire name.
For example:
If [JOB] = "DJ-229" AND [PHASE_NAME] = "Flowback" AND [TYPE] = "Separators > 100-285psi Storage Separator" THEN change "LS-0033" in [NAME] to "ST-0033"
If [JOB] = "DJ-50" AND [PHASE_NAME] = "Flowback" AND [TYPE] = "Separators > 100-285psi Storage Separator" THEN change "LS-0044" in [NAME] to "ST-0014"
If [JOB] = "DJ-85" AND [PHASE_NAME] = "Flowback" AND [TYPE] = "Separators > 100-285psi Storage Separator" THEN change "LS-0011" in [NAME] to "ST-0098"
Any ideas?
HI @davidoz,
You can try to add a custom column with the below if statement expressions to do replace value operations:
#"Added Custom" =
Table.AddColumn(#"Changed Type","Replace",
each
if
[PHASE_NAME] = "Flowback" and [TYPE] = "Separators > 100-285psi Storage Separator"
then
if [JOB] = "DJ-229" then "ST-0033"
else
if [JOB] = "DJ-50" then "ST-0014"
else
if [JOB] = "DJ-85" then "ST-0098"
else
[NAME]
else
[NAME]
)
Full query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsvJL09KTM5W0lEKTi1ILEosyS8qVogpNTAwTlUwNDDQNbIwLSjOVAgGiiempyrAFQE1uHjpGhlZAhk+wbpA9cZKsToUG2hqADPPxIQa5lmYwswzNKSGeUaGVPawhakhsgtjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PHASE_NAME = _t, TYPE = _t, JOB = _t, NAME = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PHASE_NAME", type text}, {"TYPE", type text}, {"JOB", type text}, {"NAME", type text}}),
#"Added Custom" =
Table.AddColumn(#"Changed Type","Replace",
each
if
[PHASE_NAME] = "Flowback" and [TYPE] = "Separators > 100-285psi Storage Separator"
then
if [JOB] = "DJ-229" then "ST-0033"
else
if [JOB] = "DJ-50" then "ST-0014"
else
if [JOB] = "DJ-85" then "ST-0098"
else
[NAME]
else
[NAME]
)
in
#"Added Custom"
Regards,
Xiaoxin Sheng
Thank you. This is the solution I went with. It wasn't Ideal having to make a custom column but I couldn't find a way around that
@davidoz add new custom column in Power Query, let's call it New Name
if [JOB] = "DJ-229" AND [PHASE_NAME] = "Flowback" AND [TYPE] = "Separators > 100-285psi Storage Separator" and [Name] = "LS-0033" then "ST-0033" else [Name]
after the above column is added, remove the existing Name column and rename New Name to Name. Change the logic as you see fit.
✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi @davidoz
Please create a new column using the code below code and give it a try.
If (Table1[JOB] = "DJ-229" && Table1[PHASE_NAME] = "Flowback" && Table1[TYPE] = "Separators > 100-285psi Storage Separator" && Table1[NAME] = "LS-0033","ST-0033",Table1[NAME])
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
71 | |
57 | |
38 | |
36 |
User | Count |
---|---|
81 | |
67 | |
61 | |
46 | |
45 |