Press "Enter" to skip to content

Category: Power BI

Power BI Connector for Databricks

Published 2021-03-01 by Kevin Feasel

Stefania Leone, et al, announce general availability of the Power BI connector for Databricks:

We are excited to announce General Availability (GA) of the Microsoft Power BI connector for Databricks for Power BI Service and Power BI Desktop 2.85.681.0. Following the public preview, we have already seen strong customer adoption, so we are pleased to extend these capabilities to our entire customer base. The native Power BI connector for Databricks in combination with the recently launched SQL Analytics service provides Databricks customers with a first-class experience for performing BI workloads directly on their Delta Lake. SQL Analytics allows customers to operate a multi-cloud lakehouse architecture that provides data warehousing performance at data lake economics for up to 4x better price/performance than traditional cloud data warehouses.

This is easier to work with than the Apache Spark connector and it looks like it should be faster than that connector as well.

Comments closed

Week-Over-Week Comparisons with Power Query

Published 2021-03-01 by Kevin Feasel

Gilbert Quevauvilliers knows that time is a flat circle:

I have seen in the past Week-on-Week comparisons but one of the challenges is what happens when it overlaps years. Especially at the start of a year like it is now Feb 2021, the users want to compare week-on-week for the past 3 months.

My challenge was to find a way to have a continuous week number over multiple years.

Click through to learn how.

Comments closed

Enhancing Tables with Power BI

Published 2021-03-01 by Kevin Feasel

David Eldersveld gives us ways to go beyond simplistic tables in Power BI:

Conditional formatting for a table or matrix is an easy way to keep the grid and display raw numbers while providing visual cues that our brains process faster than reading numbers. Background color, font color, icons, and data bars help jazz up bland tables. It’s an easy compromise that brings some data visualization to table detail.

Click through for more examples of this.

Comments closed

Common Admin Scripts for Power BI

Published 2021-03-01 by Kevin Feasel

Brent Powell has a new series for us:

Between the Power BI PowerShell modules and the Power BI REST APIs administrators have a rich set of tools to efficiently administer Power BI environments. Custom administrative and monitoring solutions based on these technologies have been featured on this blog before but today we will start the first of a two-part series highlighting simple script examples that Power BI administrators can use to address common scenarios.

The six PowerShell script files (.ps1) for today’s examples are available in my GitHub repo.

Check out those scripts, as well as Brent’s walkthrough of each.

Comments closed

Updating Word or Powerpoint with Power BI Data

Published 2021-02-24 by Kevin Feasel

Stephanie Bruno solves an interesting problem:

In my case, colleagues are required to develop reports every quarter with the most updated data we have and with a lot of commentary included. For example, they create the same 60-page document every quarter with the same tables and charts, and then modify the narrative depending on the data. For this reason, paginated reports may not be the right solution because the narrative varies so much. They also have a very particular format for the charts and tables that is hard to reproduce in Power BI. Finally, the data may be changing up to the day before the report is due. Their process in the past was to export the data they needed from Power BI, open it in Excel, copy and paste to another file where they had all of their charts built, export/copy/paste from more visuals, tweak the charts, then copy and paste the charts into their Word document. Then work late into the night doing this a few more times as the data is updated.

To help free them from this tedium, we worked out a new process to get their Word and PowerPoint files automatically updated in the format they required, using our good friend, “Analyze in Excel.”

Click through to learn how.

Comments closed

Using Dataflows to Speed Up Power BI Refreshes

Published 2021-02-23 by Kevin Feasel

Reza Rad shows an interesting use case of Power Query Dataflows:

No matter what caused the data source to be slow (the old technology, performance issues, slow connector, limitations, etc), it will cause the data refresh of the Power BI dataset to become slow. Even if you have an incremental refresh setup, it might not still help much, because sometimes the query folding doesn’t happen. Slow refresh time will not only be bad for the service, but it will be also bad for the developer who has to wait a long time for the data to be available after each refresh.

Read on to see how you can use Dataflows to speed up refresh times (though not speeding up the slow data source itself). Reza also has a video on the topic.

Comments closed

Refreshing a Single Table in Power BI

Published 2021-02-19 by Kevin Feasel

Marc Lelijveld doesn’t want to wait for everything to reload:

If you want to refresh a Power BI dataset, we all know where to find the refresh button in Power BI Desktop as well as in the Power BI Service. By clicking it, you will trigger the entire dataset to refresh. But sometimes it is more convenient to trigger a single table to refresh. If you want to do this, you can do a simple right-click on a table in Power BI Desktop, but how does this work in the Power BI Service? In this blogpost I will describe how you can trigger a single table refresh in the Power BI Service over XMLA endpoints. Please know, this does require Power BI Premium (either Premium per User or Premium Capacity is fine).

Click through to see how.

Comments closed

DAX and Case Sensitivity

Published 2021-02-19 by Kevin Feasel

Marco Russo and Alberto Ferrari talk about case sensitivity:

Every new language defines its own rules of case-sensitivity. R and Python are case-sensitive, DAX is not. It is not that one is right and the others are not; it is really a matter of personal taste of the author of the language. We would say that there is an equal number of pros and cons in both choices. Therefore, there is no definitive choice. That said, a choice needs to be made on two aspects: the language itself and the way it considers strings. Pascal, for example, is case-insensitive as a language, but string comparison is case-sensitive. The M language, in Power Query, is case-sensitive despite living in the same environment as DAX. DAX is case-insensitive as a formula language. 

Maybe it’s because I like living in the SQL world so much, but I highly prefer case-insensitivity as the default and case-sensitivity only when necessary.

Comments closed

Renaming All Column Names on All Tables in One Power Query Statement

Published 2021-02-17 by Kevin Feasel

Soheil Bakhshi has achieved mass production:

I previously wrote a blog post explaining how to rename all columns in a table in one go with Power Query. One of my visitors raised a question in the comments about the possibility to rename all columns from all tables in one go. Interestingly enough, one of my customers had a similar requirement. So I thought it is good to write a Quick Tip explaining how to meet the requirement.

Click through to see how to build an expression which iterates over all columns in all tables.

Comments closed