locked
Replace multiple values in a single step RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    1
    Sign in to vote

    I have built a pretty complex report that first derives data from Power Query. 

    Now I need values in one of the columns to be replaced with some other values. There are plenty of these to replace though and don't want that many steps to show up in Power Query. I ideally don't want to add a new column and use if condition to get the values replaced as I've already used the existing column in a number of places. I tried merge but that creates a whole new query that in turn loads into a new table altogether. 

    Is there any way to replace multiple values. Basically a lookup/replace kind of thing but end result should be in the same column, not a new column? 

    Basically something like replace a with B, C with D, E with F and so on and say there are 50 such cases.




    • Edited by VivDev Friday, August 7, 2015 8:10 PM
    Friday, August 7, 2015 8:08 PM

Answers

  • Question
    Sign in to vote
    5
    Sign in to vote

    If all the values involved are text, you can put the substitutions into a single record that defines the replacements and then use Table.TransformColumns with Record.FieldOrDefault to lookup the replacement. Here's an example:

    let
        Source = Table.FromColumns({{"One", "2", "Three Point Five", "For", "five"}}),
        Substitutions = [
            #"2" = "Two",
            #"Three Point Five" = "Three",
            For = "Four",
            five = "Five"],
        Substituted = Table.TransformColumns(Source, {{"Column1", each Record.FieldOrDefault(Substitutions, _, _)}})
    in
        Substituted

    Saturday, August 8, 2015 2:28 AM

All replies

  • Question
    Sign in to vote
    5
    Sign in to vote

    If all the values involved are text, you can put the substitutions into a single record that defines the replacements and then use Table.TransformColumns with Record.FieldOrDefault to lookup the replacement. Here's an example:

    let
        Source = Table.FromColumns({{"One", "2", "Three Point Five", "For", "five"}}),
        Substitutions = [
            #"2" = "Two",
            #"Three Point Five" = "Three",
            For = "Four",
            five = "Five"],
        Substituted = Table.TransformColumns(Source, {{"Column1", each Record.FieldOrDefault(Substitutions, _, _)}})
    in
        Substituted

    Saturday, August 8, 2015 2:28 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Bda,

    I have exactly the same problem, but with 100s of references, in my approach I would like to compare the "text values" existing in another table, where column A would have the existing values and B the new values to use, if matching with A.

    Let me explain better... In my Master table, I have 100s of different StockCodes that I need to breakdown in Groups and/or SubGroups, every-time I need to Forecast something I need to check reference by reference. Our DataBase haves 20 years of data (it's hard to change things), and it's organized for production, not for sales.

    So the idea of matching the values with a Second Table comes from the need to update this Second Table every-time some new product is added... and consequently update the Groups in my Master Table

    Any ideas?

    Thanks in advance,

    Alex





    • Edited by AFerrete Thursday, September 3, 2015 2:18 PM
    Thursday, September 3, 2015 12:54 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    The values you want to replace: Do they represent the whole content of the fields or are they substrings/part of the cell content?

    Imke Feldmann TheBIccountant.com

    Thursday, September 3, 2015 2:27 PM
  • Question
    Sign in to vote
    3
    Sign in to vote

    Alex,

    You can build the "Substitutions" record dynamically. Let's assume that you have a table named "Subst_tbl" in your workbook that contains the distinct substitutions in two columns, 'Old' and 'New'. The following M code generates a single record that can be used with Curt's solution. 

    After referencing the table in step Source, the trick is to pivot the table without aggregation method (see screen shot). Then, you need manually type the Table.ToRecords step as it is not available in the UI. Lastly, select the single cell returned, right-click and select "drill down".

    let
    Source = Excel.CurrentWorkbook(){[Name="Subst_tbl"]}[Content],
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(Source, {{"Old", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(Source, {{"Old", type text}}, "en-GB")[Old]), "Old", "New"),
    ListofRecord = Table.ToRecords(#"Pivoted Column"),
    Substitutions = ListofRecord{0}
in
    Substitutions

    Monday, September 14, 2015 5:54 PM
  • Question
    Sign in to vote
    2
    Sign in to vote

    Building on Imke's approach -- an alternate 'bulk replace' approach is provided below:

    let
        Tbl_1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Tbl_2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        CnvrtCol = Record.FromTable(Table.RenameColumns(Tbl_2,{{"Old", "Name"}, {"New", "Value"}})),
        Sbstd = Table.TransformColumns(Tbl_1, {{"Orig", each Record.FieldOrDefault(CnvrtCol, _, _) }} )

    in
        Sbstd

    *************************

    § two tables are ingested -- 'Table1' (column to be changed & labeled as "Orig") and 'Table2' (with "Old" & "New" header labels).

    § header labels in Table2 are changed to "Name" and "Value" - then wrapped with a 'Record.FromTable' function - removing need to pivot and click thru.

    § final function call remains unchanged.

    Thanks - Drewbbc

    aabc

    Thursday, August 17, 2017 12:54 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Drewbbc,

    yes, I like that as well!

    It's funny, because I used the Record.FromTable-function just yesterday to transform a parameter table to a "para"-Record, which is an ideal dispenser for multiple parameters I think. The function looked like so:

    (ParaTable as table) =>
Record.FromTable(Table.RenameColumns(ParaTable, List.Zip({Table.ColumnNames(ParaTable), {"Name","Value"}})))

    Assuming that this table has only 2 columns, you just have to pass the table-name, so no need to type in the column names individually.

    Cheers, Imke

    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 17, 2017 1:20 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Just came across this thread when trying to do something similar.

    I needed to replace any matching text ("" or null to unknown) in the entire table and did this

    = Table.TransformColumns(input_table,{},each if List.Contains({null, ""}, _) then "UNKNOWN" else _)

    Where I have the list in List.Contains you could replace this with a list that contains as many things as you wish to match against. If you wanted to do this for specific columns this would also be possible. I am assuming the {} in the second argument specifies the entire table rather than just specific columns, so one could change this if you didn't want all the columns.


    Tuesday, September 12, 2017 2:12 PM
  • Question
    Sign in to vote
    2
    Sign in to vote

    Just had a look into it and the {} turns out where you can put in alternative transformations for different columns, while the next parameter is your default transformation. Like follows:

    = Table.TransformColumns(#"Trimmed Text",{{"Date", each if _ = "" then null else _},{"Created", each if _ = "" then null else _}},each if List.Contains({null, ""}, _) then "UNKNOWN" else _),

    quite a handy little function.

    Don't know how computationally efficient it is ....

    • Edited by Ben 167 Tuesday, September 12, 2017 2:40 PM forgot some {} in code
    Tuesday, September 12, 2017 2:30 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    How to change code if I want to search part of the cell content and not a whole cell content? 
    Thursday, February 22, 2018 7:44 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    You'll find some methods for it in this blogpost: http://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/

    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!

    Friday, February 23, 2018 6:03 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    hi,

    I think I understand the idea of the above solution (you put the transformations into a separate list/function and then use one line of M in the main query to do the replacements), but, there isn't enough in that answer to tell me how to use it.

    How do I use it?

    androo2351

    Wednesday, January 9, 2019 12:27 PM