Forum Discussion

TBoat's avatar
TBoat
Frequent Visitor
3 years ago

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

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

 

 

 

 

Need Help

7 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    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's avatar
      TBoat
      Frequent Visitor
      3 years ago

      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's avatar
        lbendlin
        Super User
        3 years ago

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

  • TBoat's avatar
    TBoat
    Frequent Visitor
    3 years ago

    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's avatar
      lbendlin
      Super User
      3 years ago

      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.