Streaming data using Microsoft Flow and Power BI

Sassenheim, 6 June 2019In the PowerBI service you can define a streaming dataset. With this dataset you can show real time data on a dashboard and create reports based on this real time dataset. But how can you use this if you don't have a streaming datasource? The easy answer is to create one. But then, how do you get data from a static database to stream to this PowerBI dataset? This is where Microsoft Flow comes into play! In a few easy steps you are able bring this static data to life, also known as 'data in rest to data in motion'.Before going into Flow you first need to define a streaming dataset in the Power BI service. View the first few minutes of this Guy in a Cube video on how to do this.Microsoft flow allows you to connect to several source and exchange data between them. It all start with a trigger (something happens), normally an event...
Read More

Another way to fill the gap

I just read a blog by Matt Allington on how to fill gaps in data in Power Query. I've also had this issue and found another solution using a different approach. In stead of creating a new date table and joining the original data to that I create a new column with a list of date where the value applies and expand that. You do need some experience using the advanced editor. There are three tricks I use here. You can see both in highlighted in the code below. The first one is that you can Merge a query to itself. I've added two index columns to the table to be able to find the next available date. The second one is that I create a list of dates in a new column. The third one is that I can expand my new column with a list of dates to generate the new records. Copy and paste the below code in a blank query...
Read More

Gantt-like chart in PowerBI using Conditional formatting in the matrix visual

Imagine a case where the customer wants to monitor devices during the hours of the day. All devices can either be on or off and can be in serice or in error. Besides that the devices have boundary setting which are used to determine if a device should be on or off. The customer wants insight into the status, including the boundary setting if the device performs out of the ordinary (sometimes we want to see the boundary settings and sometimes we don't). For this customer we found an, I think, clever solution using the matrix visual and the ability to use conditional formatting of the cells background and font resulting in the visual below. To create this visual we need a couple of things. First we need business rules applied to the underlying data to interpret if a device is on or of, or any of the other status. We then need a translation of the status into the way we...
Read More

Use Parameters as database reference

Sassenheim, may 2, 2019 Parameters in Power Bi are a very powerfull way of making your report more dynamic. Parameters can then be applied at various places in the tool, for example, you can use parameters to apply filters to data. Another application that I find very useful is that you can use parameters to connect to different databases. By doing this through parameters you can, for example, make data accessible in an entire DTAP street with one report. This is very usefull when assessing the quality of data in different databases or when rolling out a new report. To do this, proceed as follows: 1. Go to powerquery and select Manage Parameters > New parameter to add a new parameter. 2. Create two new parameters, one for the server address and one for the database name. Use type Text and a list of values. 3. Use these parameters with every new query that gets data from one of these databases. If you have already...
Read More

Sort by column

Sort by Column is a feature in power BI with which you can change the default sort order of a column. This would typically be with ordinal fields of type character. Some examples are for instance the result of a questionnaire (strongly agree, agree, neutral, disagree, strongly disagree) or month names (January, February, etc.). Guy in a Cube shows how this works in this video. How it works Month name is a character field and would be sorted by default alphabetically. To change this you can use Sort by Column under modeling in the ribbon to select a different column to sort by, in this case month number. A requirement for this to work is that every unique option in your original column has a unique number in the column you use to sort by. In the case of the month names you can use the month numbers (1 to 12). It is not possible to use the quarter number (1 to...
Read More

Hourrecord

Victor Campenaerts broke the world hour record during his attempt at the Bicentenary Velodrome in aguascalientes on april 16th 2019. Below is a head to head of his and the attempts of others of the last few years. You can select two riders and see the differences in their attempts, both in distance per minute as in times per lap. You van play around with the mouse-over of visuals to see specific details and the slider to increase the laptimes of the rider on the right in order to make him break the record. ...
Read More

Verzettentabel dynamisch gemaakt

Sassenheim, 1 maart 2019 De Koninklijke Nederlands Wielren Unie bepaalt elk jaar het maximale verzet waarin renners in de verschillende categorie├źn mogen rijden. Renners, vooral in de jongere leeftijden, hebben een maximaal verzet omdat een te zwaar verzet niet goed is voor hun ontwikkeling. Met een lijst van maximale verzetten per categorie en discipline en een verzettentabel is het mogelijk om te bepalen met welke verzetten gereden mag worden. Dit blijft echter een lastige opgave om twee redenen: Een statische verzettentabel gaat uit van een standaard wielomtrek. De daadwerkelijke wielomtrek kan afwijken. Op basis van het maximale verzet is het lastig om in de verzettentabel de waarden kleiner dan het maximale verzet te vinden, omdat deze per individuele waarde gezocht moeten worden. Power BI to the rescue! Ook voor een dergelijk toepassing is power BI geschikt! In het onderstaande rapport worden drie vragen gesteld. Op basis van de eerste twee vragen (categorie en discipline) wordt het maximale verzet bepaald. Met het maximale...
Read More

Power BI Embedding in externe apps

Sassenheim, 23 januari 2019 Sinds de introductie van Secure Embed begin januari zijn er 3 manieren om PowerBI rapportages in externe sites op te nemen. Deze derde variant vult het gat dat er tussen de eerdere twee optie bestond. Nu dit gat gevuld is wordt het tijd ze allen eens nader te bekijken. Wat is embedden? Het embedden van een rapport betekent dat de inhoud van het rapport zichtbaar wordt in een ander portaal dan de PowerBI service. Het rapport wordt als het ware opgenomen en getoond in een andere site of app, terwijl de hosting van het rapport nog steeds plaats vindt in de PowerBI service. Dit is praktisch omdat het hierdoor niet nodig is om binnen de eigen portal of app een powerBI service instance te hosten of de app of site uit te rusten met rapportage functionaliteit. Drie varianten Er zijn sinds januari drie manieren om PowerBI rapporten te embedden, elk met zijn eigen toepassingsgebied: Publish to Web PowerBI embedded Secure Embed...
Read More

Change bar width on a continuous x-axis

Sassenheim, 5 december 2018 Here's a small undocumented feature I found out when playing around with Power BI. You can change the width of the bars on your bar charts by changing the Inner padding of your X-axis. This option is only available when you have a catagorical axis, selecting a continuous scale disables the option of setting the inner padding from the view. However, the option is still functioning! If you have a continuous scale, just change it to categorical, change the inner padding and change the axis back to continuous. The result will be that your bar width has changed, even with the continuous scale. Download an example file here. ...
Read More
12