Weird localization problem in DAX editor 17 September 2014 Nico-Jacobs Business Intelligence, Excel, Power BI, SQL Server BI 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!
Excel Power Query supports Multi-value Lookup columns in SharePoint lists 15 September 2014 Nico-Jacobs Business Intelligence, Excel, Power BI 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.
Power BI Training: Business Intelligence with Excel 29 January 2014 Nico-Jacobs Business Intelligence, Excel Power BI allows business users to solve certain business intelligence problems without the need of IT specialists. Since 2010 Power Pivot allows Excel users to create their own data models and do advanced analytics on this. Excel 2013 out of the box contains Power View as well, allowing people to create advanced and interactive reports within Excel. And recently Microsoft added Power Query, to load and filter data from a variety of data sources, and Power Map, to create stunning geographical reports. These four products together are named Power BI. From February 2014 on, U2U will run a 5 day course, oriented towards business users who are already familiar with the basic Excel functionality, to learn about these advanced business intelligence capabilities of Microsoft Excel. To give you a bit of an idea what becomes possible with Power BI, we included a small video illustrating some of the skills you will learn in this new course. For practical details on this course, check out http://www.u2u.be/CC/UBIPB