In the past, you might have used formulas such as =TEXT(A2,“DDDD”) to add a new column to your source data to return the weekday. The new Column From Examples feature in Power Query provides a new, more effective solution. Unfortunately, this feature is limited to Windows versions of Excel 2016 and later.
Start with a data set that includes a date column. Go to Home, Format as Table to declare the data as a table. Then, with one cell in the data selected, go to the Data tab. In the Get & Transform Data section, click on From Table/Range.
This will open the Power Query Editor. Click on the heading for the Date column. On the Add Column tab, choose Column From Examples.
The date in the first row of data is January 1, 2018, which was a Monday. Column From Examples creates a new Column1 on the right side of the screen. Type “Monday” in the box, and Power Query will suggest “Day of Week from Date.”
Press Enter, and a new column will be added to the data preview window showing the corresponding weekday for each date in the table. The formula bar will show the Power Query function used to create the column: Date.DayofWeekName([Date]). This formula illustrates one thing that can be frustrating for Excel veterans: Formulas in Power Query are case-sensitive. In Excel, a formula could be typed as =text(A2,“DDDD”), as =TEXT(A2,“dddd”), or even as =TeXt(a2,“ddDd”) and return the results. But in Power Query, you have to use the appropriate capitalization. But since Column From Examples generates the formula itself, you don’t have to worry about remembering the proper capitalization.
Repeat the Column From Examples technique to get a column containing the month name. The formula bar will reveal a formula of Date.MonthName([Date]).
IF YOU DON'T HAVE COLUMN FROM EXAMPLES
The Column From Examples functionality was added for Office 365 customers last year. If you don’t have that icon in Power Query, you can use the Custom Column icon and build a formula. Type =Date.DayofWeekName(. Then, find the date field in the list of fields. Double-click to insert the field in the formula. Type a closing parenthesis and click OK.
After you have new Weekday and Month fields in Power Query, you can return the data to Excel. Instead of using the Close and Load button on the Home tab, open the Close and Load dropdown menu and choose Close and Load to…, which opens the Import Data dialog.
Click Only Create Connection. Click Add This Data To The Data Model, then OK. After this, you’ll be taken back to Excel. A new Queries and Connections panel will appear along the right edge of Excel.
Go to a blank area of the worksheet where you want the pivot table to appear. Choose Insert, Pivot Table. The Create PivotTable dialog box will be preloaded to Use This Workbook’s Data Model. Click OK.
The PivotTable Fields pane will offer two items called Table1. Look for the one with an orange cylindrical icon. This is the icon that indicates the version of the table containing the extra fields.
Build the pivot table with Weekday, Month, and Sales.
PIVOT TABLE SORTING
There’s still one annoyance about pivot tables based on the Data Model. The pivot table won’t sort the days of the week or month names sequentially. When you see months in the April, August, December, February sequence, those months are sorted alphabetically.
Click the drop-down arrows in cells B1 and A2. In each case, choose More Sort Options. In the Sort dialog, choose Ascending by Weekday, then click the More Options button in the lower-left of the dialog. In the More Sort Options dialog that opens, uncheck “Sort automatically every time the report is updated.” Then you can choose the proper list from the First Key Sort Order drop-down.
After sorting both the Month and Weekday fields, you will have a pivot table showing sales by Month and Weekday.
Depending on the analysis, you might use the Field Settings to change the Sum to an Average. Apply a Color Scale, and you can see the largest weekday for sales.
THE ADVANTAGE OF POWER QUERY
The first time building this pivot table using Power Query will require more effort. Building it using methods outside of Power Query would be faster. If you invest the extra time to define the steps in Power Query, however, updating the pivot table will become much simpler and quicker.
To update the data, paste new data at the bottom of the old data. In the Queries & Connections panel, hover over the query name and a Refresh icon will appear. Click Refresh, and all of the steps to add the new columns happen automatically. Refresh the pivot table, and the report is updated.
SF SAYS
The Power Query’s Column From Examples is like Excel’s Flash Fill.
March 2019
