Power BI Desktop - Unable To Connect

 Just a quick post on an issue I saw a few people run into lately.

Context

I opened a report with a Live connection to a dataset and I was presented with the error below:

We encountered an error while trying to connect.

Details: "Looks like we're unable to access the dataset. Please contact the owner of the dataset."

Clicking Edit gets you the following dialogue, which might already give you a clue what's happening,

Solution

The answer here is actually really simple, if you know it! 😆 

You have to login to Power BI Desktop with the right account. I was currently logged in with my Powerdobs account, while this was a customer report.

So clicking Edit gave me the datasets in the Powerdobs tenant to connect to, which quickly led me to the conclusion I was logged in with the wrong account.

So to solve this, in the top right corner, click on your name/picture and click Sign in with a different account.

Open the report again and it should connect immediately!

Everyone happy :-)

My Favorite Power BI Desktop February 2022 New Features

Last week it was that time of the month again! No not that time.
I mean time for the Power BI February 2022 Feature Summary blog post by Jeroen ter Heerdt of course.

Dynamic M Query Parameters

The new thing is that dynamic M Query Parameters now supports SQL Server (and more data sources). There are several use cases that come to mind and a few people have already blogged about this:

• Teo Lachev (@tlachev) talks about calling a Stored Procedure with a parameter, and the requirements you need to meet and consider to pull this off
• Chris Webb (@cwebb_bi) has a similar approach, but uses a T-SQL Table-Valued Function with 2 parameters

The high level steps to dynamic M query parameters are:

• The source (SP/TVF) has to be in DirectQuery mode
• Create a parameter(s) in PQ
• In the model view, bind that parameter(s) to the column in your datamodel
• And then use the parameter(s) in your source in PQ

If you want to read up on all the features, supported datasources, limitations and risks, go read the documentation on how to use dynamic M query parameters.

New mobile formatting options

If you're into mobile formatting for Power BI, you're going to love this update. There's a lot more you can configure, now also independently from the web version.

To enable the new mobile formatting pane, the blog post says you'll have to enable the preview feature under Options, but after my installation of the February update it was already enabled:

And more...

Of course there's a lot more in the February update, to mention a few:

If you haven't started using Goals, there are more updates to it, including:

• Teams notifications integration,
• Multiple owners for a single goal and 
• The ability to create scorecards in My Workspace

Deployment Pipelines enhancements: there's a (fairly) new Azure DevOps extension and multiple pipelines working together, for example for disconnected reports and datasets

Download .pbix improvements: starting with this release you can download a .pbix file in more scenario's. Specifically, if a report is connected to a dataset that is configured for large models, incremental refresh or has been modified using the XMLA endpoint, you are now able to download that (connected) report, so not the dataset itself 😉

Read up on all the gotcha's and limitations of this export to PBIX.

Now go download that new update and check it out ourself! 😀

Power Query Trick: Remove (Only) Leading/Trailing Zeroes From Text

Just before the holidays start, I'd like to share this little trick I came across in Power Query.

I recently had the need to remove zeroes (but it can be any character) from the beginning of a column.
Turns out there's a perfect function for that :-)

The documentation isn't totally clear about this, but for the 2nd parameter (trim) you can add a character you want to have trimmed. The description only mentions whitespace, but you can enter any character in that parameter.

On a side note, I got this nice documentation page inside Power Query by using the #shared function, which I blogged about here.

Text.Trim[Start/End]

And in case you might need it, there's also a Text.Trim and Text.TrimEnd function:

Now onto an example, I created a text column with some values via Enter Data:

I have some combinations of text, numbers, leading and/or trailing zeroes and spaces.
For my 1st example I used Text.TrimStart to remove (only) leading zeroes.
I added a custom column and used the UI to add it like this:

This gives me this nice result:

Only zeroes at the start are trimmed, no spaces or zeroes at other places. Great, exactly what I needed!

I also had a look at the other functions and their workings I mentioned:

In case you want to test it yourself, I put the M code I used in my GitHub.

Closing

I hope you found this one useful, let me know if you already use this or are going to in the future!

This (hopefully) might not be my last post of the year 😀, but just to be sure:

Merry Christmas 🎄🎅, a great new year 🎆 and happy holidays to you all!

My Favorite Power BI Desktop November Updates

It's already 2 weeks ago that it came out, but I'd like to highlight a few of my top additions to Power BI Desktop in November, being:

• The New Format Pane
• Page and Bookmark Navigators
• The Scorecard Visual
• The new Google Sheets Connector

New Format Pane

The first one being the introduction of the New Format Pane.
Because it is still in preview, you have to explicitly enable this feature in the options: File > Options and settings > Options > Preview Features > New format pane.
I especially like the merged font settings, but there's a ton of new features there, so be sure to check out the blog post (Introduction of the New Format Pane (Preview)) for all the updates. Also be aware there's a few known issues mentioned at the bottom of that blog post.

Page and Bookmark Navigators

O. My. God. Page and Bookmark navigators is going to save everyone so, much, time!

For example, creating page navigation is now just a click (ok, actually 3 clicks) away. This not only creates a bookmark and button for every page, it also keeps them in sync, it matches the order of your pages, deleting a page will update the bookmarks.
Next to that, there's also a ton of formatting and padding options. Just awesome!

The only thing I'd love to see added, is the fact that the page navigator would replicate to all your pages and also keeping it in sync after that. Alright, I guess I'll head over to ideas.powerbi.com 😀

Scorecard Visual

This month's update also brings the new Scorecard visual in Power BI Desktop.
This will let you create a Scorecard and goals right inside Desktop.

Picture taken from the Power BI blog post

Just be aware that as of now, the visual might not behave as expected in Power BI Desktop yet. Creating a scorecard from Desktop isn't working yet, but the Power BI team is working on a fix (to the service) and it should be deployed very soon.

This is of course tied to all the updates in Power BI Goals this month, which was also covered more extensively in this blog post last month: More easily integrate goals into all your business processes.

Updates include (a.o.):

• Goal level permissions
• Power Automate integration
• Moving goals in your scorecard (drag & drop)
• Adjusting columns in the scorecard view

New Connector: Google Sheets

Starting this month you can now easily connect to a Google Sheet with this new connector. You only need the Google sheet URL, authenticate through OAuth and you're done! And the Power BI team listened to you, because it was the most upvoted connector-related idea on the ideas site.

Wrap Up

And if you really, like really, want to keep those pie charts in your reports, you might wanna check out the update to the Pie and donut chart rotation. 😆 If you're not sure why I'm saying this, go read this paper by Stepen Few: Save the pies for dessert. 

That's all for now. Go download the November update of Power BI Desktop and play with all the good stuff! Let me know what you think are the best added features.