Forum Discussion

DEADP00L's avatar
DEADP00L
Copper Contributor
Aug 26, 2020

PowerQuery

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

 

BI & Data Analysis
excel

5 Replies

  • ExcelExciting's avatar
    ExcelExciting
    MVP
    Aug 26, 2020

    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

     

     

     

  • Celia_Alves's avatar
    Celia_Alves
    MVP
    Aug 26, 2020

    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

     

    • DEADP00L's avatar
      DEADP00L
      Copper Contributor
      Aug 27, 2020

      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)

       

       

Resources