Power Query – Remove all characters before delimiter & after delimiter
Below is the Power Query Syntax where I wanted to move the characters before the delimiter. I must give credit to the Power BI Community who got me going with how to solve this. For reference: Solved: Rename all column headers removing part before to … – Microsoft Power BI Community This is an example of what my column name…
How to refresh Excel files faster in Power BI
I was recently getting an error when refreshing an Excel file that was stored in SharePoint online. This got me thinking what the issue is and is there a way to improve it and YES there is as detailed below. When I investigated this further, I found that my 17kb Excel file was using at least 616kb of data when…
Power Query (M) – Passing Parameters dynamically to a SQL Server Query
I had a requirement where the client wanted the capability to decide how much data to load from a SQL Server Query (TSQL). This was so that they could limit the dataset returned, as at times they did not need all the data. So below I demonstrate how to achieve this. NOTE: This will be slightly advanced because I had to…
Using Power Query to get data from an API that uses a Username and Password combination
In the blog post I am going to show you the steps that I took to get data from the XE.COM API which uses a username and password to log into the API You might be thinking that I could put in the username and password when I used the Web Connector. My challenge is that I wanted to create…
How to automate your data to always store the last 2 years of data using Power Query
How cool would it be to not have to manually update your dataset to keep data for last 2 Years (last year and this year to the current date)? In this blog post I will show you how you can easily filter dates in Power Query to show dates for last year and year to date by using the GUI…
How to use SSIS with Power Query to extract data from Power BI Premium and insert into local CSV
I was helping and answering some questions on the Power BI community forums and I came across a question where someone was looking to query data from Power BI Premium using SSIS (SQL Server Integration Services). This is definitely possible and I will show you below how I can use SSIS, to query data from Power BI Premium and store…
Getting automatically notified for Power BI Support Issues
I had an interesting question from a good friend of mine on Twitter (Mim), where he asked if it was possible to get notified of when there are updates to Power BI Support issues. This got me thinking as I had already created my own Power BI Report, which sends me a daily email subscription of the Power BI Support…
Power Query – Add Column to see if it is a Number Data Type
I was recently extracting some data from a web page and I wanted to remove all the rows that did not have a Numeric or Number value. I solved it by doing the following below. The column I was using was called ironically Column5 The syntax below can be used in Power Query / Query Editor to see if the…
Power Query – Getting the Min Value repeated for all rows in my table
I was working on a side project, looking to track how I had been training in Strava (Yes, I am a M.A.M.I.L. for anyone who has been to my presentations would have seen the picture!) What I was looking to do was to get the starting time of my event repeated on every row, which would then allow me to…
Creating a Time Dimension using the Power Query Editor in Power BI Desktop
Below is the syntax to create a Time Dimension Table in Power Query I had a requirement where I needed to create a Time Dimension for a customer. Most of the time I only need the date. Upon searching I could not find a resource where they had created the time dimension only using Power Query. Reference: https://docs.microsoft.com/en-us/powerquery-m/list-times What I…