Category: Power BI

Dany Hoter shows how to use Power Query to join one row to the next in a data set (given similar criteria) and do day-by-day comparisons:

I want to analyze the daily prices of certain commodities and be able to show the patterns of daily changes side by side. I want to calculate predictably the differences between each row and the row before. Each row represents data for a day, so the difference between rows is the daily change or in some cases, several days change.

I downloaded from Quandl 50 years of daily prices of gold and silver, and my goal is to calculate the daily changes in terms of dollars and percentage from day to day. Not all days are represented, so in case of a gap I calculate the number of days in the gap, and I divide the growth % by the number of days. I already imported and appended the data for both metals into a single table in Excel and we’ll start the process from this table.

Read on for the solution.  I’d just as soon LAG() the data in SQL Server, but if that’s not an option, this certainly works.

Imke Feldmann gives us a reason to use M to calculate running totals:

Today I want to share a scenario where a running total calculation in the query editor saved a model that run out of memory when done with DAX:

Problem

The model couldn’t be refreshed and returned out of memory error with a calculated column in the fact table of over 20 Mio rows (from a csv-file). A running total should be calculated for each “JourneyID”, of which there were over 1 Mio in the table itself. This rose memory consumption during refresh by over 300 % – until it finally errored out:

Click through for the solution.

Eugene Meidinger teams up with Bert Wagner to teach Power BI using a food metaphor:

A Power BI Dataset is a series of Power Query queries that have been shaped in a DAX model. Each dataset can combine different files, database tables and online services all into one tabular model.  In our cookie analogy, these are all different “ingredients”.

Unlike SSRS, a dataset in Power BI does not represent a single table or query of data. A dataset should be considered more like a “flavor” of data used to accomplish a specific type of reporting: financial, operational, HR, etc. So in our analogy, the dataset is the “raw dough”.

So in Power Query, you are going to have a set of queries which each combine a data source with a usually linear set of transformations.

The pie chart cookie is the first one I would have eaten, if only to eliminate it.

Chris Webb shows us how to take data from SQL Server and send it via Power Query through Microsoft Flow to create a CSV:

The Power Query/Flow integration is still in Preview and I found a few things didn’t work reliably: for example the first few times I ran my Flow I got errors saying that it couldn’t connect to the Azure SQL Database, even though it clearly could while I was designing the query, but that error went away after a while. What’s more it only works for SQL Server data sources right now and I really hope that it is enabled for all the other data sources that Power Query can connect to, especially Excel. These are just teething troubles though, and it’s clear that this is going to be revolutionary for Power Query and Flow users alike!

Click through for an example.

Jeanne Combrinck walks us through how to enable Power BI preview features:

Every month PowerBI releases new features. Some of the features are in preview mode and unless you turn it on you don’t get to use the preview features. This post explains how to turn them on.

Firstly you need to have the latest version of PowerBI to get the latest features. You can download it here.

Click through to see the remaining steps.  There are some interesting preview features that I’d expect to make it to the general product in the next few months.

Jesse Gorter gives us a couple examples of process for releasing Power BI dashboard changes in a corporate environment:

Corporate BI is the ‘one version of the truth’ data that must be governed and tested. The report on top of that might need to be governed too. So any changes to the model or the report (or both) need to go through some testing before it can be accepted. Hence DTAP & Power BI.

Let’s explore a case where an organization has a HR department with a manager and a data analyst. Those two make the reports and decide what they want to publish to their target users: HR employees. A developer creates the datamart and the Analysis Services Tabular model cube on which they create Power BI reports. For the first version, they create a pbix file and upload it to Powerbi.com. What happens when the business wants the report or the model changed?

Read on for two potential solutions.

Jeanne Combrinck shows us an easy method of creating Power BI layouts:

PowerBI has this great functionality where you can go and download preset layouts which make your PowerBI reports stand out more. There is a nice trick to doing this yourself.

You can create layouts in PowerPoint and then save them as images. Then insert them into your PowerBI report as an image and send the image right to the back.

Click through for an example of this in action.