Import, Filter, Edit From Multiple Data Sources: Access, Excel, Text
See how to Import, Filter, Edit Data From Access, Excel, Text Files into one Data Model, including importing new tables and columns and deleting tables after an existing connection is established:
(00:28) goal of video: establish connections between Data Model and external data sources and then see how to edit and change existing connections using the Manage Data Model window.
(00:46) Look at Access Database and the three tables and the one-to-many relationships. Importing Access tables with existing relationships into PowerPivot Data Model will retain the relationships (relationships will show up in PowerPivot Data Model).
(01:58) look at text file.
(02:27) Import three Access tables with existing relationships.
(03:55) Filter out items from a column in an imported table.
(05:00) Filter out a whole column from an imported table.
(06:04) Use Diagram View to see that Relationships from Access have been imported.
(06:14) Use Scroll Bar to Zoom in to see all tables in Diagram View.
(06:42) Import Table from Text file.
(07:53) Although we can use PivotTable button from Manage Data Model window, we don’t have to use this button to have access to a Data Model for a new PivotTable. See how to access the Data Model using the normal “Create PivotTable” dialog box and the “Use an external data source” dialog button, “Choose Connection” button, “Table” tab in the Existing Connections dialog box, then click “This workbook Data Model”.
(08:56) NO GROUPING DATES IN POWERPIVOT!!!! Grouping Feature in a normal PivotTable is NOT available in PowerPivot. Grouping of dates requires that you create a separate table of unique dates called a Calendar Table or date table.
(09:30) Clear All; Formatting and Context and PivotTable: Alt, E, A, A
(09:42) Use Excel to create a separate table of unique dates called a Calendar Table or date table.
(11:54) Import Excel Table. Import Calendar Table into PowerPivot.
(12:52) Create Sales Category Table (for creating Frequency Tables where we count or add between an upper and lower number or limit) in Excel with Excel Formulas. See formula 1 for upper and lower Numbers.
(14:35) Using “Existing Connections” button in “Get External Data” group in Home Ribbon of Manage Data Model window to import a new table from an existing connection to an Excel workbook Data Source.
(15:27) Go back to Excel workbook and add a descriptive category for our sales categories using a text formula that uses the DOLLAR function (takes number, applies Currency Formatting and delivers it as text) and text in Double Quotes and the Ampersand (Join Symbol).
(17:33) Import New Column from a table that has an existing connection using the “Table Properties” button in the “Design Ribbon tab” in the “Manage Data Model” window.
(18:47) Delete a Table from Data Model. Remove a Table from data Model. Right-click tab, Delete.
(19:27) Conclusion: Look at Diagram View.