On Demand Webinar
Webinar Details $219
- Webinar Length: 100 Minutes
- Guest Speaker: Mike Thomas
- Topic: Business Administration, Business Skills, Software, Taxation and Accounting
- Credit: CPE 2.0
-
Do you import data into Excel from other sources (such as CSV files, text files, web pages, databases or SharePoint)?
Does the data that you import need to be "cleaned" before it can be used? (cleaning refers to things like removing duplicate rows, removing blank rows, removing unnecessary columns, converting case, changing date formats and more).
Do you spend more time than necessary performing data cleaning tasks?
If the answer to any of these questions is yes, you need to learn how to use Power Query!
Why you should attend
If you work with, analyze and generate reports from large datasets, having a good understanding and working knowledge of Power Query is a must!
Topics covered
- Importing data into Excel from sources such as text files, web pages and databases
- Avoiding Excel’s "million-rows limitation” by connecting to external data sources
- Updating the imported data (when the source data changes) with two clicks
- Creating Pivot Tables from external data sources
- Using the Query Editor to quickly clean and transform data to make it useable
- Using the Query Editor to create calculated columns
- Introduction
- Importing Data 00:03:04
- Importing Data - Creating A Dashboard 00:03:42
- Importing Data - Raw DataSpreadsheet Example 00:04:02
- Importing Data - Exporting Data 00:04:15
- Importing Data - .CSV to .XLSX 00:04:40
- Clean Up Imported Data 00:05:00
- Transform Data - Exchange Rate Table Example 00:06:06
- Transform Data - Combining Data 00:06:28
- Power Query (Excel 2010 & 2013 for Windows) 00:06:36
- Excel 2016 And Later 00:07:15
- Source File 00:08:20
- Getting Data Into A File 00:12:00
- How To Bring Data In From A Webpage 00:17:36
- How To Keep Data Up-To-Date 00:23:52
- Automating Refresh 00:27:28
- Downloading Data From Multiple Excel Files 00:36:35
- Importing Data From A PDF File 00:40:12
- Power Query - Getting And Keeping the Data Clean 00:45:15
- How To Clean Data From A .CSV File With Blank Columns And Rows 01:01:28
- Process Overview 01:04:15
- Filtering 01:06:37
- Removing Filtered Rows 01:11:06
- Going Through The Applied Steps - Filtered Rows 01:12:47
- How To Add Back A Column That Was Removed 01:20:27
- Adding A Character To The Beginning Of A Column 01:22:51
- Source File 01:27:05
- Rating Perks From A Survey 01:27:16
- How To Transform From A Tabular Layout To A Plain List 01:32:15
- Employee Report File 01:34:41
- Renaming The Applied Steps 01:35:44
- The Power Query Language 01:37:35
- Speaker Email 01:39:00
- Presentation Closing 01:39:41
- .CSV 00:04:37, 00:22:00, 00:33:15, 01:01:33
- .XLSX 00:04:46
- Applied Steps 00:51:51
- Cell 00:46:59
- Chart 00:09:25, 00:12:46
- Column 00:46:00, 00:56:26, 00:58:31, 01:00:13, 01:00:46, 01:19:55
- Dashboard 00:09:03, 00:55:59
- Dialog Box 00:15:36
- Filter 01:06:37, 01:07:49, 01:36:13
- Format 00:05:07
- PDF 00:22:03, 00:40:15, 00:44:16
- Pivot Table 00:09:25, 00:13:08
- Power Query 00:01:25, 00:03:07, 00:04:44, 00:05:30, 00:06:51, 00:13:39, 00:14:17, 00:49:00, 00:58:16, 00:59:22, 01:04:54, 01:10:58
- Power Query Editor 00:49:52, 00:50:26, 0:51:35, 00:54:56, 01:01:50, 01:07:28, 01:11:35, 01:30:56, 01:37:36
- Query 00:28:13, 00:43:25, 00:49:08, 00:57:40, 01:09:19, 01:13:21, 01:16:51
- Ribbon 00:07:05
- Row 00:47:40, 00:54:00, 01:00:52, 01:35:54
- Table 00:22:39, 00:46:44, 00:50:57, 00:55:45
- Text Files 00:03:27
- Workbook 00:08:42, 00:38:21
.CSV: Comma-Separated Value files are text files where each field of data is separated by a comma. This is an effective means to export data from QuickBooks that you, in turn, wish to analyze in Excel.
.XLSX: A file with the. xlsx file extension is a Microsoft Excel Open XML Spreadsheet (XLSX) file created by Microsoft Excel. You can also open this format in other spreadsheet apps, such as Apple Numbers, Google Docs, and OpenOffice.
Applied Steps: In the Query Settings pane, you can rename steps, delete steps, or reorder the steps as you see fit. To do so, right-click the step in the Applied Steps section, and choose from the menu that appears. All query steps are carried out in the order they appear in the Applied Steps pane.
Cell: In spreadsheet applications, a cell is a box in which you can enter a single piece of data. The data is usually text, a numeric value, or a formula. The entire spreadsheet is composed of rows and columns of cells.
Chart: In Microsoft Excel, a chart is often called a graph. It is a visual representation of data from a worksheet that can bring more understanding to the data than just looking at the numbers. A chart is a powerful tool that allows you to visually display data in a variety of different chart formats such as Bar, Column, Pie, Line, Area, Doughnut, Scatter, Surface, or Radar charts.
Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.
Dashboard: An Excel dashboard is a one-pager (mostly, but not always necessary) that helps managers and business leaders in tracking key KPIs or metrics and take a decision based on them. It contains charts/tables/views that are backed by data. A dashboard is often called a report, however, not all reports are dashboards.
Dialog Box: A dialog box in Excel is a screen where you input information and make choices about different aspects of the current worksheet or its content, such as data, charts, and graphic images.
Filter: The Filter feature in Excel allows you to show or hide rows within a list of data by making selections from drop-down lists. The Filter feature is available on the Data tab of all versions of Excel as well under the Sort & Filter command on the Home menu.
Format: When we format cells in Excel, we change the appearance of a number without changing the number itself. We can apply a number format (0.8, $0.80, 80%, etc) or other formatting (alignment, font, border, etc). By default, Excel uses the General format (no specific number format) for numbers.
PDF: Portable Document Format, a universal document format created by Adobe that allows cross-platform compatibility of documents.
Pivot Table: A report creation tool in Excel that enables you to quickly summarize lists of data into summary reports by clicking checkboxes and dragging fields onscreen.
Power Query: Power Query is a data connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs. Features in Power Query are available in Excel and Power BI Desktop. Power Query is one of three data analysis tools available in Excel: Power Pivot.
Power Query Editor: Power BI Desktop also comes with Power Query Editor. Use Power Query Editor to connect to one or many data sources, shape and transform the data to meet your needs, then load that model into Power BI Desktop.
Query: A database query extracts data from a database and formats it in a readable form. A query must be written in the language the database requires; usually, that language is Structured Query Language (SQL). For example, when you want data from a database, you use a query to request that specific information.
Ribbon: The "ribbon" is the strip of buttons and icons located above the work area that was first introduced in Excel 2007. The ribbon replaces the menus and toolbars found in earlier versions of Excel. Above the ribbon are a number of tabs, such as Home, Insert, and Page Layout.
Row: A row is the range of cells that go across (horizontal) the spreadsheet/worksheet. Rows are identified by numbers e.g. row 1, row 5. Examples of use. A row might contain the headings of a table e.g. product ID, product name, price, number sold.
Table: A table is an arrangement of data in rows and columns, or possibly in a more complex structure. Tables are widely used in communication, research, and data analysis. Tables appear in print media, handwritten notes, computer software, architectural ornamentation, traffic signs, and many other places.
Text Files : Raw data files that often have file extensions such as .TXT or .CSV. TXT files are sometimes tab-delimited (meaning each field is separated by a tab character) while CSV files are comma-delimited.
Workbook: In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets, in a single file.
