One of my students (thanks, Stefan!) discovered during the PowerBI course a bizarre localization issue in the DAX editor of Excel and SQL Server Data Tools, which is easy to replicate:
Install Office 2013 or SQL Server data tools on a machine with a locale which uses a comma as decimal separator (I’m from Belgium, a Belgian locale will do).
Then create a Power Pivot model, add a table to it, and in the Table editor create a calculated column which uses at least one parameterized function. In this function provide as an argument the constant string “,”. For instance you could use as an expression =IF(1=1;”,”).
As soon as I enter this in the editor, the editor replaces the constant string “,” with “;”, but still shows in the outcome the correct value “,”.
Something similar happens when I type a dot as constant string.
When this is what I type in Power Pivot:
This is what I get when I press enter in the expression editor:
Notice how the formula in the editor uses a comma instead of a dot, but the evaluated expression (CalculatedColumn1 in my case) uses the dot I originally typed: what I see is not what I get. And to make things worse: when I edit the formula (e.g. adding an else parameter), it really starts to use the comma I never typed, and replaces in the expression the comma with a semi-column I never typed nor see in the evaluated column:
So, be careful when using constant strings “.” or “,” in DAX!
Power Query is a handy tool to load all sorts of data in Excel or PowerBI. One of the supported data types is SharePoint lists. But Power Query does more than the straight-forward loading of data in a single list. If a SharePoint list contains a lookup column referring to another list, Power Query will allow you to join these two lists together easily. Ant top of the bill: it works for multi-value lookup columns as well.
Let’s walk through an example. I start creating in SharePoint a plain vanilla list with some values in there, e.g. a list of Belgian beers:
Then I create a second list, e.g. colleagues, and I add a column where I store their favorite beer:
I input some data into this list:
Next we want to load this into Excel or PowerPivot, so we launch Excel, go to the Power Query ribbon (download, install and configure the Power Query add-on if you don’t see the ribbon) and select From other sources –> From SharePoint List
In the next dialog specify the URL of your SharePoint site (not the SharePoint list URL!) and click OK. Power Query will now load a list of all SharePoint lists on this site and allow you to select the list you want to consult. We right click our list of colleagues and select edit, which opens up the Power Query editor window (in the next screenshot I moved the Title column further to the right and did not include many other columns to improve readability):
We see that Power Query loaded the Favorite Beer column as a nested table. By clicking on the split arrow icon to the right of the Favorite Beer header, we can ask to either expand the table of calculate aggregated values. Let’s first try an expand, and just select the name of the favorite beer:
If we click OK, we get this result: one row for each combination of a colleague with his or her favorite beer:
We could also ask Power Query to aggregate the beers per colleague, in our example we count how many favorite beers each has, and what the average alcohol percentage is (our beer list contains the alcohol percentage of each beer):
The result of this would be the following data set:
From this point on, we can continue loading this data in Excel, or via Power Pivot into Power View, Power Map or plain Excel Pivot Tables and charts.