Forum Discussion
PowerQuery
DEADP00L The reasoning process when using Excel formulas is different than when using power Query. Whilst with the formulas you can work on a cell by cell case, Power Query is mostly used to work at the column level. It is possible to act at the "cell" level but it knowledge of M code language.
That being said, you might still be able to use Power Query to solve your problem without having to modify the M code that PQ generates on each step.
Tow questions for you:
1 - Why would you want to use PQ instead of the formulas that you have working in Excel already?
2 - What are the formulas that you are currently using?
If you are using VLOOKUP with TRUE argument, take a look at this video and see if it helps.
https://www.youtube.com/watch?v=EYgKciBr_dg&t=217s
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)
Quite impressive. It is not easy to add those long formulas to the names manager.