Category: Power BI

Posted on

Making a Code Block from a Power BI Query Readable Using Notepad++

Power BI logo

Oftentimes, when looking at the Advanced Editor window on a Power BI query, you will see that the SQL query in one of the steps has been compressed into fewer lines – perhaps even only one line; minified with Power Query codes like “#(lf)” (line feed) and “#(tab)”.

To make this more readable, you can copy the code block from Power BI into a Notepad++ window, and replace these Power Query codes with escape sequences that will format the code into multiple lines that are easier to read.

Once the code is in Notepad++, open the Replace window with Ctrl+R, and select the Extended radio button in the Search Mode box in the lower left-hand corner.

Replace box in Notepad++

Put the offending Power Query codes into the “Find what” field, and the corresponding escape sequence into the “Replace with” field. Select Replace All.

Repeat this with each type of code that needs to be replaced. Afterwards, you can read and modify the code block, if you wish.

To convert the whole code block back into its original format, reverse the steps, putting the escape sequence in the “Find what” field and the original Power Query code in the “Replace with” field and select Replace All again.

Posted on

Power BI Gateway Timeout Issue

Power BI logo

Recently, I was informed that one of our Power BI Datasets could not be refreshed due to an error, and that the schedule had been turned off as a result. The error that was in the refresh history was similar to the one shown below.

Power BI refresh error
Source of image: https://community.powerbi.com/t5/Service/Gateway-timeout-error/m-p/1146938

It appeared that something was causing a timeout on the Power BI gateway.

I opened the dataset in Power BI Desktop, and refreshed the queries manually to see if a similar error was thrown. Though the queries took about an hour to complete, there was no error. I found the timeout setting for the data source and saw that it was blank. I thought, being relatively new to Power BI still, that this timeout needed to be set, so I set it for 60 minutes, just to test. Still no error when refreshing manually, but when published to the Power BI service, the same error remained.

There were three queries in the dataset, and as it turns out, three separate timeout values. The data source for each query had to changed individually. When I deleted the timeout settings from all three queries and republished the dataset, it was able to be refreshed on schedule without issue.

This should be the first step in troubleshooting future timeout issues, as this is can be a quick fix for this type of problem.

Posted on

Refreshing Published Power BI Reports

Power BI logo

Since June, I have begun working in a new position that involves less in the way of application development, and more data analytics and database development. The primary tool I’ll be using is Microsoft Power BI, along with SQL Server, Amazon Redshift, and CRM Analytics (formerly called Tableau) for use with Salesforce.

Power BI is an amazing tool, but like any new or unfamiliar tool, there is a learning curve to be overcome. Though there is plenty of free training to take, there’s nothing that will teach you as much as getting your hands on the tool – and then researching the problems you encounter along the way.

Recently, I had to update a number of reports with a fairly simple update – add a new column to the dataset, and then to the reports. I was able to do this and could view the updated reports with no problem.

One of my customers looked at the report and said that the new column was not there. I assumed that the report had simply been cached in the browser, and needed to be refreshed. Not so. After digging around a bit, I found that I was looking directly at the report I’d updated, and the user was looking at the report through a published app. As it turns out, updates to the report and dataset do not show up in the app until the app is updated.

Once the app has been updated, any changes made will appear, though there may be a delay if the changes were major (such as adding a large quantity of data to a dataset).