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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi ziying35 , Sorry for pinging you on this old topic. This method works perfectly if I just want to replace the value with a fixed value (null @ 1 @2, etc). But what if I want to multiple the old value with 1000? For example if the old value is 1000, instead of changing it to null i want to make it 1,000,000.

I tried changing the null to below, but it didn't work.

each _ * 1000

 

dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi Anonymous, there are always many ways. Here you can find record base solution. I'm refering to 1st post sample data:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQ0lFSAAIQDcKmIMIcRJgZGICogKL8lNLkksz8PAWXxEqlWB2QViNsWpGxX36eLladxth0GoJtMgKThsZgyiW/HFmbCTZtxiDCAuJq3G41xWojWMTQFGo7FhvNyPaiOTk6YwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"    plan    " = _t, #"    unplan    " = _t, #"    internal    " = _t, #"    external    " = _t, #"    production     " = _t, #"    day flag    " = _t]),
    ColumnsTrim = Table.TransformColumnNames(Source, Text.Trim),
    ChangedType = Table.TransformColumnTypes(ColumnsTrim,{{"Date", type date}, {"plan", Int64.Type}, {"unplan", Int64.Type}, {"internal", Int64.Type}, {"external", Int64.Type}, {"production", Int64.Type}, {"day flag", type text}}),
    EnterColumnsToReplace = "plan, unplan, internal, external",
    ColsList = List.Transform(Text.Split(EnterColumnsToReplace, ","), Text.Trim),
    Ad_Replaced = Table.AddColumn(ChangedType, "Replaced", each 
        if [day flag] = "Down Day" then Record.Combine({_, Record.TransformFields(Record.SelectFields(_, ColsList), List.Transform(ColsList, (y)=>  {y, (x)=> x * 1000}))}) else _,
  type record ),
    Replaced = Table.FromRecords(Ad_Replaced[Replaced], Value.Type(ChangedType))
in
    Replaced