Introduction To Power BI and Excel-Based Alternatives
Webinar Details $219
- Rated:
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Software, Business Skills, Business Administration, Taxation and Accounting, Finance
- Credit: ATAOP 1.5, ATAAA 1.5, CPE 2.0
-
Power BI is a Microsoft product that empowers you to create interactive business intelligence tools, such as dashboards. In this session author and Excel expert David H. Ringstrom, CPA will explain the concept of dashboards, which allow you to summarize data while also being able to quickly drill down into the details. David will first show how to use Power Query to prep data for analysis in Excel or Power BI. He’ll then compare how you can summarize and present data both in Microsoft Excel and Power BI.
David is the author of “Exploring Microsoft Excel's Hidden Treasures: Turbocharge your Excel Proficiency with expert tips, automation techniques, and overlooked features”. He demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Excel for Microsoft 365. David draws your attention to any differences in Excel 2021, 2019, or 2016 during the presentation and in his detailed handouts. The handouts include an Excel workbook with most of the examples he uses during his demonstrations.
Excel for Microsoft 365 is a subscription-based product that receives periodic feature updates. Conversely, perpetually licensed versions have year numbers in their names and do not receive any feature updates.
Topics covered:
• Adding interactivity to PivotTables by using the Slicer feature to filter.
• Creating a PivotTable in Excel as a frame of reference for a Matrix in Power BI.
• Adding tables to a Power BI canvas.
• Comparing an Accounts Receivable Aging Detail report to the Summary version.
• Introducing the Power Query feature in Excel.
• Comparing the free versions of Power BI to the paid versions.
• Transforming reports for use in Power BI with Power Query.
• Exploring the Power BI interface
• Using Power Query to clean up accounting reports to remove pitfalls like blank rows, merged cells, missing data, and more.
• Creating mobile layouts and sharing Power BI reports.
• Demonstrating how slicers control all objects on a Power BI report.
• Tracing through a typical Power BI workflow.
Learning objectives:
• State which command enables you to update a Power Query data set.
• State which Power BI task pane enables you to add features to a report.
• Define hierarchy form for dates within Power BI visualizations.
Level:
Intermediate
Format:
Self-Study
Instructional Method:
On-demand webcast
NASBA Field of Study:
Computer Software & Applications (2 hours)
Program Prerequisites:
Prior experience with Microsoft Excel is recommended.
Advance Preparation:
None
- Introduction
- Excel Versions 00:01:13
- Excel vs. Power Query vs. Power BI 00:02:01
- Free Power BI vs. Paid Power BI 00:03:31
- A Typical Power BI Workflow 00:04:52
- Summary Reports May Not Suffice 00:08:40
- Detail Reports Provide More Options 00:09:42
- Report Clean-Up Automation Overview 00:10:48
- Clean A/R Aging with Power Query 00:13:49
- Clean A/R Aging with Power Query - Steps 1 - 8 00:17:36
- Clean A/R Aging with Power Query - Steps 9 - 18 00:22:06
- Clean A/R Aging with Power Query - Steps 19-24 00:30:01
- Clean A/R Aging with Power Query - Steps 28- 36 00:36:55
- Clean A/R Aging with Power Query - Steps 37 - 42 00:42:25
- Create PivotTable from Power Query Data 00:48:28
- PivotTable Slicers 00:53:50
- Overwriting a Power Query Data Source 00:56:59
- Connecting Power BI to an Excel Workbook 01:00:20
- Transform the A/R Report 01:06:44
- Exploring the Power BI Interface 01:12:39
- Creating a Power BI Matrix 01:13:08
- Creating a Power BI Slicer 01:18:19
- Formatting the Slicer - Steps 31 - 36 01:20:08
- Formatting the Slicer - Steps 37 - 42 01:23:29
- Creating a Power BI Chart 01:24:47
- Completed Matrix/Chart/Slicer 01:30:43
- Adding a Table to Power BI 01:31:41
- Formatting Power BI Data 01:37:22
- Q&A Visual in Power BI 01:43:17
- Interacting with a Power BI Report 01:47:53
- Overwriting a Power BI Data Source 01:49:39
- Mobile Layout/Sharing Power BI Reports 01:51:23
- Thank you for attending! 01:52:42
- .PBIX 00:03:43
- .XLSX 00:11:28
- Accounting Receivable (AR)
- Analyze 00:07:59, 00:54:14
- Audit 00:25:28
- Cell 00:16:07
- Column 00:09:16, 00:14:26, 00:30:19, 00:33:01, 00:43:42, 01:20:46, 01:23:37
- Fill Down 00:37:02
- Filter 00:15:49, 00:38:09, 00:42:32, 01:11:08, 01:24:59
- Format 00:01:08, 00:09:23, 00:14:02, 00:47:02, 01:20:30, 01:34:14
- Formula 00:08:15
- Microsoft 365 00:01:25, 00:22:46, 00:24:01
- PDF 00:22:24
- Pivot Table 00:07:02, 00:09:06, 00:43:53, 00:48:38, 00:51:03, 00:55:52
- Power BI 00:00:06, 00:02:26, 00:04:00, 00:06:49, 00:10:19, 00:14:47, 00:18:57, 00:25:18, 00:43:07, 00:58:04, 01:09:52, 01:16:31, 01:48:18
- Power BI Chart 01:24:52, 01:30:51
- Power BI Desktop 00:02:42, 00:03:35
- Power BI Matrix 00:07:21, 00:49:36, 00:51:18, 00:58:05, 01:13:13, 01:30:49, 01:48:04
- Power BI Service 00:02:52
- Power Query 00:01:00, 00:02:17, 00:02:57, 00:05:14, 00:06:23, 00:09:53, 00:12:34, 00:16:29, 00:46:45, 00:51:29, 01:49:47
- Power Query Editor 00:13:02, 00:23:40, 00:27:57, 00:30:45, 00:46:17, 01:01:30, 01:06:50, 01:11:57
- Query 00:22:00, 00:46:49, 01:11:56
- Refresh 00:13:24, 00:59:29
- Row 00:14:26, 00:16:01, 00:24:59, 00:27:40
- Slicer 00:07:26, 00:54:04, 00:55:40, 01:18:20, 01:30:32
- Table 00:55:41, 00:59:28, 01:01:19, 01:31:44, 01:34:26
- Total Row 00:47:45
- Undo Command 01:16:33
- Workbook 00:03:46, 00:17:27
- Worksheets 00:20:25, 00:30:16
.PBIX: The file extension for Power BI files is “.pbix”. The .pbix files are highly compressed file types that contain all the graphics along with the actual data.
.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.
Accounting Receivable (AR): Accounts receivable, abbreviated as AR or A/R, are legally enforceable claims for payment held by a business for goods supplied or services rendered that customers have ordered but not paid for.
Analyze: The ANALYZE tab has several commands that will enable you to explore the data in the PivotTable.
Audit: A formal examination of an organization's or individual's accounts or financial situation
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.
Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.
Fill Down: Fill Down is a rather unique transform in how it operates. By selecting Fill Down on a particular column, a value will replace all Null values below it until another non-null appears. When another non-null value is present, that value will then fill down to all Null values.
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.
Microsoft 365: Microsoft 365, formerly Office 365, is a line of subscription services offered by Microsoft which adds to and includes the Microsoft Office product line.
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 BI: Power BI is an interactive data visualization software product developed by Microsoft with a primary focus on business intelligence. It is part of the Microsoft Power Platform
Power BI Chart: Power BI consists of various in-built data visualization components such as pie charts, maps, and bar charts. It contains complex models including funnels, gauge charts, waterfall, and many other components.
Power BI Desktop: Power BI Desktop is a free application you install on your local computer that lets you connect to, transform, and visualize your data. With Power BI Desktop, you can connect to multiple different sources of data, and combine them (often called modeling) into a data model.
Power BI Matrix: The Power BI equivalent of an Excel PivotTable, which in short is a visualization that summarizes data in an interactive fashion.
Power BI Service: The Power BI service is a cloud-based service, or software as a service (SaaS). It supports report editing and collaboration for teams and organizations. You can connect to data sources in the Power BI service, too, but modeling is limited.
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.
Refresh: The Refresh command appears on the Options tab of Excel 2007 and 2010 as well as the Analyze tab of Excel 2013. Pivot tables store a snapshot of the underlying source data, so they don’t immediately reflect changes to said data. You must periodically refresh any pivot table to ensure it reflects any changes to the source data.
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.
Slicer: You can insert slicers in Excel to quickly and easily filter pivot tables. Slicers were introduced in Excel 2010, and they make it easy to change multiple pivot tables with a single click
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.
Total Row: A Total row appears below the data where each column has access to several automatic formulas. The default selection for the Total Row is none, meaning no function is selected when you first turn on the Total Row on your Table.
Undo Command: The Undo feature in Excel 2010 can quickly correct mistakes that you make in a worksheet. The Redo button lets you “undo the Undo.” The Undo button appears next to the Save button on the Quick Access toolbar, and it changes in response to whatever action you just took; the Redo button becomes active whenever you use Undo.
Workbook: In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets, in a single file.
Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.
