Use them in the same way with either the Transform Tab or the Add Column Tab. There are also a number of time options if you want to use them, The best way to get comfortable is to use them.

Once you choose two data columns this option is available to use. The order in which you select the columns is important but our data set is set up correctly with the Order Date in the first column and the delivery dates in the second. This allows users to easily ass a new column and gets it to calculate the difference in days between the first selected column and the second one. A particular one that is useful for us in this example is the Subtract Days option. You can see in the Add Column From Date & Time option menu that there are a few extra menu items available to use. Week | Week Of Year | Week Of Month | Start Of Week | End Of Week Quarter | Start Of Quarter | End Of Quarter Month | Start Of Month | End Of Month | Days In Month | Month Name I have added the following columns using the Add Column From Date & Time option. LetвЂ™s add some extra columns and play around with them and see how they look. There are a good number of options available, just as in the Transform Tab. This way I can retain my original data, but it is up to you. More often than not, this is the method that I use in Power Query to alter the formatting of my dates. If you prefer to keep your original column of data that contains your dates, then this is probably the option for you. The best way to get comfortable with these options is to use them and experiment with some data yourself. 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.

Excel query table example