Excel Power Pivot & Power Query For Dummies. Michael Alexander
Читать онлайн книгу.
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