none
Conditional Replace value RRS feed

  • Question

  • Very typical in Power Query, we need to conditionally replace values of one column depending on another.  I stumbled across an elegant solution to do that without creating a custom column, add the new value, delete the old column and rename the custom column, as shown in following code at #"Replaced Value".  This works fine until it encounters an empty table of which it deletes the table columns rendering a table without any column (#"Replaced Value1").  This is an unexpected behaviour, I am hoping someone can explain why it is so and how to correct #"Replaced Value1". 

    My workaround to this is to add an if statement to determine if the table is empty in the first place before the replacement (as in the commented code)

    let
        Source = #table({"col1","col2"},{{1,"add"}}),
        #"Replaced Value" = Table.ReplaceValue(Source,each [col1],each if [col2]="add" then [col1]+1 else [col1],Replacer.ReplaceValue,{"col1"}),
        #"Removed Top Rows" = Table.Skip(#"Replaced Value",1),
        #"Replaced Value1" = Table.ReplaceValue(#"Removed Top Rows",each [col1],each if [col2]="add" then [col1]+1 else [col1],Replacer.ReplaceValue,{"col1"})

    //    #"Replaced Value1" = if Table.IsEmpty(#"Removed Top Rows") then #"Removed Top Rows" else Table.ReplaceValue(#"Removed Top Rows",each [col1],each if [col2]="add" then [col1]+1 else [col1],Replacer.ReplaceValue,{"col1"})

    in
        #"Replaced Value1"


    • Edited by williamwong Saturday, April 16, 2016 9:09 AM
    Saturday, April 16, 2016 9:07 AM

Answers

All replies

  • Hi William. The removal of the columns seems like a bug. I've filed it, and in the meantime you'll have to use the workaround you discovered.

    Ehren

    Tuesday, April 19, 2016 7:33 PM
    Owner
  • Hi William, just wanted to notify you that this is fixed and will be available to you in the next few months :)
    Tuesday, April 26, 2016 5:52 PM
    Moderator
  • The bigger question is why not provide a human-friendly grammar for conditionally replacing values in a column. :) Above construct isn't particularly parsable for what's a rather common use case. Exposing this as a wizard in the Power Query Editor would be fantastic!

    Today this is the only sane way of doing this: https://community.powerbi.com/t5/Desktop/Replace-values-in-one-column-with-a-conditional-referencing/td-p/383883, but not sure if it's as efficient as using Table.ReplaceValue method.


    Thursday, December 6, 2018 8:13 AM
  • Hi Michael. I'd encourage you to post this (or vote for it if it already exists) on ideas.powerbi.com.

    Ehren

    Thursday, December 6, 2018 5:55 PM
    Owner