Michael Alexander - Excel Power Pivot & Power Query For Dummies

6 Chapter 6FIGURE 6-1: Start the calculated column by entering an operation on the Formula...FIGURE 6-2: Calculated columns automatically show up in the PivotTable Fields L...FIGURE 6-3: You can use the formatting tools found on the Power Pivot window’s ...FIGURE 6-4: The new Gross Margin calculation is using the previously created [T...FIGURE 6-5: Right-click and select Hide from Client Tools.FIGURE 6-6: Hidden columns are grayed-out, and calculated columns have darker h...FIGURE 6-7: The Insert Function dialog box shows you all available DAX function...FIGURE 6-8: The DAX SUM function can only sum the column as a whole.FIGURE 6-9: DAX functions can help enhance the invoice header data with Year an...FIGURE 6-10: Using DAX functions to supplement a table with Year, Month, and Mo...FIGURE 6-11: DAX calculations are immediately available in any connected pivot ...FIGURE 6-12: Month names in Power Pivot-driven pivot tables don’t automatically...FIGURE 6-13: The Sort by Column dialog box lets you define how columns are sort...FIGURE 6-14: The month names now show in the correct month order.FIGURE 6-15: The discount amount in the Customers table can be used in a calcul...FIGURE 6-16: Use the RELATED function to look up a field from another table.FIGURE 6-17: The final discount amount calculated column using the Discount% co...FIGURE 6-18: Creating a new calculated measure.FIGURE 6-19: Calculated measures can be seen in the PivotTable Fields List.FIGURE 6-20: The Manage Measures dialog box lets you edit or delete your calcul...FIGURE 6-21: These cells are now a series of Cube functions.FIGURE 6-22: Excel gives you the option of converting your report filter fields...FIGURE 6-23: Cube functions give you the flexibility of restructuring your pivo...

7 Chapter 7FIGURE 7-1: Creating a new measure that calculates Total Revenue.FIGURE 7-2: The results of a calculated measure can be seen by adding it to a p...FIGURE 7-3: Creating a new measure that will calculate Total Units.FIGURE 7-4: DAX allows you to use existing measure as arguments in other measur...FIGURE 7-5: DAX formulas recalculate to show appropriate results based on the d...FIGURE 7-6: I need a calculated measure that returns OrderQuantity * UnitPrice ...FIGURE 7-7: The [Realized Sales] measure uses a simple Sumaggregator function ...FIGURE 7-8: The [Realized Sales] measure looks good at the granular OrderNumber...FIGURE 7-9: The math falls apart when you move to any granularity above OrderNu...FIGURE 7-10: Using the SUMXfunction enables the measure to retain row context ...FIGURE 7-11: The new [Realized PriceX] measure remains accurate now at every ag...FIGURE 7-12: You need to calculate the sum of Sales Amount in cell E1, but only...FIGURE 7-13: The resulting answer is for the specific filter context applied.FIGURE 7-14: Each cell in a pivot table contains its own filter context.FIGURE 7-15: Filter context is propagated between tables via relationships in t...FIGURE 7-16: The [Bike Sales] measure has its own product category context, so ...FIGURE 7-17: Using CALCULATEand FILTERtogether to establish a new filter cont...

8 Chapter 8FIGURE 8-1: Starting a Power Query web query.FIGURE 8-2: Enter the target URL containing the data you need.FIGURE 8-3: Select the correct data source and then click the Transform Data bu...FIGURE 8-4: The Query Editor window allows you to shape, clean, and transform d...FIGURE 8-5: Change the data type of the High, Low, and Close fields to currency...FIGURE 8-6: Select the columns you want to keep, and then select Remove Other C...FIGURE 8-7: Removing errors caused by text values that could not be converted t...FIGURE 8-8: The Power Query Editor can be used to apply transformation actions ...FIGURE 8-9: The Import Data dialog box gives you more control over how the resu...FIGURE 8-10: Your final query pulled from the internet: transformed, put into a...FIGURE 8-11: You can view and manage query steps in the Applied Steps section o...FIGURE 8-12: Right-click on any query step to edit, rename, delete, or move the...FIGURE 8-13: Right-click any query in the Queries & Connections pane to see the...FIGURE 8-14: Click the Table Actions icon in the upper-left corner of the Query...

9 Chapter 9FIGURE 9-1: Select the data sources you want to work with, and then click the L...FIGURE 9-2: Preview the data and use the option drop-down menus to tell Power Q...FIGURE 9-3: The available tables and pages in the PDF are shown in the Navigato...FIGURE 9-4: Data preview of the files in the target folder.FIGURE 9-5: Use the Power Query Editor to add more file attributes to the impor...FIGURE 9-6: Power Query offers connection types for many of the popular databas...FIGURE 9-7: Tools for connection to Microsoft Azure cloud database services.FIGURE 9-8: Starting an ODBC connection.FIGURE 9-9: Select the view you want imported, and then click the Load button.FIGURE 9-10: The final imported database data.FIGURE 9-11: Edit a data source by selecting it and clicking the Edit Permissio...FIGURE 9-12: Edit a data source by selecting it and clicking the Transform Data...FIGURE 9-13: Data Profiling options are found in the Data View group under the ...FIGURE 9-14: Choose Column Profiling Based on Entire Data Set to get a more com...FIGURE 9-15: Exposing the quick actions for a column using the data column qual...FIGURE 9-16: Right-clicking a column profile histogram bar exposes the quick ac...

10 Chapter 10FIGURE 10-1: Does this table have duplicate records? It depends on how you defi...FIGURE 10-2: Removing duplicate records.FIGURE 10-3: Undo the removal of records by deleting the Removed Duplicates ste...FIGURE 10-4: Replacing null with 0.FIGURE 10-5: Replacing empty strings with the word Undefined. FIGURE 10-6: Merging the Type and Code fields.FIGURE 10-7: The Merge Columns dialog box.FIGURE 10-8: The original columns are removed and replaced with a new, merged c...FIGURE 10-9: Reformatting the ContactName field to proper case.FIGURE 10-10: Replacing text values.FIGURE 10-11: Leading spaces can cause issues in analysis.FIGURE 10-12: The Trim command.FIGURE 10-13: The Extract command allows you to pull out parts of the text foun...FIGURE 10-14: Extracting the first three characters of the Phone field.FIGURE 10-15: Extracting the two middle characters of the SicCode.FIGURE 10-16: The Split Column command can easily split the ContactName Field i...FIGURE 10-17: Splitting the ContactName column at every occurrence of a comma.FIGURE 10-18: The ContactName field has been split successfully into three colu...FIGURE 10-19: Matrix layouts are problematic for data analysis.FIGURE 10-20: All months are now in a tabular format.FIGURE 10-21: Use Unpivot Other Columns when the number of matrix columns is va...FIGURE 10-22: Pivoting the Month and Value columns.FIGURE 10-23: Confirm the aggregation operation to finalize the pivot transform...FIGURE 10-24: The Custom Column dialog box.FIGURE 10-25: A formula to merge the Type and Code columns.FIGURE 10-26: Use the Data Type drop-down menu to discover and select the data ...FIGURE 10-27: A formula to create a consistent ten-digit padded CompanyNumber.FIGURE 10-28: Applying an IFstatement in a custom column.FIGURE 10-29: Using the Group By dialog box to create a view of 2021 Total Pote...FIGURE 10-30: The resulting aggregate view by State and City.FIGURE 10-31: Creating a custom data type.FIGURE 10-32: Each value in a data type column contains the data for underlying...FIGURE 10-33: Data types have a special icon next to each value and allow you t...FIGURE 10-34: Referencing a data type value and entering the dot (.) operator a...

11 Chapter 11FIGURE 11-1: This data can be used as the source for various levels of aggregat...FIGURE 11-2: Merge the Last_Name and First_Name columns to create a new Employe...FIGURE 11-3: Group the Employee field and Sum Sales Amount to create a new Reve...FIGURE 11-4: All the query steps before Grouped Rows are needed in order to pre...FIGURE 11-5: Naming the new query SalesByBusiness.FIGURE 11-6: The two queries are now sharing the extracted steps.FIGURE 11-7: The data found on each region tab needs to be consolidated into on...FIGURE 11-8: Create a connection-only query for each region.FIGURE 11-9: Appending multiple queries to NorthData.FIGURE 11-10: The final output.FIGURE 11-11: The kinds of joins supported by Power Query.FIGURE 11-12: You need to merge the Questions and Answers queries into one tabl...FIGURE 11-13: Activating the Merge dialog box.FIGURE 11-14: The Completed Merge dialog box.FIGURE 11-15: Expand the NewColumn field and choose the merged fields you want ...FIGURE 11-16: The final table with merged questions and answers.FIGURE 11-17: Right-click the Source query step and select Edit Settings to rea...FIGURE 11-18: The Merge dialog box with Fuzzy Matching selected.