Power Query: Excel(lent) functions
5 April 2017
Welcome to our Power Query blog. Over the last few weeks I have shown some of the tasks that can be achieved with M language and functions. Today I look at how to replicate some familiar Excel formulas.
As I pointed out in M-Powered, Power Query functions are not always the same as Excel formulae. Since most Power Query users will already have their favourite Excel formulae, it is useful to know how to replicate those that are most commonly used. OR() and AND() seem like a good place to start.
OR()
I have the data shown in the screen below:
I decide that I simply must add a column to flag all data where the Amount is $120 or the Item_Group is ‘Floor’.
In Excel the formula for the new column would be:
=OR([Amount] = "120", [Item_Group] = "Floor")
In the Query Editor, I could try filtering on Amount and then Item_Group, but this would give me those rows that meet both criteria, i.e. AND() instead [and there is a better way to do AND() too, as I will explain later].
Instead, I will create a new custom column, which I will call ‘120_OR_Floor’, by going to the ‘Add Column’ section and choosing to ‘Add Custom Column’.
As the screen above shows, the formula I have used is:
=if List.AnyTrue({[Amount]="120",[Item_Group]="Floor"}) then "Matches" else "Nope"
List.AnyTrue is Boolean (i.e. it can be TRUE or FALSE) which takes a list of statements and returns TRUE if any of the statements are true. So, I can easily extend this to add any conditions that I would like to test for.
AND()
As I mentioned above, filtering can leave me with the rows that meet each criterion required, but it can be more useful to flag which rows meet the criteria (which means I can still see those that don’t).
The function I can use is in fact very similar to the one I used for OR(), and I repeat the process to add another custom column to my query:
And this time, the function is clearly related to the last one I used (and yes, I did copy and paste to speed things up!):
if List.AllTrue({[Amount]="120",[Item_Group]="Floor"}) then "Matches" else "Nope"
So, I have my equivalent to OR() and AND(), but just to prove it, below I have uploaded the data to a worksheet and added the Excel columns that would serve a similar purpose:
Not all formulas are so easy to replicate (yet!), so no doubt I will be visiting ways of reproducing other useful Excel formulae in future blogs. Next time though, I will be making a date with calendar creation…
Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!
