Government Accounting Technology Conference - Virtual-Presentation - Connect, Transform, and Analyze Data Like a Pro with Power Query

Presentation - Connect, Transform, and Analyze Data Like a Pro with Power Query

Pdf Summary

This presentation introduces how to connect, transform, and analyze data in Excel using Power Query, Power Pivot, Tables, and Power BI. It explains that modern Excel reporting works best when data is stored in tables, then brought into Power Query for cleaning, reshaping, and refreshing rather than manually copying and pasting data. A major theme is “write once, refresh repeatedly,” showing how Power Query can automate data retrieval from files, folders, databases, the web, ODBC connections, APIs, and cloud services.<br /><br />The presentation covers the Power Query interface, including the ribbon, query pane, preview area, query settings, and applied steps, and demonstrates common transformations such as filtering, removing rows and columns, splitting and parsing text, pivoting and unpivoting, merging and appending queries, and creating new columns with custom formulas or conditional logic. It also explains how to close and load data back into Excel as a table, connection only, or into the data model.<br /><br />Other topics include Excel tables, data modeling, Power Pivot, and PivotTables built from the data model, which support larger and more flexible analysis than conventional PivotTables. Power BI is also introduced as a dashboard and reporting platform for combining data from multiple sources and creating real-time visual reports.<br /><br />Advanced topics include the Power Query Advanced Editor, M language, parameters, custom functions, custom data types, and AI Insights. The presentation emphasizes practical business use cases, especially for accounting and finance professionals, and includes guidance on connecting to sample SQL Server, ODBC, web, and REST API data sources for hands-on learning.

Keywords

Power Query

Excel Tables

Power Pivot

Power BI

Data Modeling

PivotTables

M Language

Data Transformation

SQL Server

REST API