none
Table.ReplaceValue without changing every column type RRS feed

  • Question

  • I'm guessing this is straight forward, but searched and searched and can't find.  When using Table.ReplaceValue every column type is changed.  As we've used powerquery more and more, what was an annoyance is becoming more of a blocker.  Is there a way to replace values in single column without impacting all columns?

    thanks in advance

    Thursday, August 3, 2017 3:15 PM

Answers

  • Edit: the following code restores the types, but it won't convert values to those types.

    @Imke, sure:

    = Value.ReplaceType(PreviousStep,Value.Type(StepWhenTypesWereOK))


    Thursday, August 3, 2017 4:41 PM

All replies

  • You can provide the column name (as a single item list, so between curly brackets), as the last argument with Table.ReplaceValue.

    Syntax:

    Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list) as table

    Thursday, August 3, 2017 3:22 PM
  • Thanks.  Does this work if it is using other columns to determine the value and requires the use of each before the newValue and oldValue?

    Here is a simple example...

    = Table.ReplaceValue(Source
    ,each [Currency Code]
    ,each if [Account Is Stat] = "1" then "___" else [Currency Code]
    ,Replacer.ReplaceValue,{"Currency Code"})

    Thursday, August 3, 2017 3:39 PM
  • You'll find some working syntax samples for this here:

    http://www.thebiccountant.com/2017/07/23/transforming-a-column-with-values-from-another-column-in-powerbi-and-powerquery-in-excel/


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, August 3, 2017 3:55 PM
    Moderator
  • Thanks to both, but confused.  I have read that column and understand that syntax well.  The problem is that the step I listed above is changing the column type for every single column in entire table, which have already been changed to the appropriate Text, Date, Decimal, etc. in earlier steps.  Is there an approach that doesn't do this and leaves the column types alone?
    Thursday, August 3, 2017 4:07 PM
  • Sorry, didn't read properly.

    That's a pain! Seems to happen once you write "each" into the 2nd argument. When using plain text or a value, all column types are kept. Guess it's a bug, but wouldn't count on quick fix.

    @Marcel: Let me guess: You probably have a function ready that "restores" the previous types? That would save me some time :-) Thx a lot !!


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, August 3, 2017 4:30 PM
    Moderator
  • Edit: the following code restores the types, but it won't convert values to those types.

    @Imke, sure:

    = Value.ReplaceType(PreviousStep,Value.Type(StepWhenTypesWereOK))


    Thursday, August 3, 2017 4:41 PM
  • Thx Marcel,

    as far as I can see, it does what I would expect.

    (Still need to get my head around the types....)

    But as the values have been converted before, and only the type-definition of the columns seems to have been lost, it seems to fix the problem that occurred through the replacement appropriately. Or am I missing sth here?


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, August 3, 2017 5:29 PM
    Moderator
  • The following code will both restore the types AND convert values to those types.

    The code refers to previous step names CorrectlyTyped and WronglyTyped.

    TransformationsList = List.Transform(Table.ColumnNames(CorrectlyTyped), each {_, Type.TableColumn(Value.Type(CorrectlyTyped),_)}),
    TransformedTypes = Table.TransformColumnTypes(WronglyTyped, TransformationsList)
    

    Thursday, August 3, 2017 5:31 PM
  • Thx again Marcel.

    Filed a bug-report here: http://community.powerbi.com/t5/Issues/Bug-Table-ReplaceValue-kills-all-column-types/idi-p/225088#M11610

    Please upvote, chances are low if we don't get any votes here, thx.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, August 3, 2017 6:06 PM
    Moderator
  • Upvoted. Coincidentally I have been investigating "types" the last couple of weeks.

    An interesting but complex topic. I'm afraid some more "bugs" exist in this area...

    Thursday, August 3, 2017 6:30 PM
  • Great, thx!

    If you send links, you get my votes ;-)


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, August 3, 2017 6:35 PM
    Moderator
  • Thanks for the workaround.  Voted.  Surprised with this bug - the workaround works well, but has to impact performance.
    Friday, August 4, 2017 11:36 PM