Download the sample Power Query model
CMBI developed this World Cup Power Query model using Excel 2016. If you are using Excel 2010 or 2013 you will need the Power Query Add In which is FREE from Microsoft.
- Click here to download World Cup 2018 Australia Match Schedule Power Query Sample Workbook
Introduction to World Cup 2018 Power Query Model
The World Cup 2018 Australian Match Schedule Power Query model demonstrates some key features of the Power Query user interface and functions. In this example we take some semi-structured match schedule data for US Eastern Time and tidy it and convert it to Australian AEST. While we're at it, we merge some team ranking and work out the favourite for each of the Group Stage matches.
Inspect the Power Queries
You can view the Power Queries by selecting the Show Queries (and Connections) from the Data tab on the Excel ribbon (2016/Microsoft 365) or on the Power Query tab (Excel 2010/2013). The workbook demonstrates a number of Power Query functions including
- Tranforming and cleansing data
- Extracting information from Date fields
- Merging data from two sources
- Adding custom columns
Finding the Power Queries
Viewing the Power Queries (right click ...Edit)
Power Query Sample How-to Video Part 1
The video below shows you how we created the Power Query transformation step by step using the Power Query UI. No coding required!
NOTE: YOU CAN MAXIMISE THE VIDEO USING THE BOTTOM RIGHT ICON FOR BETTER RESOLUTION
Power Query Sample How-to Video Part 2
The video below shows you how we merged the World Cup schedule data with the Team Ranking data
NOTE: YOU CAN MAXIMISE THE VIDEO USING THE BOTTOM RIGHT ICON FOR BETTER RESOLUTION
Power Query functions used in the sample
Power Query functions used in the sample include
- Excel.CurrentWorkbook
- Table.TransformColumnTypes
- Table.DuplicateColumn
- Table.ReplaceErrorValues
- Table.FillDown
- Table.SelectRows
- Table.ReplaceValue
- Table.AddColumn
- Table.RenameColumns
- Table.ReorderColumns
- Table.RemoveColumns
- Table.Sort
- Table.TransformColumns
- Table.NestedJoin
- Table.ExpandTableColumn
Inspecting the steps in the Power Query
View Power Query dependencies
Power Queries can reference each other. The Power Query UI provides a way to visualise the chain of dependencies
Power Query dependency view
Want to learn more about Power Pivot?
CMBI provides onsite training for all levels of Power Pivot users and developers.
See our Training page to learn more about CMBI's training and mentoring programs.
About CMBI
CMBI designs and develops business intelligence and analytics solutions for clients in Sydney and regional NSW. CMBI technology expertise includes Power BI, Power Pivot, Excel, SQL Server, Analysis Services, SharePoint BI, and Tableau. Contact us today to see how we can help you make your BI goals a reality.
