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 |