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
Simon Hurst: Simon is a chartered accountant who has been involved in computer software for the accountancy profession over the past 30 years. He is the proprietor of the Knowledge Base, an organisation which concentrates on providing onsite IT training and consultancy. Simon is a member of the Excel Community Advisory Committee. He writes about Microsoft Office and related subjects for several websites.
This webinar is exclusively available to members of the Excel Community, Data Analytics Community and Faculties Online.
The webinar is brought to you by the Excel Community. Membership gives you access to exclusive premium content including our webinars, eight suites of Excel online training, regular eBulletins and extensive online resources focused using Excel more efficiently and effectively. For more information about Excel Community membership and our latest joining offers, please visit icaew.com/joinexcel.
Should you experience webinar difficulties, please click here
This screen shows you the details for the selected product.
Click the "Add to Cart" button to add this product to your shopping cart. You can enter a quantity larger then 1 to add multiples of this product to your shopping cart.
If the product is full you will see a "Wait List" button. Click this button if you would like to be notified if/when capacity is added. If capacity is increased we will email you. Upon receipt of the increased capacity notification, registration will be on a first-come, first-served basis.
If the product is not ready for purchase you will see a "Notify Me" button. You can click this button if you would like to be notified when this product is ready for purchase.
Some products can only be purchased through our partner. In this case you will see an "External Register" button. Click this button to purchase through our partner's website.