Power BI – Page 4 – Curated SQL

Finding the SQL Power BI DirectQuery Mode Generates

Published 2024-09-16 by Kevin Feasel

If you’re performance tuning a DirectQuery mode semantic model in Power BI, one of the first things you’ll want to do is look at the SQL that Power BI is generating. That’s easy if you have permissions to monitor your source database but if you don’t, it can be quite difficult to do so from Power BI. I explained the options for getting the SQL generated in DirectQuery mode and why it’s so complicated in a presentation here, but I’ve recently found a new way of doing this in Power BI Desktop (but not the Service) that works for some M-based connectors, for example Snowflake.

Click through for the solution.

Comments closed

Migrating Power BI Dataflows from Gen1 to Gen2

Published 2024-09-12 by Kevin Feasel

Reza Rad talks migration:

Unfortunately, there isn’t a migration tool to convert your Power BI dataflow (gen1) to Microsoft Fabric dataflow (gen2). If you have Fabric capacity licenses, it just makes sense to do that migration because Dataflow Gen2 gives you data destinations into four destinations, which we don’t have in Dataflow Gen1. However, converting Gen1 to Gen2 isn’t that complicated. The process is explained in this blog and video.

Click through for the blog post and the video.

Comments closed

Myths and Reality of Copilot for Power BI

Published 2024-09-11 by Kevin Feasel

Kurt Buhler puts together an essay:

However, recent months reveal rising skepticism, concern and possibly even disillusionment with generative AI tools, both from investors (especially from investors) and from the public. Despite the massive investment, enthusiasm, and promotion, these tools seem to be seeing limited adoption and aren’t yet showing the measurable value that fulfills their promises. And yet, paradoxically, many professionals will agree anecdotally that they use generative AI tools regularly, and that these tools seem to help them be more productive in certain tasks. Furthermore, there are concrete success stories where generative AI is bringing value, such as the models like the latest versions of Alphafold (from Google) and ESMfold (from Meta) that aid in protein folding for pharmaceutical companies more effectively find potential new drug candidates. So, who are these tools for, what problems do they solve, and how can we use them effectively? This is too big of a topic for even Bink and Bonk the Data Goblins to solve, so let’s narrow the focus, a bit.

This is a must-read, and Kurt even provides a de-goblinified PDF version for management.

Comments closed

Reading Always Encrypted Data in Power BI

Published 2024-09-11 by Kevin Feasel

Rod Edwards wants to make use of encrypted data:

This is where things start to get a little more interesting compared to Pt1, as now we have a different application in the mix for reading the data. So how can that application retrieve the key needed to successfully decrypt?

Read on to see how it all works. There are a lot of working parts here, though some of it pertains to using an on-premises gateway versus Always Encrypted as such, so you get even more bang for your buck.

Comments closed

Displaying Dates without Data in Power BI

Published 2024-08-29 by Kevin Feasel

Alon Ohayon looks for the missing month:

When you’re using Power BI, you probably create line charts that show data by month pretty often. It usually works great, but what happens if some months don’t have any data?

By default, Power BI just skips those months in the chart. That might seem okay, but it can actually be misleading—especially if you’re looking for trends over time, including the months with no activity.

Alon shows us an example of how to do this in DAX and that works. But if you can solve this at the data layer, such as when querying from a SQL Server, that’s even better. This happens to be one of the good uses of a calendar table: giving you a complete set of months (or whatever time period you want) that you can then use to left join to your data, returning either a data point with a value, or a NULL that you can coalesce with 0 to ensure that you have a result for each month.

Comments closed

Loading Scanner API JSON Data from Fabric into Power BI Desktop

Published 2024-08-28 by Kevin Feasel

Gilbert Quevauvilliers hunts for some output files:

How to get data from a Fabric Lakehouse File into Power BI Desktop – Using Scanner API JSON

In this blog post I am going to show you how I connected to my Scanner API JSON file which is stored in the files section of my Microsoft Fabric Lakehouse.

Full credit on how to complete this comes from Marc’s blog post https://data-marc.com/2023/08/25/access-onelake-files-from-power-bi-desktop/

Click through for the instructions.

Comments closed

Comparing Power BI Stacked Column Charts

Published 2024-08-27 by Kevin Feasel

Meagan Longoria compares and contrasts:

One of the new features in the August Power BI Desktop release is the updated legends that are styled to more accurately reflect the per-series formatting on the visual. This made me curious how close I could get to the clean look of a Deneb (vega-lite) stacked bar chart.

I used open source data from the Vega github repo and applied a few filters in Power BI. I used exactly the same source data in each chart, filtering down to five sites and 9 varieties. I chose a visual with a legend, data labels, and total labels to display the differences. We can argue about whether you should show a chart with this many labels another day.

Click through to see how the in-built visual compares to a custom visual of the same form.

Comments closed

Setting Row Label and Key Columns in Power BI

Published 2024-08-26 by Kevin Feasel

Chris Webb makes use of metadata:

A few weeks ago I wrote a post on how to improve the results you get from Power BI Copilot by editing the Linguistic Schema. As I mentioned, though, there are in fact lots of different ways that you as a Power BI semantic model developer can improve the results you get from Copilot and in this post I’ll show you another one: setting the Row Labels and Key Columns properties on a table.

Read on to see how these can affect results.

Comments closed

Rating a Dashboard

Published 2024-08-22 by Kevin Feasel

Martin Schoombee explains the challenge of dashboard review:

A little while ago I was asked to rate a Power BI dashboard. The person who asked, participated in a Power BI challenge (I’ll call it that because that’s the way they are being marketed) and wanted some feedback on the submission. I agreed on the condition that the feedback would be public and in the form of a blog post.

Martin has a thoughtful explanation of the difficulty of providing a review (especially without important context around what the end users intend to do) but then does yeoman’s work talking about the visuals.

Comments closed

Troubleshooting Non-Editable Power Query Parameters in Microsoft Fabric

Published 2024-08-20 by Kevin Feasel

Soheil Bakhshi digs into a problem:

Power Query is a powerful tool within the Microsoft Fabric environment, enabling users to manage data sources and transform data efficiently. However, a common issue you may face is that after publishing the Semantic Model, the Power Query parameters either do not appear or are greyed out, making them non-editable. In this post and its accompanying YouTube video, I’ll walk you through the steps to diagnose and fix these problems, ensuring that your parameters work as expected in your published semantic models.

Click through for the video and a pair of common reasons.

Comments closed

Category: Power BI