Press "Enter" to skip to content

Category: Power BI

Multi-Parameter Website Scraping With Power Query

Published 2017-10-20 by Kevin Feasel

Callum Green shows how to build up a URL based off of multiple parameters, scraping data from a page for each permutation of parameters:

The sections highlighted in red are the parameters and sit in between some of the hard-coded URL text

Code Breakdown:

–          Text = http://www.boxofficemojo.com/monthly/?page=

–          Parameter = [Page]

–          Text = &view=calendargross&yr=

–          Parameter = [Year]

–          Text = &month=

–          Parameter = [Month]

–          Text = &p=.htm

This is a rather clever solution, and if your parameters are functionally dependent (unlike this example, where it was a simple cross join of the three domains), you can still use the solution the same way; you just need to populate your parameter combination table differently.

Comments closed

Spelunking In The SSRS REST API

Published 2017-10-16 by Kevin Feasel

Chris Webb uses Power BI to look at the new SQL Server Reporting Services 2017 REST-based API:

And the online documentation for the API is here:

https://app.swaggerhub.com/apis/microsoft-rs/SSRS/2.0

Interestingly, the new API seems to be OData compliant – which means you can browse it in Power BI/Get&Transform/Power Query and build your own reports from it. For example in Power BI Desktop I can browse the API of the SSRS instance installed on my local machine by entering the following URL:

This is something that SSRS has been missing for a long time.  I’m glad they’re introducing a real API.

1 Comment

Working With CosmosDB

Published 2017-10-11 by Kevin Feasel

Derik Hammer has an introductory article showing how to work with CosmosDB to store and use document-style data:

Querying Cosmos DB is more powerful and versatile. The CreateDocumentQuery method is used to create an IQueryable<T> object, a member of System.Linq, which can output the query results. The ToList() method will output a List<T> object from the System.Collections.Generic namespace.

Derik also shows how to import the data into Power BI and visualize it.  It’s a nice article if you’ve never played with CosmosDB before.

Comments closed

Power BI Conditional Formatting

Published 2017-10-11 by Kevin Feasel

Matt Allington shows us how to implement conditional formatting in Power BI:

Conditional formatting is one of the easiest ways to turn tables of boring data into a visual that almost makes the numbers jump out at you on a page.  There is nothing worse than looking at pages and pages of numbers and then trying to find insights from those numbers.  Conditional formatting helps you format your tables (and matrices) of data so that the patterns and outliers in the data are easier to spot at a glance.  Take a look at the before and after images below and see how much easier it is to see the variations in performance.

It turns out to be pretty easy, so check it out.

Comments closed

Timeline Storyteller Custom Visual

Published 2017-10-04 by Kevin Feasel

Devin Knight continues his Power BI custom visuals series:

In this module you will learn how to use the Timeline Storyteller.  The Timeline Storyteller is a great way to tell a story about your data. It gives you the ability to create multiple representations of your data and then pull them together by creating multiple scenes.

This is a flashy visual and I think Devin’s set is an excellent example of where you might want to use it.

Comments closed

Variables In DAX

Published 2017-10-04 by Kevin Feasel

Matt Allington shows us how to use variables in DAX:

Variables in DAX is a relatively new feature and is available in

  • Power BI Desktop
  • Excel 2016
  • SSAS Tabular 2016

Variables are not available in Excel 2013 or Excel 2010.

Click through to see how to assign and use variables.  It’s interesting to see how they’re local to a measure, so at this point at least, you can’t share variables between measures.  Given what DAX is supposed to be, that’s probably the right choice.

Comments closed

Ribbon Charts

Published 2017-09-29 by Kevin Feasel

Reza Rad explains what ribbon charts are and why it’s good that they’re now available in Power BI:

Ribbon Chart shows bigger value in each column at the top, then the next value comes after. Look at the sales amount value for female and male in 2005 and 2006. In 2005, Female (Black) had more sales than Male. However, in 2006, Male (Green) generated more revenue than the female, so it is on the top for the 2006 column.

The main benefit of the ribbon chart is in this re-ordering, so it’s easier to tell which categories are largest in a specific time period.

Comments closed

A Power BI Report To Track Power BI Changes

Published 2017-09-28 by Kevin Feasel

Julie Smith has found the only way to keep up with Power BI changes—using a Power BI report:

Microsoft makes major improvements via monthly and sometimes weekly releases to Power BI. In my time working on Power BI projects at Innovative Architects, I have found that the only way to stay on top of the frenetic pace of Power BI’s improvements is to closely follow its blog and other social media feeds.

This page is a Power BI report (how meta right?) built off of the content from Microsoft’s Power BI blog. I have set it up to run daily and refresh. Please let me know in the comments if you feel that the data has become stale. As part of building this report, I have added some curated slicers–things like PBI Service, PBI Desktop, Gateways, Connectors– and blogging content tags such as Contest or Webinar. Keep in mind that this is my best effort and there is no guarantee on the accuracy of my tagging. I provide this as my gift to you “as is” with no expressed or implied warranty.

Click through to see the report.

Comments closed