10.9 Refresh and Load Options
Home › Close & Load ▾ › Close & Load To… gives the choices:
| Option | What happens | When to use |
|---|---|---|
| Table | Loads to a worksheet as an Excel Table | You need to see/use the rows |
| PivotTable Report / PivotChart | Loads straight into a pivot | Summary only |
| Only Create Connection | Query stored, nothing on a sheet | Staging queries used by Append/Merge |
| Add this data to the Data Model | Loads to Power Pivot's in-memory model | Large data (beyond 1,048,576 rows), relationships, Distinct Count, DAX |
Steps in Excel – refresh
- Data › Queries & Connections › Refresh All (Ctrl + Alt + F5), or right-click a query in the Queries & Connections pane › Refresh.
- Query Properties (right-click query › Properties…): Refresh every n minutes, Refresh data when opening the file, Enable background refresh.
- Change load destination later: right-click the query › Load To….
- Data › Get Data › Query Options – global settings such as privacy levels and default load.
Ravindra Bagale's Tip
Staging queries (Blinkit, Amazon Now) pan Table mhanun load kele ki workbook jad hota aani sheets bharatat – khup students ase 5-6 sheets banvtat. Madhlya queries Only Create Connection theva, fakt final query load kara. Ani 10 lakh+ rows asle tar Data Model madhe load kara, sheet var nahi.
Practice task
Set Blinkit_Orders and AmazonNow_Orders to connection only, load All_Orders to the Data Model and build a PivotTable from it. Turn on Refresh data when opening the file.
Thodkyaat sangaycha tar (quick recap)
Power Query mhanje cleaning steps record karun Refresh ne punha chalvne: Excel/CSV/Folder madhun data (dates la locale English (India)), bank statements folder madhun combine, Append = khali jodne (column nava same), Merge = join (key duplicate nako, Left Anti ne missing shodha), Unpivot Other Columns, Group By, path sathi parameters, aani staging queries connection-only. Aata pudhe jaauya – what-if analysis.