Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Replacing values in multiple columns based on condition in Power Query

Hello,   I have a table like this: Date     plan         unplan         internal         external         production          day flag     1/1/2020     0 0 50 70 6000 Production ...
Need Help
  • ziying35's avatar
    ziying35
    6 years ago

    Hi, Anonymous 

    Based on the simulated data source and the data processing logic you provided, write the query code like this:

    = Table.ReplaceValue(Source, each [day flag]="Down Day", null, (x,y,z)=> if y then z else x, List.Range(Table.ColumnNames(Source),1,4))

    If my code solves your problem, mark it as a solution

  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Anonymous 

     

    As what is suggested by ziying35 , I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may insert a new step as below. On your side, you may add all corresponding columns in {"plan","unplan","internal","external",...}.

    = Table.ReplaceValue(Source, each [day flag]="Down Day", null, (x,y,z)=> if y then z else x, List.Select(Table.ColumnNames(#"Changed Type"),
each  List.Contains( {"plan","unplan","internal","external"},_)
)
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Rickmaurinus's avatar
Rickmaurinus
Helper V
4 years ago

Hi ziying,

 

Thanks for adding the clarification. I could imagine in my head that happens. I'm just stumbled by how you thought of doing this, please help me understand. 

 

No where does it say that Y represents the Table function, or that the third parameters should be the Z or what X should do. 

 

If I can ask

1. How did you come up with this in the first place? Is there any documentation on this? 

2. In theory, you could have replaced the function letters with any other letter, correct? 

 

Best,

Rick

lbendlin's avatar
lbendlin
Super User
4 years ago

You can replace it with any string you want.

(original,logiccheck,alternative)=> if logiccheck then alternative else original

 

Yes, the documentation is a bit sparse on this.  Here's a slight hint:

Replacer.ReplaceValue - PowerQuery M | Microsoft Docs

It's misleading, but at least it mentions parameters 🙂

 

I have a suspicion that this approach can be used for other M functions as well.  Didn't have time to test it yet.