Power Query – Changing DateTime columns data types to Date without specifying the column names
This blog post I will demonstrate how to change a DateTime column data type to Date dynamically. You might be wondering what I mean by dynamic, this means that I will not specify any actual column names. The code will find the column names based on their data type. For me this has been a challenge to get it working,…
Converting Multiple Items into One Row in Power Query
I was working with some external data where I had a list of Australia postal codes, the challenge is that there are multiple suburbs that are part of one postal code. Below I am going to show you how I took the multiple suburb names and put them into a single row, which would then allow me to have a…
Multiple conditions for a conditional column in Power Query
I am often working on datasets where there is more than one condition for a conditional column. And whilst the GUI based Conditional column is really good, it currently does not have the capability for multiple conditions. In this blog post below, I will demonstrate how to achieve this. In my example below, I have a table that has got…
Power Query – A function to remove spaces within Text values
UPDATE: 19 Nov 2018 With the recent announcement of dataflows in the Power BI Service, I see more people will be looking to better understand and leverage dataflows by using the M language which is available in Power BI Desktop, Power Apps and Microsoft Flow. I had a great response to this blog post, and both Ted and Daniil had…
Review of new Features coming to Power BI by Oct 2018 – From Business Applications Summit
I was fortunate to attend my first ever Business Applications Summit in Seattle. I had the pleasure of sharing an Airbnb with Matt Allington, Phil Seamark and Miguel Escobar, it was great to spend time with these Power BI Legends. I also did meet a lot of people and got to chat with people in the Microsoft Power BI team,…
Power Query Pattern – Adding Spaces in Text within your data with Camel Case
In this week’s blog post, I created this Power Query Pattern, which I created to add in spaces for CamelCase text within a column. To get this to work for you, all that you do is need to make one change to the code. Below is what the data looked like Then I created the following Power Query Pattern below….
Power Query – Renaming Multiple Columns
I was working on a dataset where I wanted to change multiple column names using one step and not having to change them manually. Since there were over 30 columns this would be really time consuming. Below I detail how to complete this in the Power Query Editor, which will replace all the column names for me in one step….
Power BI – Turning off (Disabling) Native Database Queries
In this quick blog post I am going to show how to turn off or disable having the dialog box which pops up when using Native Database Queries. This can sometimes be an extra step on something that you have to action when refreshing your data either in the Query Editor or in Power BI Desktop. NOTE: I was often…
Power Query – How to get same row number for x number of rows
I was answering a question on the Power BI Community Forum where a user was looking to get some data from an Excel spreadsheet into Power BI. And in order for me to complete it, I wanted to get the same row number for x number of rows. Example I had some sample data where the same data was repeated…
Using Power BI to ensure Daylight Savings Dates and Times change dynamically
I was helping on the Power BI Community and it struck me that there currently was not a way to easily manage Dates and times for Daylight savings. In this blog post below, I will show how I achieved this by using parameters and little bit of M magic (Directly Editing the M Code). As part of completing this solution I…