16. Practice Exercises with Answer Hints
16.9 Module 10: Power Query
- Load
tblOrdersinto Power Query, trim and capitalise City, change Amount to Decimal and load back. Hint: Data › From Table/Range; Transform › Format › Trim / Capitalize Each Word. - Append monthly files January–March from a folder. Hint: Data › Get Data › From File › From Folder › Combine & Transform.
- Merge Orders with Stores to bring in the City Manager. Hint: Home › Merge Queries › Left Outer on Store ID › expand City Manager.
- Unpivot a sheet with months as columns into Month and Sales rows. Hint: select City › Transform › Unpivot Other Columns.
- Load dd-mm-yyyy text dates correctly. Hint: column menu › Change Type › Using Locale… › Date, English (India).
Ravindra Bagale's Tip
Many students don't notice when Power Query's type change (Changed Type) goes wrong – dd-mm-yyyy dates are read US-style and 03-04 becomes 4 March. For dates, always use Using Locale › English (India), and check the Applied Steps once from top to bottom.
Ravindra Bagale's Tip – मराठी
Power Query मध्ये type change (Changed Type) चुकीचा होतो ते बरेच students बघत नाहीत – dd-mm-yyyy तारखा US style समजून 03-04 चा 4 March होतो. तारखांसाठी नेहमी Using Locale › English (India) वापरा आणि Applied Steps एकदा वरून खाली तपासा.
Ravindra Bagale's Tip – हिंदी
Power Query में type change (Changed Type) गलत हो जाए तो बहुत से students ध्यान नहीं देते – dd-mm-yyyy तारीखें US style में पढ़ी जाती हैं और 03-04 बन जाता है 4 March. तारीखों के लिए हमेशा Using Locale › English (India) इस्तेमाल करो और Applied Steps एक बार ऊपर से नीचे तक check करो.