A Fine Slice Of SQL Server
Adam Saxton points us toward good resources for learning about Power BI:
Just starting with Power BI? Maybe you’ve been using Power BI for a while and have hit a wall. We wanted to call out some items that help with a lot of conversations we have over and over again. Take your skills to the next level!
Check out the video and click through for links to helpful resources.
Comments closedImke Feldmann shows how to refresh Power Query results on protected sheets in Excel:
When working with Power Query in Excel you might want to refresh Power Queries on protected sheets. But this will not work by default. Using a macro to temporarily unprotect the sheet and protect it again will do the trick. But this requires the password being displayed in the VBA code. So please have in mind that this technique only works for scenarios where you want to prevent accidental changes with the password protection.
Read on for the process.
Comments closedStephanie Bruno has updated a useful tool:
If you’re like me, building a data model in Power BI is an iterative process. Sometimes you have to try out different ways of writing measures before you hit on the one that’s right. You end up with temporary measures that don’t actually end up being used in visuals. You may also pull in more columns than you might end up needing, just in case. When you’ve finally finished your masterpiece with measures and visuals, there are probably quite a few that you don’t need. Two problems with this are that having extraneous columns and measures (1) can slow down your model and (2) can make it more difficult to maintain. You may also want to know where on your report a change to a measure will have an impact.
Click through for a demonstration of the solution.
Comments closedMatt Allington wants to trim the fat:
I have a saying in Power BI. Load every column you need, and nothing that you don’t need. The reason for this advice is that columns can make your data model bigger and less performant. You will of course need some columns in your data model for different purposes. Some are used for defining measures and some are used for slicing, dicing and summarising your data in the various visuals. But it is very common for people to load everything from the source, meaning that some of the columns are likely to be loaded but not used. Once the data model is ready and the reporting is done, it can be beneficial to remove the columns that are not being used and are not likely to be used for ad hoc reporting in the near future. The question is – how do you find the columns not being used? This is where Imke’s Power BI Cleaner tool comes in; I will show you how to use it below.
Read on for Seven Minute Abs for your Power BI data model.
Comments closedImke Feldmann solves a problem:
When you use the UI to import files from SharePoint, you’ll end up with the Sharepoint.Files function. This function can become fairly or super slow when you use it on large SharePoint sites. This is due to the fact, that it will retrieve metadata for ALL files that lie on the site. Meaning: The root site whose URL you have to enter as the function argument. So I’ve developed a better way for File import from SharePoint.
Click through for the solution and how to use it. Imke reports 2X query performance when reading Sharepoint data, so it’s worth checking out.
Comments closedMarc Lelijveld violates Betteridge’s Law of Headlines:
Recently, I run into a case where we wanted to combine Direct Query sources with imported data. Well, this functionality is actually called composite models nowadays.
Having that said, let me start with managing some expectations for this post. The title says composite models, but I have to admit, it is not about the to be released composite models feature where you can combine multiple Power BI datasets. Instead of that, it is about combining Direct Query and Import mode in one single dataset.
In this blogpost I will describe more about my use case, why I considered to use some sources on Direct Query, how this combines with Row Level Security and Query Folding.
Read on for the test.
Comments closedGerhard Brueckl shows us how to work with semi-additive and non-additive measures in Power BI:
In one of my recent projects we wanted to visualize data from the customers analytical platform based on Azure Databricks in Power BI. The connection between those two tools works pretty flawless which I also described in my previous post but the challenge was the use-case and the calculations. We wanted to display the distinct customers across various aggregations levels over a billion rows fact table. We came up with different potential solutions all having their pros and cons:
1. load all data into Power BI (import mode) and do the aggregations there
2. use Power BI with direct query and let the back-end do the heavy lifting
3. load only necessary pre-aggregated data into Power BI (import mode)
Please keep in mind that we are dealing with a distinct count measure here. Semi- and Non-additive measure like this cannot easily be aggregated from lower levels to higher levels without having all the detail data available!
Read on for Gerhard’s thoughts on each as well as the decision and process.
Comments closedGilbert Quevauvilliers walks us through the Vertipack Analyzer in DAX Studio:
If you are looking to better understand your Power BI Model, how big are your tables, which column is taking up the most space then you can use Vertipaq analyzer which is built directly into the amazing DAX studio.
If you are looking for a video on how to do this, there are some awesome videos found here.
SQLBI.COM – Introducing VertiPaq Analyzer in DAX Studio
Guy In a Cube – How do you even use VertiPaq Analyzer with Power BI???
The reason for my blog post, is that I find sometimes I want to watch a video to better understand the content, and other times I want to follow a step by step process on how complete the analysis.
Click through for a walkthrough of the process.
Comments closedPatrick LeBlanc shows us how to incorporate images into Power BI data sets:
Are you curious about how images can be used in Power BI? Patrick looks at some different ways to incorporate images into your data model and Power BI Report!
Click through for a video. All of the techniques here use an image URL which gets pulled in rather than storing the binaries in Power BI itself.
Comments closedEd Hansberry shows how to deal with renaming columns whose names regularly change:
The easiest way to rename a column in Power Query is to do it the same way you do in Excel – just double-click it and rename it. Sometimes though the column you are renaming can have different names with each refresh, so this method won’t work. We can use a List in Power Query to make this dynamic. Consider this very simple example:
You receive this file every day, and the column name starting in Row 6, Column B, constantly changes. We are going to extract that date and make it a column, as it should be, and then rename both columns regardless of what they are called.
Read on for the process.
Comments closed