PowerQuery

Celia_Alves Thank you for your reply and the link to Oz's video. Great stuff.

My initial reasoning to use PQ was to try and push as much of the computation into the back-end. Now that I have completed it I would still like to learn 'for next time'. 

🙂

The sort of formulas I am using are quite standard and I have used named formulas to improve readability. some examples:

packaging.cost		=IF(HasNoDimensionData,"",SWITCH(packaging.use,PackType1,PackType1.Cost,PackType2,PackType2.Cost,PackType3,PackType3.Cost,PackType4,PackType4.Cost,PackType5,PackType5.Cost,PackType6,PackType6.Cost,PackType7,PackType7.Cost))

packaging.type		=IF(
			AND(Stock_Details[@[Min Dimension]]<PackType1.DimensionMin,Stock_Details[@[Median Dimension]]<PackType1.DimensionMedian,Stock_Details[@[Max Dimension]]<PackType1.DimensionMax),PackType1,IF(
			AND(Stock_Details[@[Min Dimension]]<PackType2.DimensionMin,Stock_Details[@[Median Dimension]]<PackType2.DimensionMedian,Stock_Details[@[Max Dimension]]<PackType2.DimensionMax),PackType2,IF(
			AND(Stock_Details[@[Min Dimension]]<PackType3.DimensionMin,Stock_Details[@[Median Dimension]]<PackType3.DimensionMedian,Stock_Details[@[Max Dimension]]<PackType3.DimensionMax),PackType3,IF(
			AND(Stock_Details[@[Min Dimension]]<PackType4.DimensionMin,Stock_Details[@[Median Dimension]]<PackType4.DimensionMedian,Stock_Details[@[Max Dimension]]<PackType4.DimensionMax),PackType4,IF(
			AND(Stock_Details[@[Min Dimension]]<PackType5.DimensionMin,Stock_Details[@[Median Dimension]]<PackType5.DimensionMedian,Stock_Details[@[Max Dimension]]<PackType5.DimensionMax),PackType5,IF(
			AND(Stock_Details[@[Min Dimension]]<PackType6.DimensionMin,Stock_Details[@[Median Dimension]]<PackType6.DimensionMedian,Stock_Details[@[Max Dimension]]<PackType6.DimensionMax),PackType6,IF(
			AND(Stock_Details[@[Min Dimension]]<PackType7.DimensionMin,Stock_Details[@[Median Dimension]]<PackType7.DimensionMedian,Stock_Details[@[Max Dimension]]<PackType7.DimensionMax),PackType7, "No")))))))

PackType1.Cost		=INDEX(Packaging[Cost],PackType1.index)

PackagingType1.index	=MATCH(PackType1,Packaging[Type],0)