Press "Enter" to skip to content

Category: Power BI

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

A New Financial Sample Dataset with Power BI

Published 2020-10-23 by Kevin Feasel

David Eldersveld takes a look at a new sample data set in Power BI Desktop:

With the October 2020 release of Power BI Desktop, Microsoft has incorporated a sample dataset directly into the product. While there are a few commonly used training and demo datasets already, there are benefits to having a basic starter Financials dataset included.

This data can accelerate the learning experience for new users, particularly those who may only need to focus on authoring reports on existing datasets at their organization and don’t need to immediately know much about data modeling.

Click through for David’s mixed review.

Comments closed

Getting Power BI to Write Your DAX

Published 2020-10-23 by Kevin Feasel

Gilbert Quevauvilliers shows off an interesting way of using a Power BI feature:

Recently I had to get some data from a Power BI Dataset. At first, I started writing the DAX using the fantastic DAX Studio.

Then a thought occurred to me, what if I could get the DAX already written and change it to my requirement. This would save me a lot of time and effort. I love the quote from Patrick in Guy in a Cube “I am not lazy, I am efficient”

Click through for the scenario. Looks like it will get you at least part of the way there.

Comments closed

Changing Power BI Data Source Credentials

Published 2020-10-23 by Kevin Feasel

Martin Schoombee walks us through changing data source credentials on deployment with Power BI:

The method we need to use here is the Patch method. Why is it Patch and not Post like we did with the parameters? Great question, and the “devil is in the details”…the method dictates how the underlying resource or attribute is modified, and the Patch method in this case means that there are partial (and in-place) modifications to an existing resource. Read more about the differences between Post, Put and Patch here.

This is one of those areas where Power BI can be quite a letdown if you only use the UI.

Comments closed

Mapping New Column Names with Power Query

Published 2020-10-22 by Kevin Feasel

Soheil Bakhshi reminds me of DB/2:

So, here is my scenario. I received about 10 files, including 15 tables. Some tables are quite small, so I didn’t bother. But some of them are really wide like having between 150 to 208 columns. Nice!

Looking at the column names, they cannot be more difficult to read than they are, and I have multiple tables like that. So I have to rename those columns to something more readable, more on this side of the story later.

Fortunately, there’s a way to fix this; click through for that way.

Comments closed

Dynamic Format Strings when using Calculation Groups

Published 2020-10-21 by Kevin Feasel

Alberto Ferrari shows off how you can dynamically generate format strings when using calculation groups in Power BI:

Each product in Contoso weighs a certain weight. The weight is stored in two columns: the unit of measure and the actual weight, expressed in that unit of measure. Specifically, Contoso uses three units of measure: ounces, pounds, and grams.

Because the units of measure are different, you cannot aggregate the weight over different products. If you author a simple measure that computes the ordered weight of products by using a simple SUMX, the result is wrong:

Click through to see how you can work through this problem.

Comments closed

Power BI Custom Format Strings

Published 2020-10-20 by Kevin Feasel

Matt Allington is back from the future:

The ability to apply custom format strings directly inside the Power BI Desktop report view was first announced back in February 2020. At the time I was quite excited and tweeted about it only to find out shortly thereafter that it hadn’t been released at all! It seems it was a release candidate that was pulled at the last minute, but no one updated the announcement! Then late last week I was doing some work in Power BI desktop and noticed that this feature has now been released and is working (I don’t recall seeing a new announcement at all).

Below I show you how to use custom formatting strings in Power BI.

Click through to see how it works, as well as a couple gotchas.

Comments closed

Creating Power BI External Tools in VS Code

Published 2020-10-20 by Kevin Feasel

Phil Seamark takes us through creating external tools in Power BI:

For this article, I want to share a way for you to create your own Power BI “Helper Tool” and register it as an external tool in Power BI. This article carries on from some of my recent articles on how you can use Visual Studio Code to help automate specific tasks by taking advantage of the existing Analysis Services client libraries.

In my role, I often connect to AS models (Power BI or Azure AS) and often want to perform specific tasks quickly. The helper tool I share here allows you to connect easily to an AS model and then perform helpful tasks. I’ve deliberately kept the look and feel of the tool to be ‘old school’ like me. 

Click through for the step-by-step instructions.

Comments closed