You want to collect information from your sales representatives on their forecasts of product sales for the coming year, by customer, by month. You end up with a workbook for each salesperson, each one containing a sheet for each product showing details of customer sales by month. Your task is to consolidate all that information in order to produce a forecast of total sales for the year.
We will use this example as a general introduction to the ways in which Power Query can be used to solve a wide range of Excel problems. The introduction will cover a guide to using the Power Query editor and a range of key techniques and features, all of which can be used to solve practical problems and automate time-consuming processes. We will also see how Power BI can work with Power Query to create powerful visualisations of the data strewn throughout Excel workbooks.
Amongst the techniques we will cover are:
- Dealing with monthly columns
- Consolidating rows from multiple Excel ranges, spread across multiple worksheets
- Transforming formatted reports to structured tables
- Transforming structured tables to formatted reports
- Linking Excel to Power BI visualisations
First broadcast on 17 June 2022