fx Analysis & Visualization

Power Query Basics

90 min7 lessons21 quiz questions

Lessons

1. What Power Query is and when it beats formulas13 min 2. Import: CSV, folder (multiple files at once), web table13 min 3. Core transforms: remove columns, split, change type, replace, fill down13 min 4. Unpivot — turning a wide report into an analysable table13 min 5. Merge (joins) vs Append — the scalable version of VLOOKUP13 min 6. Query dependencies, staging queries, refresh settings13 min 7. Common gotchas: locale/date errors, hard-coded file paths, type steps12 min

Practice file

⬇ Download analysis-visualization-practice.zip

Analysis & Visualization practice bundle

Extract the ZIP before opening files. Start with pivot-practice.xlsx: its Sales sheet contains the original 16-row tblSales table for pivot and dynamic-array exercises. Grand total: ₹10,70,000. Keep an unchanged copy as your baseline.

capstone/data/ contains 24 monthly sales CSVs, totaling 50,000 synthetic orders from April 2024 through March 2026. Stores, Products and Targets CSVs sit outside that folder so a sales-only folder import does not combine incompatible schemas.

analysis-reference.xlsx contains a Wide Targets exercise and independently calculated reference summaries for checking your work. These are plain tables, not completed PivotTables or a Power Pivot model; build those in Excel as directed in the lessons. solutions-en.md and solutions-hi.md provide exercise answers.

calendar.csv contains daily dates for 2024–2026, FY labels and fiscal month numbers. Load it into the model and mark its Date column as the date table. answer-key.json and the optional standard-library audit_capstone.py let you reconcile the CSVs without Excel. README.md explains setup and filter scope.

Use a supported Windows desktop Excel edition with Power Pivot for the complete model exercises. The modern dynamic-array lessons specify their own Excel version requirements. The download contains no macros, credentials, or external connections.

Module quiz (21)

1. Where do you see the recorded cleaning steps?

Applied Steps pane

2. Does Power Query change the original file?

3. How do results update when source data changes?

Refresh / Refresh All

4. Which import combines 24 monthly files into one table?

From Folder → Combine & Transform

5. What does the Source.Name column contain?

The file each row came from

6. Text looks like junk symbols after import — what setting?

File Origin / encoding — choose UTF-8

7. Safer way to drop unwanted columns?

Remove Other Columns

8. Indian dd-mm-yyyy text dates — which option?

Change Type → Using Locale → English (India)

9. Fill Down does nothing — likely reason?

The blanks are empty text, not null

10. Which column do you select before "Unpivot Other Columns"?

The ones that should stay — e.g. Region

11. Why is "Unpivot Other Columns" better than "Unpivot Columns"?

New month columns are included automatically

12. Opposite of unpivot in Power Query?

Pivot Column

13. Merge adds columns or rows?

Columns

14. Join kind to find records with no match?

Left Anti

15. Row count went up after a merge — why?

Duplicate keys in the lookup table

16. Load option for staging queries?

Only Create Connection

17. Reference vs Duplicate — which follows changes in the original?

Reference

18. Where do you see a diagram of query flow?

View → Query Dependencies

19. Same query shows 4 May instead of 5 April on another PC — fix?

Change type using locale en-IN

20. Why can the auto "Changed Type" step break refreshes?

It names every column; renamed/missing columns fail

21. How do you avoid a hard-coded folder path?

Parameter from a named cell, or SharePoint folder