In this recording of a live webinar I ran in late January 2023 you will learn how to import multiple files in a single Power Query. If you use data at all then Power Query is an essential skill to possess.
Use the buttons below the video to download the materials.
Building on the skills covered in the Introduction session, we will start working with multiple files. For example you may have 12 separate CSV files in a folder. All with the same layout, one for each month of the year. Power Query can import all 12 files as if they were a single file and create a table for the whole year
This session covers • importing multiple CSV files from a folder • a technique to capture the file name in a field (column) • importing multiple Excel files • merging data from multiple tables • Extracting header information into a column from multiple CSV files
As always, I shared other tips and tricks along the way.
A while ago I posted about creating an ordinal (1st, 2nd etc) for a date. With the inclusion of the LAMBDA function I thought I would create a custom function to simplify the process.
In this recording of a live session ran in late January 2023 you will learn how to automate data importation in Excel with Power Query. If you use data at all then Power Query is an essential skill to possess.
Use the buttons below the video to download the materials.
Power Query allows you to automatically perform data cleansing routines on your data sources – no manual intervention required. Simply refresh and your data is ready to use. You can use csv files; txt files; databases and existing Excel tables as your data sources.
Learn the basics, plus an advanced technique to automate data cleansing routines on your data sources.
This session covers
fixing dates so that Excel can recognise them
formatting columns as text – retaining leading zeroes in CSV files
deleting unwanted rows and columns from your data
removing leading and trailing spaces
populating blank values with zeroes
populating blanks with entries from above
correcting trailing minus signs
unpivot a report – converting a report layout into a table layout
converting a MYOB report into a data table
capture header information in a column
As always, I shared other tips and tricks along the way.
As we get used to the new year we may want to perform some calculations based on the old year. A recent inquiry requested a formula that could calculate the percentage of a year that an employee had been employed. He suggested using an IF function. See the solution below, but it doesn’t involve the IF function.
In this session you will learn all about Excel’s formatted tables. Using Formatted Tables is an essential skill in Excel. Use the buttons below the video to download the materials and completed file.
Many of Excel’s features and functions work seamlessly with formatted tables. They can help you improve the structure and reliability of your spreadsheet files.
Formatted tables can allow you to create powerful reports like those in a relational databases.
Topics covered
advantages and limitations of formatted tables
keyboard shortcuts
using formatted tables with formulas
solutions to some of the limitations of formatted tables
A client recently requested a formula to round to the nearest 9 cents. This avoids getting to a price point. This is a common requirement in retail businesses. The solution was simpler than I thought it would be.
“Every action you take is a vote for the type of person you wish to become. No single instance will transform your beliefs, but as the votes build up, so does the evidence of your new identity.”
Calculating age with years months and days takes several functions to create. Whenever you need multiple functions to calculate an entry you may have a good use case for a custom function.
“Just start. Start slow if you have to. Start small if you have to. Start privately if you have to. Just start.”
James Clear
Open Power Query Shortcut
Woohoo ! A new shortcut to open the Power Query window.
Works in the latest subscription version of Excel.
Excel has a function to find the last day of the month. To find the last weekday of the month you can combine a couple of functions. Here is a custom function that also works.
