Learn Power Query and Refreshable Excel Dashboards
Profile before transforming
Inspect column names, types, nulls, duplicates, date formats, and likely keys before loading data. Keep a record of source locations and expected file structure. If an input changes shape, the refresh should produce a visible exception rather than silently misclassifying values.
Create a transparent query
Use explicit type conversions, trim and normalize values where justified, and separate repeatable cleanup from manual corrections. Merge or append sources only when their keys and meanings align. Preserve a route back to the original source so a reviewer can explain how a reported value was produced.
Validate the refresh and communicate clearly
After refresh, compare row counts, totals, date ranges, and unmatched keys against independent controls. Build PivotTables and charts around questions the data can actually answer. Label units, time periods, and filters; provide a clear note for excluded records or incomplete source periods.
Practice task
Combine three synthetic monthly files, normalize category labels, and create a dashboard. Test one missing file, an unexpected column, and a duplicate key, then document how each issue appears to the user.