Ravindra BagaleCourses & study guides मराठी Track your progress

29. Practice Exercises and Final Assignment

29.5 Modules 9–10: Folder Combine and Parameters

By Last updated:

  1. Combine three monthly bank statements (Jan, Feb, Mar) from a folder. Clean title rows, Opening/Closing Balance lines and amounts with commas in Transform Sample File.
  2. Filter out ~$ temporary and hidden files.
  3. Derive Month and Month No from Source.Name.
  4. Make Mar.xlsx use Withdrawal Amt./Deposit Amt. and an extra Value Date column. Fix it with MissingField.Ignore and MissingField.UseNull.
  5. Convert a single Amount column with Dr/Cr into Debit, Credit and Net Amount.
  6. Add Apr.xlsx and refresh. What would you need for scheduled refresh in the Service? What changes with a SharePoint folder?
  7. Create FolderPath, ServerName (list) and StartDate parameters and use them in M.
  8. Create a list query of cities and use it as Suggested Values for a SelectedCity parameter.
  9. Export a .pbit template and open it to test the parameter prompts.
  10. Create RangeStart/RangeEnd and describe the incremental refresh policy you would set for 3 years of orders.

Practice files (fictional): folder_orders/Orders_2026_04.csv, folder_orders/Orders_2026_05.csv

Hints
  • Test with a third file and a hidden temp file.
  • Month from Source.Name: Text.Middle or split by _.
Solutions (checked on the practice files)
  1. Practice equivalent: the two order files combine to 8 rows, ₹2,100 (April 3 rows ₹900, May 5 rows ₹1,200).
  2. Filter Extension = .csv and Attributes[Hidden] = false before Combine.
  3. From Orders_2026_04.csv: Month No 4, Month April (FY 2026-27 month 1).
  4. Use MissingField.Ignore / UseNull in Table.SelectColumns / Table.RenameColumns so a missing column does not break the query.
  5. Debit = if [Dr/Cr] = "Dr" then [Amount] else 0; Credit similarly; Net = Credit − Debit.
  6. Refresh picks up the new file. In the Service a local folder needs a gateway; a SharePoint folder needs no gateway.
  7. Use parameters in Source (FolderPath & file), server name and a date filter.
  8. Create a list query of cities and choose it as Suggested Values (Query) for SelectedCity.
  9. File › Export › Power BI template; opening the .pbit prompts for parameter values.
  10. Example policy: store 3 years, refresh last 10 days, on Order Date using RangeStart/RangeEnd (Module 10.8).

Ravindra Bagale's Tip

Friends, don't make a mistake here: a common mistake in the folder exercise is testing with only one or two files. Add a third file with a slightly different layout and a hidden temporary file, and make sure your query still works. This matters for both exams and interviews.