Power BI Archives

Hello Power Excellers. In this blog post, I want to share with you two ways to convert dates in Excel Power Query. Why are there multiple ways?. Well one of them uses and Menus available on more recent versions of Power Query and one uses the Date.ToText function. (A Power Query M Function). We will cover both methods today. Getting The Excel Date Set Ready To work through both of these examples we need to get the data set that we want to work with into Excel. We need to specifically get the data set 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. Excel 2010/2013 - Power Query Tab | From Table Excel 2016 - Data Tab | Get Data & Transform Data | From Table/Range I am using Excel 2016, therefore, I use the second method to get the data 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. It will look a bit like my example below depending on what your data set contains. Currently, my date columns are formatted as Date/Time now they have been imported into Power Query. The Date tools in Power Query are located in two areas. the Transform Tab and the Add Column Tab Using the Transform Tab 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. 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. We can then easily see if the deliveries are in the same week of the year. We can see that some of the deliveries run over into the next week. This may be useful to analyse data volumes by the week of the year rather than daily orders. It all depends on how you want to analyse your data set. There are many more options available if you choose to transform your date columns. Week of the year is just one example. I have summarised in a table the list of options that are available. The best way to get comfortable with these options is to use them and experiment with some data yourself. Using the Add Column Tab If you prefer to keep your original column of data that contains your dates, then this is probably the option for you. More often than not, this is the method that I use in Power Query to alter the formatting of my dates. This way I can retain my original data, but it is up to you. There are a good number of options available, just as in the Transform Tab. Let's add some extra columns and play around with them and see how they look. I have added the following columns using the Add Column From Date & Time option. Year Menu Year | Start Of Year | End Of Year Month Menu Month | Start Of Month | End Of Month | Days In Month | Month Name Quarter Menu Quarter | Start Of Quarter | End Of Quarter Week Menu Week | Week Of Year | Week Of Month | Start Of Week | End Of Week Extra Menu Options & Tips You can see in the Add Column From Date & Time option menu that there are a few extra menu items available to use. A particular one that is useful for us in this example is the Subtract Days option. 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. 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. Once you choose two data columns this option is available to use. (not greyed out). Here is the result. Very handy. There are also a number of time options if you want to use them, The best way to get comfortable is to use them. Experiment. Use them in the same way with either the Transform Tab or the Add Column Tab. What About Older Versions of Power Query? At the start of this article, I mentioned that there were a couple of methods that are available. Well, the second method only applies to older versions of Power Query. It also only applies to a couple of options that are not available. Let's walk through them in case you are using earlier versions. Using The Date.ToText Function With older versions of Power Query, you may not see the same options available in the first date conversion method above. That's ok though, we just need to use an M function in Power Query that converts the date to text. It is straightforward and will get you the desired effect if you want to add Month Name or Day Name. Adding Month Name using Date.ToText Select the date column Add Column Tab Custom Column Name your new column - I have called this example Order Month Enter the following formula. =Date.ToText([Order Date], "MMM") Note that Power Query formulas are actually case sensitive. So, I requested upper case "MMM", which returns the Month, if I used lower case "mmm", this returns minutes. Adding Day Name using Date.ToText Select the date column Add Column Tab Custom Column Name your new column - I have called this example Order Day Enter the following formula. =Date.ToText([Order Date], "ddd") Finally, you just need to change the data type of these new columns to Text. You can either right-click on the column and select the Text data type or alternatively, you can select the Transform Tab | Data Type | Text. Depending on your version of Power Query, all of the most recent menu options to change or convert your data types may be available. you can easily use some Power Query simple formulas to plug the gaps in the few that are missing. Download The Sample Workbook If you want to look at the dataset and the example that I have used in this blog post, feel free to download it using the button below. Practice how to convert your dates using Power Query. [ddownload id="13294"] Share The Article & Sign Up For More Please feel free to share this post on Social Media and join my mailing list to be notified of new content and awesome selected training offers. Want More Power Query Stuff? If you want even more Power Query, Power Pivot or Microsoft BI training and tips then take a look at my recommended training supplier below.

How To Excel At Excel

Tag: Power BI