Then click Add Column > Custom Column, and set it up like this: Then click the at the top right of the new column that was just added:
Once you have it selected, you should see something like this:Ĭlick Home > Merged Queries, and fill out the screen like this, using whatever name you used for your Unit Conversion Matrix where you see UnitConversionMatrix: Select the query for your Sales Table, to edit it. and click OK to see something like this:Ĭlick on the BaseUnit column to select it, then click Transform > Fill > Down, to get something like this: So that's what these next two steps will do.Ĭlick Add Column > Custom Column, and fill out the screen like this: You'll need to add your Base Unit to each row, so that it is available later, for the final table. Select the query for the Unit Conversion Matrix, to edit it. ( When I brought your Sales Table in as a source, I just called it "Sales." I called your Unit Conversion Matrix "UnitConversionMatrix.") In other words, you'll have two queries: one for your Sales Table and one for your Unit Conversion Matrix. Start by loading both tables into PowerQuery. I changed "Rolls" to "Roll" and "LargeBox" to "Large Box." With that said. Likewise you can't use "Large Box" and "LargeBox" for that purpose. You can't use "Rolls" in one and "Roll" in the other to refer to the same thing. First off, your data needs to be consistent in both your source and conversion tables if you intend to reference the information between the two.