PowerQuery

[DEADP00L]

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

[#2]

@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

[#3]

@DEADP00L 

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.  

[#4]

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

[#5]

@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)

[#6]

@DEADP00L 

Quite impressive. It is not easy to add those long formulas to the names manager.