How to change units of multiple columns in one table?

[Jsaidtecnimont]

Hello everybody,

I have a table that has specific columns that are specifically in the imperial units of measure and I want to change all the quantities in my table to become imperial. I've accomplished it but I think it's very inefficient as you can see below, is there a way where I can do this all in one step? I want to also do this in power query so that I can automate the processing of the same looking tables in the future too.

#"QTY IFC to metric" = Table.ReplaceValue(#"QTY IFR to metric", each [QTY IFC], each if[UM QTY] = "CubicYard" then [QTY IFC] * 0.76455486 else if [UM QTY] = "Foot" then [QTY IFC] * 0.3048 else if [UM QTY] = "Pound" then [QTY IFC] * 0.45359237 else [QTY IFC], Replacer.ReplaceValue, {"QTY IFC"}),
#"QTY VD to metric" = Table.ReplaceValue(#"QTY IFC to metric",each [QTY VD], each if[UM QTY] = "CubicYard" then [QTY VD] * 0.76455486 else if [UM QTY] = "Foot" then [QTY VD] * 0.3048 else if [UM QTY] = "Pound" then [QTY VD] * 0.45359237 else [QTY VD], Replacer.ReplaceValue, {"QTY VD"}),
#"QTY ACC to metric" = Table.ReplaceValue(#"QTY VD to metric",each [QTY ACC], each if[UM QTY] = "CubicYard" then [QTY ACC] * 0.76455486 else if [UM QTY] = "Foot" then [QTY ACC] * 0.3048 else if [UM QTY] = "Pound" then [QTY ACC] * 0.45359237 else [QTY ACC], Replacer.ReplaceValue, {"QTY ACC"})

thank you in advance! 

[v-jingzhang · Accepted solution]

Hi @Jsaidtecnimont 

This is possible. Please try this

ReplaceMultipleColumns = Table.ReplaceValue(#"previous step name", each List.Contains({"CubicYard", "Foot", "Pound"}, [UM QTY]), each if [UM QTY] = "CubicYard" then 0.76455486 else if [UM QTY] = "Foot" then 0.3048 else if [UM QTY] = "Pound" then 0.45359237 else 1, (x,y,z)=> if y then z * x else x, {"QTY IFC", "QTY VD", "QTY ACC"})

I get the idea from an old thread Replacing values in multiple columns based on condition in Power Query and an article Replace Values in Power Query M (Ultimate Guide) - BI Gorilla. Hope they will be helpful!

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.