Читать книгу Excel Power Pivot & Power Query For Dummies - Michael Alexander - Страница 5

1 Chapter 1FIGURE 1-1: Data is stored in an Excel spreadsheet using a flat-table format.FIGURE 1-2: Databases use relationships to store data in unique tables and simp...

2 Chapter 2FIGURE 2-1: The Power Pivot Ribbon interface.FIGURE 2-2: You want to use Power Pivot to analyze the data in the Customers, I...FIGURE 2-3: Convert the data range into an Excel table.FIGURE 2-4: Give your newly created Excel table a friendly name.FIGURE 2-5: The Power Pivot window shows all the data that exists in your data ...FIGURE 2-6: Each table you add to the data model is placed on its own tab in Po...FIGURE 2-7: Diagram view allows you to see all tables in the data model.FIGURE 2-8: To create a relationship, you simply click and drag a line between ...FIGURE 2-9: When you create relationships, the Power Pivot diagram shows join l...FIGURE 2-10: Use the Manage Relationships dialog box to edit or delete existing...FIGURE 2-11: Use the Edit Relationship dialog box to adjust the tables and fiel...FIGURE 2-12: You now have a Power Pivot-driven pivot table that aggregates acro...

3 Chapter 3FIGURE 3-1: The values area of a pivot table calculates and counts data.FIGURE 3-2: The row area of a pivot table gives you a row-oriented perspective.FIGURE 3-3: The column area of a pivot table gives you a column-oriented perspe...FIGURE 3-4: The filter area allows you to easily apply filters to the pivot tab...FIGURE 3-5: Start a pivot table via the Insert tab.FIGURE 3-6: The Create PivotTable dialog box.FIGURE 3-7: The PivotTable Fields task pane.FIGURE 3-8: Select the Market check box.FIGURE 3-9: Add the Sales Amount field by selecting its check box.FIGURE 3-10: Adding a layer of analysis is as easy as bringing in another field...FIGURE 3-11: Your business segments are now column oriented.FIGURE 3-12: Adding Region to the Filters drop zone displays a Region drop-down...FIGURE 3-13: Refreshing the pivot table captures changes made to your data.FIGURE 3-14: Changing the range that feeds the pivot table.FIGURE 3-15: Select the new range that feeds the pivot table.FIGURE 3-16: The three layouts for a pivot table report.FIGURE 3-17: Changing the layout of the pivot table.FIGURE 3-18: Right-click the target field to select the Value Field Settings op...FIGURE 3-19: Use the Custom Name input box to change the name of the field.FIGURE 3-20: Changing the type of summary calculation used in a field.FIGURE 3-21: Subtotals sometimes muddle the data you’re trying to show.FIGURE 3-22: Use the Do Not Show Subtotals option to remove all subtotals at on...FIGURE 3-23: The report shown in Figure 3-21, without subtotals.FIGURE 3-24: Choose the None option to remove subtotals for one field.FIGURE 3-25: To remove Bikes from this analysis …FIGURE 3-26: … deselect the Bikes check box.FIGURE 3-27: The analysis from Figure 3-25, without the Bikes segment.FIGURE 3-28: Clicking the Select All check box forces all data items in that fi...FIGURE 3-29: All sales periods are showing.FIGURE 3-30: Filtering for the Europe region causes certain sales periods to di...FIGURE 3-31: Select the Show Items with No Data option to force Excel to displa...FIGURE 3-32: All sales periods are now displayed, even if there is no data to b...FIGURE 3-33: Applying a sort to a pivot table field.FIGURE 3-34: Default pivot table Filter fields do not work together to limit fi...FIGURE 3-35: Filter fields show the phrase (Multiple Items) whenever multiple s...FIGURE 3-36: Slicers work together to show you relevant data items based on you...FIGURE 3-37: Slicers do a better job at displaying multiple item selections.FIGURE 3-38: Inserting a slicer.FIGURE 3-39: Select the fields for which you want slicers created.FIGURE 3-40: Select the fields you want filtered using slicers.FIGURE 3-41: The fact that you can see the current filter state gives slicers a...FIGURE 3-42: Clearing the filters on a slicer.FIGURE 3-43: Adjust the slicer size and placement by dragging its position poin...FIGURE 3-44: The Format Slicer pane offers more control over how the slicer beh...FIGURE 3-45: Adjust the Number of Columns property to display the slicer data i...FIGURE 3-46: The Slicer Settings dialog box.FIGURE 3-47: Choose the pivot tables to be filtered by this slicer.FIGURE 3-48: Select the date fields for which you want slicers created.FIGURE 3-49: Click a date selection to filter the pivot table or pivot chart.FIGURE 3-50: You can expand the range on the Timeline slicer to include more da...FIGURE 3-51: Quickly switch among Quarters, Years, Months, and Days.

