Excel Power Pivot & Power Query For Dummies. Michael Alexander
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
Sum aggregator 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 SUMX function 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 CALCULATE and FILTER together 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 IF statement 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.
12 Chapter 12FIGURE 12-1: Enter your custom code in the Advanced Editor window.FIGURE 12-2: Give your custom function a friendly name.FIGURE 12-3: A text file containing Invoice details.FIGURE 12-4: Use the Custom Column action to invoke your function.FIGURE 12-5: The custom column showing the results of the function for each row...FIGURE 12-6: You need to merge into one table the data in all the Excel files i...FIGURE 12-7: Connect to one of the Excel files in the target folder, and naviga...FIGURE 12-8: Use the Query Editor to apply any necessary transformation actions...FIGURE 12-9: Open the Advanced Editor to see the starter code.FIGURE 12-10: Wrapping the starter code with function tags and replacing the ha...FIGURE 12-11: The Query Editor after defining parameters.FIGURE 12-12: Create a new query using the From Folder connection type to retri...FIGURE 12-13: Use the Custom Column action to invoke the function.FIGURE 12-14: Power Query triggers the function and returns a table array for e...FIGURE 12-15: Click the Custom column header to expand the table arrays.FIGURE 12-16: Power Query exposes the columns pulled from each Excel file and a...FIGURE 12-17: Confirming that the parameters in the URL actually work.FIGURE 12-18: The clean base query.FIGURE 12-19: Wrapping the starter code with function tags and specifying a Yea...FIGURE 12-20: Create a simple parameter table.FIGURE 12-21: Use the Custom Column action to invoke the function.FIGURE 12-22: The combining of Excel and Web data triggers Power Query to ask a...FIGURE 12-23: Choose to load the final query results under the parameters table...FIGURE 12-24: The final parameter query provides an interactive mechanism to fl...
13 Chapter 13FIGURE 13-1: A star schema is the most efficient data model, with a single fact...FIGURE 13-2: Snowflake schemas are less efficient, causing Power Pivot to perfo...FIGURE 13-3: Slicers work together to show relevant data items based on a selec...FIGURE 13-4: Deselecting the Visually Indicate Items option with No Data disabl...
14 Chapter 14FIGURE 14-1: Hover the cursor over a query to get quick information, including ...FIGURE 14-2: Queries can be organized into groups.FIGURE 14-3: Use the Choose Columns command to find and select columns faster.FIGURE 14-4: Get in the habit of renaming applied steps.FIGURE 14-5: Create a new query from an existing column.FIGURE 14-6: The Query Dependencies dialog box displays how each of your querie...FIGURE 14-7: Use the Global Data Load options to set a default load behavior.FIGURE 14-8: Power Query automatically adds a step to change data types when da...FIGURE 14-9: Disabling the type detection feature.FIGURE 14-10: Disabling the privacy-level settings.FIGURE