Updating your Query syntax in Power Query to show the entire query in M
Welcome to 2023, I am looking forward to an amazing year! I have seen this come up a few times in Twitter where there is an option to put in your own TSQL Query or DAX Query and the input box is very small and often you cannot see the entire query as shown below. I will show you how…
Did you know you can get the advanced filtering pane to show more than two items in Power BI?
Recently I found out a way to update the advanced filtering pane to show more than the default of two items. This is what it looks like by default. What I figured out was to then have an extra 4 options to filter on. The reason that this came about is because I was using at integer field which does…
How to resolve error when deploying changes to Power BI Premium Per User for Incremental partitions
I have been doing a lot of work recently using Power BI Premium Per User and recently I got the following error below when trying to update my fact table in Power Query. “An unexpected error occurred (file ‘tmsavepoint.cpp’, line 1503, function ‘TMSavePoint::GetProxyImpl’)” This is what the actual error looks like. This is rather a confusing error and it can…
How to connect to a Power BI Premium Per User using SQL Profiler
I was recently looking on how to use SQL Profiler to connect to my Premium Per User (PPU) dataset so that I could see where the processing of my incremental partitions was. When I first tried to connect using the default options, I got the error “Either the race with the ID of ‘AAA’ does not exist in the server…
Power Query – Continuous Week on Week Comparison
Below is the Power Query syntax to allow for Week-on-Week comparison over multiple years. I have seen in the past Week-on-Week comparisons but one of the challenges is what happens when it overlaps years. Especially at the start of a year like it is now Feb 2021, the users want to compare week-on-week for the past 3 months. My challenge…
How to create DirectQuery (Composite) using an existing Power BI Dataset and a local Excel file
If you are looking how to use DirectQuery (Composite) models in Power BI I show you how to do this with an existing Power BI dataset from the Power BI Service and then importing an Excel file from SharePoint. In my working example I already have got a dataset which contains the Power BI Audit Log data. I want to…
Showing Dates in reverse order for Tables/Matrix with dates, so that the latest is always shown first in a Power BI Matrix
While doing some work I had a matrix that I had created. What I found was that when I had more Yr-Month values that could fit in the matrix due to having the scroll bar it would show me the older Yr-Months and not the latest ones as shown below. I always like to think what the experience would be…
How to configure Incremental Refresh with Power Query in Power BI
Following on from my successful blog post How you can incrementally refresh any Power BI data source (This example is a CSV File), I found a way where I can just use dates created in Power Query to get data refreshing incrementally. Full credit goes to Rafael Mendonç who actually figured this out. All that I have done is to…
How you can incrementally refresh any Power BI data source (This example is a CSV File)
How you can incrementally refresh any Power BI data source (This example is a CSV File) I was reading the awesome blog post by Dustin Ryan (POWER BI INCREMENTAL REFRESH WITH WEB API DATA SOURCE) and what I saw him do gave me an idea, is it possible to configure a CSV file to be incrementally refreshed using a similar…
How I saved 40%+ on my Power BI Dataset size
I have been working with a customer who has a fairly large dataset. Part of my role was to see if there was any way to see how I could reduce the amount of memory being consumed, whilst still allowing for the same metrics to be represented. Challenge accepted! I had already applied all the best practices in terms of…