Convert the data into same rate in new column

[pye]

Hi there!

I have two tables here, conversion table and data table. The value in data table is inserted by user with different mass unit (g, kg, lb, oz etc.). I would like to convert the inserted value into same unit in kg, referring to the conversion table.

Here is the illustration, new column (green, column I) created to show the convertion of inserted value in column G-H. This can be achieved by multiplication and vlookup from the coversion table. However, is there anyway to achieve it in power query?

Currently, I am using hardcode in power query M language, by adding a new columnn and using if / else function as suggested by one of the questions.

if [unit] = "g" then [value]*0.001 else if [unit] = "kg" then [value]*1 else if [unit] = "lb" then [value]*0.45359237 else if [unit] = "oz" then [value]*0.02834952 else if [unit] = null then [value] else "error"

Checking if there is a better turn around on this as there are more than 30 conversion rate to type in. Thanks!

[pye]

Hey @wdx223_Daniel thanks for your help on this custom formula. May I check is there is topic related on this? I would like to do some learning, but couldn't find it such as the ? validation.

As I have another doubt on writing the code if I would like to add an additional custom column to show the convertion unit referring to the conversion table, when there are different "to unit" in the conversion table.

Tried to add a new custom column with code: ConversionRate{[#"From unit"=[Unit]]}?[Convert to Universal Unit])

It is not working, as seems the final result is a number and causing error.

[wdx223_Daniel]

according to your snapshot sample data, you can try this code to add two columns in one step.

NewStep=#table(Table.ColumnNames(DataTable)&{"To Value","To Unit"},List.Transform(Table.ToRows(PreviousStepName),each let a=ConversionUnitTable{[#"From unit"=_{2}]}?,b=if a=null then {null,null} else {a[Conversion Rate]*_{1},a[To unit]} in _&b))

and about the learning of M code, i suggest you to read the book of "M is for data monkey", which is my first M book.

[pye]

thank you! i learnt a lot from this

[wdx223_Daniel · Accepted solution]

=ConversionTable{[#"From unit"=[Unit]]}?[Conversion Rate]?*[Value]