Press "Enter" to skip to content

Category: Power BI

Querying Data Lake Files in Power BI through Synapse Analytics

Published 2020-11-09 by Kevin Feasel

Wolfgang Strasser shows us how to integrate Azure Synapse Analytics and Power BI:

Sometimes however, would not it be nice to access the data lake in Direct Query mode – to get the most up to date information for every report view? I would say: yes … but how can you achieve this? The options natively provided by ADLS Gen2 and Power BI are not sufficient to solve this requirement. But: there are options to achieve this and, in this post, I would like to show you the possibilities using Azure Synapse Analytics to build a query layer on top of a ADLS Gen2 storage account.

Click through for a step-by-step walkthrough.

Comments closed

Triggering a Refresh with Power BI’s API

Published 2020-11-05 by Kevin Feasel

Martin Schoombee continues a series on automating Power BI deployments:

At times you may want to refresh a Power BI dataset from outside the portal, either on-demand or as part of another process (think DevOps for instance). In those cases the API provides the ideal mechanism to do so. Just remember that you are still limited to 8 refreshes a day if you don’t have a Premium workspace, and using this method will not work beyond the number of allowed refreshes. You also cannot count on the API to return a useful error message in that case.

Read on to see how.

Comments closed

Testing Format String Expressions

Published 2020-11-05 by Kevin Feasel

Gilbert Quevauvilliers shows us a neat way to check that the Format String Expression for a calculation group works:

Currently the only way to create and or edit the Format String Expression is in Tabular Editor. Whilst I love Tabular Editor it does not have any DAX Intellisense.

I kept on editing the Format String Expression and I honestly must have had to edit it 4 times and it still was wrong.

Below is a quick tip on what I did to make sure my Format String Expression was valid and would indeed work!

Read on to see how.

Comments closed

Geospatial Analysis with Azure Data Explorer

Published 2020-11-04 by Kevin Feasel

Chris Webb continues along a theme:

Since last week’s blog post about dynamic M parameters generated so much interest, this week I thought I’d give you another example of something cool you can do with them when you’re using Azure Data Explorer (ADX) as a DirectQuery source in Power BI: geospatial analysis.

Let’s say you work for a chain of supermarkets and want to use Power BI see what other competing stores are close to one of your stores.

Read on for the rest of the story.

Comments closed

Asymmetrical Matrices in Power BI

Published 2020-10-28 by Kevin Feasel

Matt Allington walks us through a complex scenario using the Matrix visual in Power BI:

Not every report you may want to build has such simple layout requirements as the one above.  When using an Excel pivot table, there is a feature called “Field, Items and Sets” that makes the process of creating asymmetric pivot tables relatively easy, but that doesn’t exist in Power BI.  To help you understand, here is an example of an asymmetric visual (Power BI matrix in this case).

Note in my example above, the first 4 years come from the year column, just like before, but now I have 2 additional columns at the end of the matrix that are not simply the addition of other columns.  To create the last 2 columns above I need to write measures, but you can’t have both measures and columns from your model showing like this in a Power BI matrix.  Instead you need to build a solution that leverages a separate table to generate the columns you need, and then to generate the results you need in each column.

Below I will show you how to build such an asymmetric matrix like this.

Read on for the solution. This is quite clever but also makes me wonder if the product shouldn’t make this scenario a bit easier for us

Comments closed

Updating a Power BI Refresh Schedule

Published 2020-10-28 by Kevin Feasel

Martin Schoombee continues a series on automating Power BI deployments:

There’s a few things you need to pay close attention to when setting the refresh schedule via the API:

– Unless you’re setting the refresh schedule for a Premium workspace, you can only refresh a dataset up to 8 times a day. We’re only going to set it to update once a day here, but keep this in mind if you’re planning to adjust the API call to refresh multiple times a day.

– The name of the time zone you provide has to match exactly with the names (middle column) in this reference: Microsoft Time Zone Index

– The refresh time has to be in the format hh:mm, and similar to the options in the Power BI portal you can only refresh on the hour or half-hour.

Read on to see how it works and the API call to make.

Comments closed

Combining CSV Files in Power BI

Published 2020-10-28 by Kevin Feasel

Stephanie Bruno shows us a method for combining CSV or Excel files:

One of the unsung heroes to me in Power BI desktop (or Power Query in Excel) is how wonderfully simple it is to combine csv or Excel files from a folder. Maybe it’s not totally unsung, but I think it’s an everyday problem that many people have to deal with. It could easily be an entry point for many new users to see just how much easier Power Query can make their lives.

As much as I appreciate how easy the tool allows us to automatically combine files without having to write any code, I never like all those extra queries that get created in my file and so I prefer to simplify it by just writing one magical little line of code. But I always got frustrated because my one line of code left me without proper column headers, so my one line of code turned into a few extra cumbersome steps. In this post, I’ll show you how to slightly modify that one line of code to get the column headers and keep your queries clean and simple.

Click through for a demo.

Comments closed