Forum Discussion
Helper IIPower Query - update column
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
Helper IIHi,
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?
Microsoft EmployeeHi 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!