Bulk find and replace

Power Query
1

HI,

I would like to know how bulk find and replace works. So in the PowerBI file that I have attached, there are two tables: ’ find and replace’ and ‘inventory table’. I want to have a new column ‘Bin category’ in my inventory table, which ought to be based on the Bin category column in the ‘find and replace table’ . I know that I can achieve this by using related function by joining the two tables, but I would like to use bulk find and replace please.
Find and replace table.xlsx (8.9 KB)
Inventory report.pbix (27.6 KB)
inventory table.xlsx (9.0 KB)

2

Hi @musa.fasih,

See if this works for you, just paste it into a new blank query (in your file).

let
    SearchIn = List.Buffer( List.Transform( #"Find and replace table"[Bin Location], each Text.Trim( Text.Clean( Text.From(_) )) )),
    Result = List.Buffer( List.Transform( #"Find and replace table"[Bin Category], each Text.Trim( Text.Clean( Text.From(_) )) )),
    Source = #"Inventory table",
    BulkReplace = Table.ReplaceValue( Source, each [bin location], each List.ReplaceMatchingItems( {Text.Trim( Text.Clean( Text.From([bin location]) ))}, List.Zip( { SearchIn, Result }), Comparer.OrdinalIgnoreCase ){0},Replacer.ReplaceValue,{"bin location"})
in
    BulkReplace

I hope this is helpful.

1 Like
3

Hi @musa.fasih , did the response provided by @Melissa help you solve your query?

If not, how far did you get and what kind of help you need further?

If yes, kindly mark as solution the answer that solved your query.

4

HI Melissa,

It is not working. The M code just turns red and I am unable to run it. Also, the new column ought to be “Bin Category” not bin location, I think the code replaces values in the existing bin location column.

Regards,
Musa

5

Hi @musa.fasih,

Sorry to hear that, attached a working example.

You described your query as “find and replace” so yes the initial solution does a lookup on Bin Location and replaceses that value with the Bin Category (if found). But the attached file also included an example that adds the result as a new column. Choose whatever works best for you.

Bulk Find and Replace.pbix (28.5 KB)

I hope this is helpful.

1 Like
6

Hi Melissa,

It works well now, though I am having trouble replicating the steps on my actual dataset- the data that I gave you was dummy data. So for example, if I want to go ahead with the new column approach, I am not able to insert the ‘SearchIN’ step before the ‘Source’ step.

Regards

Musa

7

@musa.fasih, just open the Advanced Editor window and copy it in. Note that you’ll have to update Table and Column name references in that code to match data in your production file. And don’t forget to put in a comma at the end of each line (except the last one before the IN clause).

I hope this is helpful.

1 Like
8

Sorry my m-code is very weak, which is why i am still having issues.

I have tried to replicate the steps using the M code in the Adanced Editor but I am still having issues.
image

As shown in the image, I have inserted steps in the Stock whse Detail table, which is my main inventory table which consists of many columns, and has bin location and bin category column as well. After the navigation step I inserted the ‘SearchIN’ and then ‘ReplaceWith’, and in the m-code I replaced column and table names with the ones that I have. Then, I tried to insert the source step, replicating the m-code in the advanced editor window to include the following code:

= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“Lc67FYNQDMDQXagpns1/Fg57xJ4+EVGpSve+pzFimqdPTM9MJJHGQizGSqzGRmzGTuzGQRzGSZzGRVz/iPFOhwWhJASEkhAQSkJAKAkBoSQEhJIQEEpCQCgJAaEk5HinEhJCS0gILSEhtISE0BISQktICC0hIbSEhNASEkJLWMY7/RGeLw==”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#“stock_code” = _t, #“Bin location” = _t])

In the final line, I replaced “Inventory code” with “stock_code”, but this step is only retrieving the values in the table from the dummy data file that you have send to me, not the data that I am working with.

9

Hi @musa.fasih,

That’s correct. You need to replace that entire piece of code with another table reference

Here’s a link to a video that covers most issues you’re likely to encounter and how to resolve them.

1 Like
10

Hi @musa.fasih, It’s great to know that you are making progress with your query.

Please be reminded that asking more than one question in a forum thread and asking question after question in the same forum thread around the same project or piece of development work is considered inappropriate.

You can create a separate thread for your other questions related to this inquiry so that other users can easily check the details of your queries.

For further questions related to this post, please make a new thread. More details can be found here - Asking Questions On The Enterprise DNA Support Forum

11

My Apologies.

12

Thanks for your help @Melissa ! I appreciate it.