Multi-conditional find replace in Data Power Query from two seperate columns

[TBoat]

Hi,

I have an issue with some of the data being miss classified in my report. I am trying to find a solution on the Power Bi side instead of changing it in the data base. In Power Query does anyone know how to do a "Find and Replace" very specifically based off multiple columns?

Here is my issue:

Can I sort in Specifically in Power Query by Chart Acct Number Name column 4450 - Other - Consignment and change the column Chart Acct find and replace with in this example a 05 - Other Wholesale to 04 - Consignment?

I can't do this in Dax off these two columns because they are already written in Dax to get to this point and are based off a sort order in the original Database.

If I could go in and change the I think this could be done in Power Query with M language or not sure. Can I do this in Power Query before messing with the data with DAX? Does anyone have suggestions?

Thanks so much!

T

"Find Replace" Chart Acct

[TBoat]

Thanks for your help on this. @lbendlin 

My report is acting and refreshing so slow now with just a few changes. Is there any way to cut down on the refresh / load time of these new items? I only have 2 million rows too...

[lbendlin]

You can instead opt to add a calculated column in DAX. That will be faster as it happens in memory.

The replacer function is likely not the source of your slowness as it only works on row level.  You probably have other expensive transforms like sorting or merging.  Those should be avoided.

[lbendlin]

Absolutely.  Replacer.ReplaceValue is actually a function placeholder that you can replace (no pun intended) with your own (x,y,z) function to do all kinds of wonderful things.

Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[TBoat]

Hi - @lbendlin  

Thanks for getting back to me. Interesting. I would like to see how this function works in Power Query. I attached a sample of what my data looks like:

In this case I would like to Replace cg_sort_order " 7 " with " 8 " - only if ca_AcctNumber is "4650"and da_AcctNumber is "4191".

Thanks so much for your help!

Trevor

[lbendlin]

please provide the sample data in usable format. I can't work with screenshots.

[TBoat]

@lbendlin 

Okay, I think I added this correctly. Thanks for the help!

https://achillesanalytics15-my.sharepoint.com/:u:/g/personal/trevor_achilles-analytics_com/EUjYnvmya...

[lbendlin]

Here is a simple version.  For more complex scenarios you can write a custom replacer function.

Table.ReplaceValue(Source,each [cg_sort_order],each if [cg_sort_order]="7" and [ca_AcctNumber]="4650" and [da_AcctNumber]="4191" then "8" else [cg_sort_order],Replacer.ReplaceValue,{"cg_sort_order"})

see attached.