Data Loading Tricks in Power Query

Data Loading Tricks in Power Query

The video presents five ways to make Power Query data loading and model maintenance more flexible:

1. Filter a folder to its newest file After connecting to a folder with Folder.Files, filter the Date created column to keep the latest file. The filter updates dynamically as files are added. “Latest” refers to the most recently created file, not necessarily the file with the largest or newest-looking name. Other relative date filters, such as recent months or years, are also available.

2. Use a parameter for folder paths Create a text parameter containing the folder path, then reference it in the source step of each relevant query. If the folder moves, update the path once in the parameter instead of editing every query.

3. Use CSV files to improve load speed The presenter says CSV files can load into Power Query considerably faster than Excel files, and recommends converting Excel files to CSV when practical. A cited blog post compares the two formats; a comment on the post mentions Power Automate as a possible way to automate conversion.

4. Create helper tables without maintaining a separate workbook Helper tables can be created with DAX or entered directly in Power Query or Power BI using Enter Data. For the latter method, copy and paste values from Excel, then edit them later through the query’s source settings.

5. Build a date from a filename For filenames containing a year, use a custom column to remove the .xlsx extension, convert the remaining text to a number, and pass it to #date with the desired month and day. The example creates January 1 of the year in each filename.

Guides and References

• A related video on creating a dynamic path in Excel
• A video about DAX’s DATATABLE function for creating tables
• A blog post by Chris Webb comparing CSV and Excel loading speed
• A comment by Eric suggesting a Power Automate connector for converting Excel files to CSV

Main speaker: Chandeep Goodly Other sources cited: Chris Webb and Eric