Nicky van Vroenhoven shows off #shared:
In addition to what the title suggests, you can not only show all the functions, but all tables, records, (custom) functions and query (results) inside Power Query.
Click through to see how.
Comments closedCategory: Power BI
Chris Webb continues along a theme:
Since last week’s blog post about dynamic M parameters generated so much interest, this week I thought I’d give you another example of something cool you can do with them when you’re using Azure Data Explorer (ADX) as a DirectQuery source in Power BI: geospatial analysis.
Let’s say you work for a chain of supermarkets and want to use Power BI see what other competing stores are close to one of your stores.
Read on for the rest of the story.
Comments closedPatrick LeBlanc has a video for us:
Have you struggled to compare data from two different time periods in Power BI? Does the data just continue on and not overlap? Patrick shows you how you can easily overlay the data for comparison.
Click through for the video.
Comments closedGilbert Quevauvilliers shows us how to remove a Power BI On-Premises Gateway from a cluster:
In the past when I had to remove a Power BI Data Gateway from a cluster, I had to use PowerShell with a couple steps which was rather complex.
I recently found out that this can now be easily achieved using the Power BI Admin Centre in the steps below.
Click through for the process.
Comments closedMatt Allington walks us through a complex scenario using the Matrix visual in Power BI:
Not every report you may want to build has such simple layout requirements as the one above. When using an Excel pivot table, there is a feature called “Field, Items and Sets” that makes the process of creating asymmetric pivot tables relatively easy, but that doesn’t exist in Power BI. To help you understand, here is an example of an asymmetric visual (Power BI matrix in this case).
Note in my example above, the first 4 years come from the year column, just like before, but now I have 2 additional columns at the end of the matrix that are not simply the addition of other columns. To create the last 2 columns above I need to write measures, but you can’t have both measures and columns from your model showing like this in a Power BI matrix. Instead you need to build a solution that leverages a separate table to generate the columns you need, and then to generate the results you need in each column.
Below I will show you how to build such an asymmetric matrix like this.
Read on for the solution. This is quite clever but also makes me wonder if the product shouldn’t make this scenario a bit easier for us
Comments closedMartin Schoombee continues a series on automating Power BI deployments:
There’s a few things you need to pay close attention to when setting the refresh schedule via the API:
– Unless you’re setting the refresh schedule for a Premium workspace, you can only refresh a dataset up to 8 times a day. We’re only going to set it to update once a day here, but keep this in mind if you’re planning to adjust the API call to refresh multiple times a day.
– The name of the time zone you provide has to match exactly with the names (middle column) in this reference: Microsoft Time Zone Index
– The refresh time has to be in the format hh:mm, and similar to the options in the Power BI portal you can only refresh on the hour or half-hour.
Read on to see how it works and the API call to make.
Comments closedStephanie Bruno shows us a method for combining CSV or Excel files:
One of the unsung heroes to me in Power BI desktop (or Power Query in Excel) is how wonderfully simple it is to combine csv or Excel files from a folder. Maybe it’s not totally unsung, but I think it’s an everyday problem that many people have to deal with. It could easily be an entry point for many new users to see just how much easier Power Query can make their lives.
As much as I appreciate how easy the tool allows us to automatically combine files without having to write any code, I never like all those extra queries that get created in my file and so I prefer to simplify it by just writing one magical little line of code. But I always got frustrated because my one line of code left me without proper column headers, so my one line of code turned into a few extra cumbersome steps. In this post, I’ll show you how to slightly modify that one line of code to get the column headers and keep your queries clean and simple.
Click through for a demo.
Comments closedDavid Eldersveld takes a look at a new sample data set in Power BI Desktop:
With the October 2020 release of Power BI Desktop, Microsoft has incorporated a sample dataset directly into the product. While there are a few commonly used training and demo datasets already, there are benefits to having a basic starter Financials dataset included.
This data can accelerate the learning experience for new users, particularly those who may only need to focus on authoring reports on existing datasets at their organization and don’t need to immediately know much about data modeling.
Click through for David’s mixed review.
Comments closedGilbert Quevauvilliers shows off an interesting way of using a Power BI feature:
Recently I had to get some data from a Power BI Dataset. At first, I started writing the DAX using the fantastic DAX Studio.
Then a thought occurred to me, what if I could get the DAX already written and change it to my requirement. This would save me a lot of time and effort. I love the quote from Patrick in Guy in a Cube “I am not lazy, I am efficient”
Click through for the scenario. Looks like it will get you at least part of the way there.
Comments closedMartin Schoombee walks us through changing data source credentials on deployment with Power BI:
The method we need to use here is the Patch method. Why is it Patch and not Post like we did with the parameters? Great question, and the “devil is in the details”…the method dictates how the underlying resource or attribute is modified, and the Patch method in this case means that there are partial (and in-place) modifications to an existing resource. Read more about the differences between Post, Put and Patch here.
This is one of those areas where Power BI can be quite a letdown if you only use the UI.
Comments closed