Power BI refresh error “could not load file or assembly…provided impersonation level is invalid”

Short version

Run Power BI Desktop as administrator before opening the report.

Explanation/Scenario

Recently, when opening a report from Power BI Report Server that used a SQL database as a data source I received the following error.

An error happened while reading data from the provider: ‘Could not load file or assembly ‘System.EnterpriseServices, Version=4.0.0.0, Culture=neutral,PublicKeyToken=b03f5f7f11d50a3a’ or one of its dependencies. Either a required impersonation level was not provided, or the provided impersonation level is invalid. (Exception from HRESULT: 0X80070542)’

So I clicked “Edit Queries” to see what was going on and received almost the same error but referencing a specific table and getting me thinking about SQL specifically.

Error seen after clicking “Edit Queries”

So I checked that table in the query editor and received yet again the same error.

Error seen after clicking the specific table referenced in the previous error message

Simple solution. It’s likely that your credentials and queries are just fine.

The solution for my particular issue here, and the meaning behind the “impersonation level” part of the error, is just that you need to run Power BI Desktop as administrator before accessing the report. After opening the report in Power BI Desktop being ran as an administrator, everything worked as expected.

“Unable to connect” error when accessing SQL database in Power BI Desktop

When attempting to access a SQL server/database in Power BI, you might get the following error message:

Unable to connect

We encountered an error while trying to connect.

Details: “An error happened while reading data from the provider: ‘Could not load file or assembly ‘System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=…..

Error message received when trying to get a SQL database as a data source in Power BI

Solution

You just need to run Power BI as administrator before attempting to access the SQL database as a data source.

  1. Save your work and close Power BI Desktop
  2. Right click Power BI Desktop and select “Run as administrator”

3. Attempt to access your database again – you shouldn’t have an issue.

Solution: “An error occurred while attempting to save the report to Power BI Report Server”

Symptoms

Did you recently download a newer version of Power BI Desktop (optimized for Report Server)? The latest is August 2018 as of the writing of this post. If you’ve downloaded August and are trying to save to an outdated version of Report Server via the client application, it’ll give you this error message.

“An error occurred while attempting to save the report to Power BI Report Server.”

2018-11-27_09-55-22

And you can try going to Power BI Report Server via your browser and uploading there directly, but you’ll get this error message which is more helpful:

“We can’t save this Power BI Desktop report because either it is newer version or has component parts that are not supported.”

chrome_2018-11-27_12-34-14

Cause

This error occurs when users are trying to save a report from a Power BI Desktop version that’s newer than the version of Power BI Report Server installed on your server.

Solution

Upgrade your Power BI Report Server version to the latest available to make sure it’s compatible with users using the latest version of Power BI Desktop available. It will be backwards-compatible in case you have users still on October 2017 or March 2018.

  1. Log in to the server that hosts your Power BI Report Server
  2. Download the latest version of Power BI Report Server
    1. Go to https://powerbi.microsoft.com/en-us/report-server/
    2. Click “Advanced download options”
      chrome_2018-11-27_12-46-35.png
    3. Select your language and click “Download”
      chrome_2018-11-27_12-46-59.png
    4. Check the box next to “PowerBIReportServer.exe” and “Next”
      chrome_2018-11-27_12-47-26.png
  3. Run the downloaded .exe file and click “Upgrade Power BI Report Server”
    mstsc_2018-11-27_10-10-03.png
  4. Follow the prompts, and reboot the server when finished. This will cause a short amount of downtime.

You should now be able to access and save reports from/to the server as usual from all versions of Power BI Desktop.

 

 

 

How to count per-row occurrence of multiple words or phrases across multiple columns in Power BI

Sometimes you want to know how many times words and phrases appear across multiple columns. For example, if we want to know how many times “Cat” OR “Mouse” appears in each row, we would get:

Column 1 Column 2 Column 3 Word Count
Cat Yarn Mouse Cat Dog 3
Dog Cat Horse Hay Dog 1
Cat Cat Cat Mouse 4

Continue reading “How to count per-row occurrence of multiple words or phrases across multiple columns in Power BI”

What is the Related() function in Power BI?

RELATED() pulls values from different tables based on relationships established in the relationships tab. This is useful and makes it so we don’t necessarily need to append or merge tables to get the same result.

For example, we can create a new calculated column in our fact table Sales that pulls in related information from two other tables without needing to append or merge tables:

PBIDesktop_2018-09-25_10-32-08

Continue reading “What is the Related() function in Power BI?”

Leading zeroes in Power BI: Restoring them from auto-detection of data types

Power BI tries to be helpful and auto-detects data formats such as dates, decimal numbers and integers. However if you’re dealing with zip codes, employee IDs, product IDs, etc. that have leading zeroes, you may not want them auto-converted to integers/number format which removes the leading zeroes. Here’s how to get them back.

From Power Query Editor (recommended)

  1. Click the “Data type” icon next to the column name
  2. Select “Text”
    2018-09-06_10-44-29.gif

From Data Tab

  1. Select the column header
  2. From the Modeling tab, change “Data type” to “Text”
    2018-09-06_10-42-06.gif

 

How to add rows for dates between start and end dates in Power BI date range data

screenshot1
Screenshot from appsource.microsoft.com’s app detail page.

In Power BI there are several custom visuals available, such as Elastacloud’s Calendar Visual, that show the density of events over time. However, if your data includes date ranges (start date with a different end date, such as task/project lists), visuals like these will only understand one of the two dates (whichever you’ve chosen for the data value) and none of the dates in between will be accounted for, making your data visualization incorrect.

You can, however, “expand” these date ranges or durations to create rows for all the dates including start, end and those in between. This way each date that’s part of the range is then graphed appropriately.

Continue reading “How to add rows for dates between start and end dates in Power BI date range data”

How to change Power BI report page size

Did you know you can adjust the page size of your reports in Power BI? Each tab/page of your report can be a unique size specified by you down to the pixel. This comes in handy for creating “widget-like” visuals for embedding or for creating reports for print and optimal display on various screen sizes.

Your options are:

  • 16:9 (default)
  • 4:3
  • Cortana
  • Letter
  • Custom (in pixels)

Continue reading “How to change Power BI report page size”

Power BI: Calculate next year’s amount in previous year’s row

next year

The following is a DAX formula you can use to create a calculated column that shows “next year’s” value in “this year’s” row. You can easily adapt this to show “yesterday’s” amount or “tomorrow’s” total as well. It can be modified for days, weeks, months, etc. as long as the time measure is able to be sorted sequentially.

Continue reading “Power BI: Calculate next year’s amount in previous year’s row”

Custom themes in Power BI Report Server (PBIRS)

newtheme

Power BI Report Server (as of the time of this post) doesn’t allow preview features, therefore doesn’t allow custom themes (easily). But with a little work, anyone can easily “install” a custom theme for their report in PBIRS in just a few steps.

Continue reading “Custom themes in Power BI Report Server (PBIRS)”