Ravindra BagaleCourses & study guides

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.

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.