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 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…
Review of new features & Announcements for #PowerBI – From the Microsoft Business Applications Summit
I was once again very fortunate to make it across to the Microsoft Business Applications Summit in Atlanta. I had 3 sessions which I presented at, and they went well. It was great to meet new people (some of whom I had only read their blog posts or viewed their videos) and reconnected with existing people. As I did last…
Power Query – How to extract data before and after delimiters
I had a requirement where I wanted to extract the data before and after the data as shown below. In order to do this, I created the following Custom Column with the syntax below. I did try and use the “Columns from Examples” but it did not work, because in my actual dataset some columns did not have the “(Data)”…
Power Query – Replacing Column Names with spaces in Camel Case Notation
I was working with a table with quite a few columns where the column names were using Camel Case Notation (ThisIsMyColumnName), and I did not want to rename them manually. I used the following Power Query Code below to let it do the column renaming and putting in the space for me. Thanks to @Excel_City for assisting me in finding…
Where can I find all the data sources for my #PowerBI report?
I have found that at times people get other Power BI reports and are not sure what data sources there are in the file. As well as which data sources are connecting to what? In this blog post below, I will show you where you can find all the data source details. In my sample PBIX I have connected to…