Introduction To Power BI and Excel-Based Alternatives

On Demand Webinar

Webinar Details $219

  • 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

All Access Membership

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.

Who should attend:
Professionals who would like to learn how to create interactive reporting tools in Power BI and/or Microsoft Excel. 

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


  1. Introduction
  2. Excel Versions 00:01:13
  3. Excel vs. Power Query vs. Power BI 00:02:01
  4. Free Power BI vs. Paid Power BI 00:03:31
  5. A Typical Power BI Workflow 00:04:52
  6. Summary Reports May Not Suffice 00:08:40
  7. Detail Reports Provide More Options 00:09:42
  8. Report Clean-Up Automation Overview 00:10:48
  9. Clean A/R Aging with Power Query 00:13:49
  10. Clean A/R Aging with Power Query - Steps 1 - 8 00:17:36
  11. Clean A/R Aging with Power Query - Steps 9 - 18 00:22:06
  12. Clean A/R Aging with Power Query - Steps 19-24 00:30:01
  13. Clean A/R Aging with Power Query - Steps 28- 36 00:36:55
  14. Clean A/R Aging with Power Query - Steps 37 - 42 00:42:25
  15. Create PivotTable from Power Query Data 00:48:28
  16. PivotTable Slicers 00:53:50
  17. Overwriting a Power Query Data Source 00:56:59
  18. Connecting Power BI to an Excel Workbook 01:00:20
  19. Transform the A/R Report 01:06:44
  20. Exploring the Power BI Interface 01:12:39
  21. Creating a Power BI Matrix 01:13:08
  22. Creating a Power BI Slicer 01:18:19
  23. Formatting the Slicer - Steps 31 - 36 01:20:08
  24. Formatting the Slicer - Steps 37 - 42 01:23:29
  25. Creating a Power BI Chart 01:24:47
  26. Completed Matrix/Chart/Slicer 01:30:43
  27. Adding a Table to Power BI 01:31:41
  28. Formatting Power BI Data 01:37:22
  29. Q&A Visual in Power BI 01:43:17
  30. Interacting with a Power BI Report 01:47:53
  31. Overwriting a Power BI Data Source 01:49:39
  32. Mobile Layout/Sharing Power BI Reports 01:51:23
  33. 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.

ATAOP Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in operations.

ATAAA Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in administrative.

CPE Credit

Continuing Professional Education

Aurora Training Advantage is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.

For more information regarding administrative policies such as complaint and refund, and cancellation please contact our offices at 407-542-4317 or training@auroratrainingadvantage.com.

You must answer all questions during the webinar, view the recording completely and pass the test at the end with 70% correct answers to receive CPE credit.

Guest Speaker

  • David H. Ringstrom, CPA

    David H. Ringstrom, CPA

    David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. David’s mantra is “Either you work Excel, or it works you,” so he focuses on what he sees use [...]

    View Full Profile

Webinar Survey Overall Rating

This webinar received a total of 0 survey responses. Attendees have given an average rating of 0 stars out of a possible 5, reflecting the quality and value of the content presented.

Average rating

0.0 / 5
Webinar Presentation
How many of the objectives of the event were met?
0.0 Stars
How useful was the information presented at this event?
0.0 Stars
Overall, how satisfied were you with this event?
0.0 Stars
Speaker Performance
Overall, how satisfied were you with this presenter?
0.0 Stars
How closely did the presenter follow the schedule?
0.0 Stars