Hi Mynda & fellow PQ forum sers, I have just finished the course and have started working on my first big project with PQ. My data source starts life with around 10k rows and grows to around 300k rows so I am trying to be as efficient as I can.
The requirement I have is simple enough in that I want to evaluate a maximum value from a pair of month numbers. I extract these using Date.Month - so far so good.
All I could find in the M reference library is Value.Compare which just gives me 1,0,-1 depending on the values and so I then use a conditional column to choose the maximum.
I have to use the technique 3 times per record and with my data size at 10k rows I can already feel the query puffing and so by the end of my year it will be quite slow indeed.
I was hoping I had just been blind and missed an in-built function that performs the same task as Excel's Max(#,#) - hope someone out there can help.
Regards, Chris Timbers (UK)
Hi Christopher,
There are 2 functions for MAX in Power Query: List.Max and Table.Max.
Are you trying to get the Max number from an entire column? Or trying to compare values from the same row, in 2 columns?
Hi Catalin, thanks for looking at my post.
I am trying to compare two fields held in columns on a row-by-row basis. I have to compare the Date.Month value extracted from the end date of a holiday booking and compare it to the current financial month (January = 1 and so on). I then do some financial value calculations based around knowing the Max of these two integers.
There are are two other very similar comparisons I need to do for the same holiday record - one uses the MIN of two Date.Month (#) results. Each holiday is a single row and I need to calculate three values for each holiday and then repeat for the next row etc. The min & max functions were always the easiest tools when I did the report in XL hence my question. I will look up List.Max and Table.Max on the M Language reference website.
Hi Christopher,
I'm sure there should be another way to get to your results, it's not a good idea to replicate in PQ what you do in excel, in PQ you have to think different. You cannot use rows and cells in functions, only entire columns, so your strategy should be different.
If you can upload a sample data and desired result, I will try to see if it can be one with a different approach.
