I have summarised in a table the list of options that are available. There are many more options available if you choose to transform your date columns. It all depends on how you want to analyse your data set. This may be useful to analyse data volumes by the week of the year rather than daily orders. We can see that some of the deliveries run over into the next week. We can then easily see if the deliveries are in the same week of the year. In the sample data set in this example, we will transform the Order Date Column and the Delivery date Column using the Week Of Year option. If you do not want or need to specifically keep the original column of dates in your data then you can choose to transform the dates to suit your data needs.

The Date tools in Power Query are located in two areas. It will look a bit like my example below depending on what your data set contains.Ĭurrently, my date columns are formatted as Date/Time now they have been imported into Power Query. Ok, our data is now loaded into Power Query where we can carry out some magic on the data to transform it into the format we want it. I am using Excel 2016, therefore, I use the second method to get the data into Power Query. I have a table of Sales data in my current workbook and depending on the version of Excel you are using it can be one of two ways to get the data set loaded to work with.Įxcel 2010/2013 – Power Query Tab | From TableĮxcel 2016 – Data Tab | Get Data & Transform Data | From Table/Range We need to specifically get the data set into Power Query. To work through both of these examples we need to get the data set that we want to work with into Excel.

Well one of them uses and Menus available on more recent versions of Power Query and one uses the Date.ToText function. In this blog post, I want to share with you two ways to convert dates in Excel Power Query.

How to add power query to excel 2016