Data Models - Microsoft



Power Query and Flash FillContents TOC \o "1-3" \h \z \u Data Models PAGEREF _Toc14899041 \h 3Parsing the Data PAGEREF _Toc14899042 \h 6Inner Joins PAGEREF _Toc14899043 \h 7Normalize Tables PAGEREF _Toc14899044 \h 8Merge Worksheets With Empty Cells PAGEREF _Toc14899045 \h 9Combine Queries to Eliminate Vlookups PAGEREF _Toc14899046 \h 10Aggregate Amount To Eliminate SumIfs PAGEREF _Toc14899047 \h 10Flash Fill PAGEREF _Toc14899048 \h 12Data ModelsData Model is datasets that are not together. In other words, tables that need to be linked to extract data from two or more tables.In Excel 2010 and 2013 need to install the Power Query; however, in 2016 is automatically part of the Data Tab ribbon.Open 1. Data Model fileSee in the Design Tab under Table Tool contextual tab. The table names in the Properties Group.The table names are Transactions, Managers, and Availability. When the tables are created you need to select the headings as well.Explore each table and the Transactions table is comprised of information from the other two tables.The Transactions table has repeated items while the other two does not have repeated items.How many specialty were made during Leo's Shift? The answer31We need set up the relationships between the three tables.Click on Data Tab > Relationships in the Data Tools Group > New OR Insert Pivot Table, Analyze, RelationshipForeign keys can have repeated data in the column you need to use in the table like in Transactions table. Primary keys have NO repeats in the column you need to use in the table and they are used as the lookup tables. We want to form a one-to-many relationships. Create each relationships and Close when relationships are created.Flavors columns will act as the Primary key in the Availability table and Flavor column will act as the Foreigner key in the Transactions table.Time of Day column will act as the Primary key in the Managers table and Time column will act as the Foreigner key in the Transactions table.44962919600Click in the Transactions table and click on the Insert Tab > Pivot Table > New Worksheet > Add this to Data Model (Data Model is a relationship database looking at several tables at once). To see diagram: Click on Data Tab, Manage Data Model, Diagram View-24638052705000In the Pivot Table Field List, click All and click on each triangle to expand the list of fields for each table. Place the fields in the Row and Values sections.Below the Transactions table on the Sheet 1 has extra data.Move the data to be part of the table.Click on the Pivot table sheet and click on Refresh in the Analysis Tab.How many specialty shakes were made during Leo's Shift? The answer31Below the tables are extra information that needs to be added to the existing table. Select the table and move it below the main table. Right click on the Power Query result in the Power Query Pane and choose Refresh.35169580145You can create slicers as well to be more only display Leo’s information without clicking on the filter buttons.Parsing the DataIn Excel 2010 and 2013 need to install the Power Query; however, in 2016 is automatically part of the Data Tab ribbon and the commands are located in the Get & Transform Group.Open 2A and 2B. Parsing Text with Get & Transform.When the table is created by Insert > Table, you need to select the headings as well.The data has been inserted as a table. See in the Design Tab under Table Tool contextual tab that the table name is Table1 in the Properties Group.We want to parse out each item into separate columns.Click in the table and click on Data Tab > From TableThe Query Editor opens up and on the right side, you see the steps are going to be performed will be recorded in this area.Click on Split Colum > By Delimiter > Semicolon > Each occurrence of the delimiter Now we want to parse out the .com .org etc. from the emails using the new table.Select the email column.Click on Split Colum> By Delimiter > > Custom > type a period > Right-most delimiter.Click on Close & LoadBelow in the original table in Parse Text sheet has extra data.Select and move the data to be part of the table.Right click on the Workbook Queries on the right side and choose Refresh.With the 2 rows added to the 37 rows in the new query table now makes the table have 39 rows.The beauty of Workbook Queries is that you can create different queries on the same table without destroying the other queries.Reasons to Use Power Query:Using the Power Query Editor leaves the original table unharmedEliminates the hard work in developing advanced formulasPlaces the results in a new sheetInner JoinsObjective: List the people who are on both listsCapture their region, status and retake data-586696317306A-171A-357A-3740A-171A-357A-374Open 3. Inner Join ExampleRename each table to match the table data. In this example, Wednesday and Thursday.Sort the tables by ID and CodeClick one table and click on Data Tab > From Table > Load & Close.Repeat these actions with the other table.Right Click on the first table name in the Query Workbooks pane and choose merge.We need to choose the name of the table and the column where it needs to match with the other table and its column and choose Inner Join in the Join Kind box.2901950119189551 Count0051 Count2870200269049544 Count0044 Count49530022352000The results only show where records appear in both tables only.Normalize TablesIn order to pivot tables, they must be normalized. To this quickly, Power Query does the transformation in seconds.-24638027559000Open 4 and 4B. Unpivot ExampleSelect the Table and click on Data Tab > From Table.Select the column that needs to be filled and click on Transform Tab.Choose the Fill command > Down.Select the columns that need to be “unpivot” which are January to October.Click on the January header and then Shift+Click on October.Then on the Transform Tab > Unpivot Columns. See other choices.Close & LoadBelow in the original table in Sheet1 has extra data.Select and move the data to be part of the table.Right click on the Workbook Queries on the right side and choose Refresh. With the 4 rows containing 10 months will convert this information into 40 new rows. Added to the existing 120 rows makes the new table have 160 rows.Merge Worksheets With Empty CellsWhen you have more than one sheet to consolidate, let Power Query do all the work. Once the sheets are consolidated, then creating pivot table is a breeze.Open 5. Merge Order Sheet fileWe need to fix the worksheets where the Branch is repeated down the column.Format each table as Table by Insert Tab > Table > OK on each sheet also include headings. Name each table for each region.Select each table and click on Data Tab > From Table > Transform Tab > Fill > Down where customer column is selected.Close & Load To… > Only Create Connection > Load in order to connect all tables.Repeat for all tables.Right click in the Workbook Queries pane the first table and choose Append.Choose Three or more tables.Add the other three tables to the right side.Choose OKClose & Load The NorthEast table has 94 records, the East table has 50 records, the South table has 28 records, and the NorthWest table has 38 records. After the Append command, the new table has 210 bine Queries to Eliminate Vlookups Open 5B. Combine Queries Sheet fileThe two sheets have already been Close & Load To Connection Repeat for all tables.Click on Data Tab, Show QueriesTo create a lookup scenario and it is no longer necessary to have the lookup column to be the first column of the table.Data Tab > New Query > Combine Queries > Merge.Select the first table which is Data and select the AcctID. Then select the second table which is Lookup and select AcctID. This will be a Left Outer join where all the information from the first table is looked up from the second table.Choose OK.Aggregate Amount To Eliminate SumIfsClick on SumIf table and Data Tab > From Table > Load and Close35170262890Transform Tab > Group ByThe Group by field should be FS Line so that it creates one row for each unique value in the FS Line column. Then, we want a new column called Amount that is basically the Sum of the Amount column. So we update the dialog as follows:2740762930Close & Load The FS Line are now combined and aggregated.Flash FillMake sure that your Flash Fill is activated. Go to File > Options > Advanced > Editing Options > check Automatically Flash Fill.If Flash Fill is turned on but doesn’t start automatically when you type data that matches a pattern, try starting it manually by clicking Data > Flash Fillor by pressing Ctrl+E which it is not the centering command but a Flash Fill command and the new information must reside in a cell next to a table.TIPS BEFORE YOU GET STARTED1. To use Flash Fill you need to be in the column adjacent to the column(s) containing your original data.2. Format your headers different to your data to help Excel know that the top row is a header so it won’t use it in determining the pattern. 3. Give Flash Fill an example of the final result you want for every permutation in your data. This will help it accurately determine the pattern.4. Beware, sometimes it gets the pattern wrong, other times it’ll leave them blank. If it leaves blanks then you can just go and add an example for the remaining items and it’ll finish the job.5. If it makes a mistake you can just correct one of the entries and it will fix the rest by backspacing, edit and enter.Open 6. Flash Fill fileFlash Fill:combines namesextracts namesformat numbersadd text or numbersmove text & numbersextract names from emailsdollar amountscombination of text, currency and datetimethe possibilities are endless ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download