Power BI vs Datazen

By Satalyst Brilliance on 20 Jul 2015

As you may have heard, Microsoft acquired Datazen in April 2015 to boost their BI dashboarding and mobility offering. This announcement has left a lot of people confused - where does Datazen fit in the Microsoft BI roadmap? Does Datazen compete with Power BI? Complement Power BI?

Read More

Power Query: Creating a parameterized function in M

By Satalyst Brilliance on 19 May 2015

This will be part two in my on-going series about Power BI, Power Query, and the general joys of data. In my last post I outlined how to iterate over a list in Power Query. But what if I want to iterate over a list and need to parameterize it with today’s date? What if I want to issue multiple web requests based on a list? This is exactly the scenario I had when trying to fetch crime data from the WA Police web site. First, I have to thank my colleague and web wizard John Chillemi who authored the azure website that I’m using. The WA Police site has all the data, but not in a nice HTML table, which is what Power Query needs. So, thanks to John, I can execute a single web request and get a webpage that looks like this:   The web request takes five parameters – Suburb, Start Month, Start Year, End Month, and End Year, as seen below: I’d like to execute that web request for every suburb in Western Australia (there are over 1300 by my count) so obviously this is a case for automation, which means I need an M parameterized…

Read More

Power Query: How to iterate over a list

By Satalyst Brilliance on 30 Apr 2015

If you’ve been using Power BI or you’ve seen the demos, then like me, you’re probably excited about Power Query. As a data nerd, I’ve been having a lot of fun pulling data sets off the web and mashing them up. Getting web data used to be quite a manual task for me, since I wasn’t about to write a custom screenscraper. I really love the “From Web” functionality that mines web pages for HTML tables. But I’m a developer, so of course I wanted to load a list of files, or a list of websites, and I had some trouble figuring out how to do it. Hopefully my experiences will help someone else. One of the tricks that I found was that the list that you want to iterate over has to be pulled into Power Query designer. So let’s start with looping over a list of files – this is something that EVERYONE will want to do at some point. So first, launch the Power Query designer from the Power Query ribbon, and use the folder that you want as your data source: As you can see, you get a lot of meta data about your folder contents. The most important…

Read More