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
Super UserHi 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?
zvmHi,
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-msftMicrosoft Employee
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!