4 Chapter 4FIGURE 4-1: Open the Table Import Wizard and select Microsoft SQL Server.FIGURE 4-2: Provide the basic information needed to connect to the target datab...FIGURE 4-3: Choose to select from a list of tables and views.FIGURE 4-4: The Table Import Wizard offers up a list of tables and views.FIGURE 4-5: The Preview & Filter screen allows you to uncheck columns you don’t...FIGURE 4-6: Use the drop-down arrows next to each column to filter out unneeded...FIGURE 4-7: The last screen of the Table Import Wizard shows you the progress o...FIGURE 4-8: Open the Table Import Wizard and select Microsoft Access.FIGURE 4-9: Provide the basic information needed to connect to the target datab...FIGURE 4-10: Open the Table Import Wizard and select your target relational dat...FIGURE 4-11: Enter the connection string for your database system.FIGURE 4-12: Use the Data Link Properties dialog box to configure a custom conn...FIGURE 4-13: The Table Import Wizard displays the final syntax for your connect...FIGURE 4-14: Open the Table Import Wizard and select Excel File.FIGURE 4-15: Provide the basic information needed to connect to the target work...FIGURE 4-16: Select the data sources to import.FIGURE 4-17: Open the Table Import Wizard and select Text File.FIGURE 4-18: Provide the basic information needed to connect to the target text...FIGURE 4-19: You can copy data straight out of Microsoft Word.FIGURE 4-20: The Paste Preview dialog box gives you a chance to see what you’re...FIGURE 4-21: Power Pivot allows you to refresh one table or all tables.FIGURE 4-22: Select a connection and click the Properties button.FIGURE 4-23: The Connection Properties dialog box lets you configure the chosen...FIGURE 4-24: The Connection Properties dialog box lets you configure the chosen...FIGURE 4-25: Use the Existing Connections dialog box to reconfigure your Power ...

5 Chapter 5FIGURE 5-1: This table shows transactions by employee number.FIGURE 5-2: This table provides information on employees: first name, last name...FIGURE 5-3: When you create a new pivot table from the Transactions table, be s...FIGURE 5-4: Create a new pivot table from the Employees table, and select Add T...FIGURE 5-5: Select ALL in the PivotTable Fields list to see both tables in the ...FIGURE 5-6: When Excel prompts you, choose to create the relationship between t...FIGURE 5-7: Build the appropriate relationship using the Table and Column drop-...FIGURE 5-8: You’ve achieved your goal of showing sales by job title.FIGURE 5-9: The Manage Relationships dialog box enables you to make changes to ...FIGURE 5-10: Use the Queries & Connections task pane to manage the queries and ...FIGURE 5-11: Open the Create PivotTable dialog box and choose the external data...FIGURE 5-12: Use the Existing Connections dialog box to select the Data Model a...FIGURE 5-13: The newly created pivot table shows all tables in the internal dat...FIGURE 5-14: Getting data from a Microsoft Access database.FIGURE 5-15: Enable the selection of multiple tables.FIGURE 5-16: Place a check next to each table you want import to the internal d...FIGURE 5-17: Create a PivotTable Report from the Import Data dialog.FIGURE 5-18: You’re ready to build your pivot table analysis based on multiple ...

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 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 14-11: Disabling relationship detection.