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
If staging queries (Blinkit, Amazon Now) are also loaded as Tables, the workbook gets heavy and fills up with sheets – many students end up with 5–6 such sheets. Keep the in-between queries as Only Create Connection, and load only the final query. And for 10 lakh+ rows, load into the Data Model, not onto a sheet.
Ravindra Bagale's Tip – मराठी
Staging queries (Blinkit, Amazon Now) पण Table म्हणून load केल्या की workbook जड होतं आणि sheets भरतात – बरेच students अशा 5-6 sheets बनवतात. मधल्या queries Only Create Connection ठेवा, फक्त final query load करा. आणि 10 लाख+ rows असतील तर Data Model मध्ये load करा, sheet वर नाही.
Ravindra Bagale's Tip – हिंदी
Staging queries (Blinkit, Amazon Now) को भी Table के रूप में load किया तो workbook भारी हो जाता है और sheets भर जाती हैं – बहुत से students ऐसी 5-6 sheets बना लेते हैं. बीच वाली queries को Only Create Connection रखो, सिर्फ़ final query load करो. और 10 लाख+ rows हों तो Data Model में load करो, sheet पर नहीं.
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.