Aug 26 2020 05:28 AM
Hi - thanks for taking the time to look at my question.
I am very familiar with Excel's front end and formulae but have recently started exploring PowerQuery.
Everything, I want to achieve, I can do using Excel's formula options BUT, I was wondering if I could do the same thing in PQ. If so how?
I have a couple of tables. The main table contains product data and a small 5 row table contains packaging data. Essentially, I am comparing the product dimensions against the packaging limits and it the product fits a pack type then return that.
When I first excitedly jumped over to PQ, it soon became clear without a direct relationship and the knowledge of if/how to cross reference I was lost.
Any advice or just a plain "can't be done" would be great. Thanks
Oli
Aug 26 2020 06:12 AM
@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
Aug 26 2020 06:23 AM
I'd recommend this resource Modern Excel webinar: Advanced Data Preparation Techniques with Power Query from Ken Puls. Independently of skills level in Power Query it'll be very useful.
Aug 26 2020 06:30 AM
Hi @DEADP00L
Yes it possible, please share the same data to advise you the right solution
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more
Aug 27 2020 01:56 AM
@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)
Aug 28 2020 08:59 AM
Quite impressive. It is not easy to add those long formulas to the names manager.