Power Query - update column

[zvm]

Hi,

Is it possible to update column with Power Query ("M") like with SQL update command?

Here is the situation:

- one column is free text where I need to search for a certain patterns (1, 2 or 3 different in each row)

- second column is let's say category_code

- result depends on text patterns and category_code

I need to go through all rows and group them according to their test patterns and category_code and assign something like product_code.

The source is Excel file (3 of them, but that is not important), not database. And database cannot be used. So, solution could be developed in Excel as well.

I can filter rows with Power Query . IT looks like this:

 #"Filtered Rows" = Table.SelectRows(#"Appended Query", each Text.Contains([opis], "70") and Text.Contains([opis], "400") and [šifra] = "A" or Text.Contains([opis], "25") and [šifra] = "C" or Text.Contains([opis], "500") and Text.Contains([opis], "60") and [šifra] = "B" or Text.Contains([opis], "0,7") and [šifra] = "A")

Pseudo code would be:

if column 'description' contains "70" and contains "400" and column cat_code = "A" then prod_code = A5 

if column 'description' contains "25" and column cat_code = "B" then prod_code = B2

if column 'description' contains "agg" and contains "40" and column cat_code = "A" then prod_code = A2

...

Or SQL update:

update Table
set prod_code = "A5"
where description like "%70%" and description like "%400%"
and cat_code = "A"

And I need approximately a hundred different combination to check and update. 

Is there any solution to this without SQL, ie. within Excel, Power Query?

[Thejeswar]

Hi @zvm,

Power Querygives you option to create a custom column which can have if statements in it. 

Did you try that out...?

But when you say that there are 100 similar combinations to replace we should go for something better than an IF Statement or an update command.

Can you tell me if the column 'description' will have a distinct number of entries? or Can it have any number of new entries getting added often to description column?

[zvm]

Hi,

Custom column and If statements available there are pretty simple and cannot solve this. IF can only have one condition and I need 2 or more od different columns.

It is not "to replace" , I need to create or fill another column based on values from those two: description and cat_code.

Descrption can have any number of entries. typically it has several thousands. And I am interested in about hundred combinations. But those hundred combination can occur in several hundred records. For instance:

"Bottle 400 ml, 50 mg"

and 

"Bot. 400ml,50mg"

are both valid entires that describe the same product and both should get the same prod_code. I search for "400" and "50", for instnace, to filter them. But how can I assign prod_code programmaticaly?

[v-yulgu-msft · Accepted solution]

Hi @zvm,

If there is no existing [prod_code] field in source table and you are trying to insert a new one, please add a custom column like this:

=if Text.Contains([description],"70") and Text.Contains([description],"400") and [cat_code]="A" then "A5" else if Text.Contains([description],"agg") and Text.Contains([description],"40") and [cat_code]="A" then "A2" else if Text.Contains([description],"25") and [cat_code]="B" then "B2" else " "

If there is already an esisting [prod_code] field and you want to update its value with correct prod numers, please add a further step based on above change.

#"Replaced Value" = Table.ReplaceValue(#"Added Custom",each [prod_code], each [Custom] , Replacer.ReplaceText,{"prod_code"})

Best regards,

Yuliana Gu

[zvm]

Hi Yuliana,

Thanks! That's it.

Everything is clear and straightforward. I was actually thinking myself that is the way to go via "if-else if". But I didn't know proper M syntax.

cheers!