none
M language query. Bulk replacement - match cell content instead of part of string RRS feed

  • Question

  • Hi all,

    I got the function below online and it worked great with my data. However, the bulk replacement works for any part of the string. Since I have in my replacement column strings like X, XY and ZYX, the X gets replaced in the other cells so it becomes a mess. I wanted the bulk replacement to work using the cell content instead of part of the string. Just not sure how to modify the function below. 

    Anybody could help?

    Thank you!


    let BulkReplace = (DataTable as table, FindReplaceTable as table, DataTableColumn as list) =>
        let
            //Convert the FindReplaceTable to a list using the Table.ToRows function
            //so we can reference the list with an index number
            FindReplaceList = Table.ToRows(FindReplaceTable),
            //Count number of rows in the FindReplaceTable to determine
            //how many iterations are needed
            Counter = Table.RowCount(FindReplaceTable),
            //Define a function to iterate over our list 
            //with the Table.ReplaceValue function
            BulkReplaceValues = (DataTableTemp, n) => 
            let 
                //Replace values using nth item in FindReplaceList
                ReplaceTable = Table.ReplaceValue(
                    DataTableTemp,
                    //replace null with empty string in nth item
                    if FindReplaceList{n}{0} = null then "" else FindReplaceList{n}{0},
                    if FindReplaceList{n}{1} = null then "" else FindReplaceList{n}{1},
                    Replacer.ReplaceText,
                    DataTableColumn
                    )
            in
                //if we are not at the end of the FindReplaceList
                //then iterate through Table.ReplaceValue again
                if n = Counter - 1 
                    then ReplaceTable
                    else @BulkReplaceValues(ReplaceTable, n + 1),
            //Evaluate the sub-function at the first row
            Output = BulkReplaceValues(DataTable, 0)   
        in
            Output
    in
        BulkReplace

    Monday, April 1, 2019 1:40 PM

Answers

  • let BulkReplace = (DataTable as table, FindReplaceTable as table, DataTableColumn as list) =>
        let
            //Convert the FindReplaceTable to a list using the Table.ToRows function
            //so we can reference the list with an index number
            FindReplaceList = Table.ToRows(FindReplaceTable),
            //Count number of rows in the FindReplaceTable to determine
            //how many iterations are needed
            Counter = Table.RowCount(FindReplaceTable),
            //Define a function to iterate over our list 
            //with the Table.ReplaceValue function
            BulkReplaceValues = (DataTableTemp, n) => 
            let 
                //Replace values using nth item in FindReplaceList
                ReplaceTable = Table.ReplaceValue(
                    DataTableTemp,
                    //replace null with empty string in nth item
                    if FindReplaceList{n}{0} = null then "" else FindReplaceList{n}{0},
                    if FindReplaceList{n}{1} = null then "" else FindReplaceList{n}{1},
                    Replacer.ReplaceText,
                    DataTableColumn
                    )
            in
                //if we are not at the end of the FindReplaceList
                //then iterate through Table.ReplaceValue again
                if n = Counter - 1 
                    then ReplaceTable
                    else @BulkReplaceValues(ReplaceTable, n + 1),
            //Evaluate the sub-function at the first row
            Output = BulkReplaceValues(DataTable, 0)   
        in
            Output
    in
        BulkReplace

    Try replacing this part with Replacer.ReplaceValue
    Monday, April 1, 2019 3:00 PM
  • If you want to replace whole strings only, you might want to try this approach instead: https://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!

    Saturday, April 27, 2019 4:57 AM
    Moderator

All replies

  • let BulkReplace = (DataTable as table, FindReplaceTable as table, DataTableColumn as list) =>
        let
            //Convert the FindReplaceTable to a list using the Table.ToRows function
            //so we can reference the list with an index number
            FindReplaceList = Table.ToRows(FindReplaceTable),
            //Count number of rows in the FindReplaceTable to determine
            //how many iterations are needed
            Counter = Table.RowCount(FindReplaceTable),
            //Define a function to iterate over our list 
            //with the Table.ReplaceValue function
            BulkReplaceValues = (DataTableTemp, n) => 
            let 
                //Replace values using nth item in FindReplaceList
                ReplaceTable = Table.ReplaceValue(
                    DataTableTemp,
                    //replace null with empty string in nth item
                    if FindReplaceList{n}{0} = null then "" else FindReplaceList{n}{0},
                    if FindReplaceList{n}{1} = null then "" else FindReplaceList{n}{1},
                    Replacer.ReplaceText,
                    DataTableColumn
                    )
            in
                //if we are not at the end of the FindReplaceList
                //then iterate through Table.ReplaceValue again
                if n = Counter - 1 
                    then ReplaceTable
                    else @BulkReplaceValues(ReplaceTable, n + 1),
            //Evaluate the sub-function at the first row
            Output = BulkReplaceValues(DataTable, 0)   
        in
            Output
    in
        BulkReplace

    Try replacing this part with Replacer.ReplaceValue
    Monday, April 1, 2019 3:00 PM
  • If you want to replace whole strings only, you might want to try this approach instead: https://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!

    Saturday, April 27, 2019 4:57 AM
    Moderator