Page 1 of 21
Power Query
What is it? (It’s not PowerPoint anyway..)
Page 2 of 21
Power Query is a data connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs.
Features in Power Query are available in Excel and Power BI Desktop.
Source: Microsoft online training
Page 3 of 21
Power Query is a data connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs.
Features in Power Query are available in Excel
Source: Microsoft online training
Page 4 of 21
Power Query is a data connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs.
Features in Power Query are available in Excel
“information, especially facts or numbers, collected to be examined and considered and used to help decision-making, or information in an electronic form that can be stored and used by a computer”
Source: Cambridge Dictionary
Page 5 of 21
Power Query is a data connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs.
Features in Power Query are available in Excel
“information, especially facts or numbers, collected to be examined and considered and used to help decision-making, or information in an electronic form that can be stored and used by a computer”
Source: Cambridge Dictionary
Page 6 of 21
Power Query is a data connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs.
Features in Power Query are available in Excel
Another way of looking at this is it’s a bit like baking a cake.
Source: Me
Page 7 of 21
Power Query is a Ingredients connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs.
Features in Power Query are available in Excel
Another way of looking at this is it’s a bit like baking a cake. Your data are your ingredients, but you’d hardly eat flour by itself. You need to add more ingredients (data) to make something that tastes good. So, let’s do that with some data…
Source: Me
Page 8 of 21
Data Sources
“Easily discover, connect and combine data from multiple sources, then shape and refine it to meet your needs”
Source: Excel ribbon
Page 9 of 21
Data Sources
Page 10 of 21
Data Sources
Infohub
Page 11 of 21
Problems & Solutions
using Power Query in excel
Page 12 of 21
Problem
I need to track student registration progress and make sure they are registered to the correct modules in the right stage
Page 13 of 21
Solution
I need to track student registration progress and make sure they are registered to the correct modules in the right stage
Use Power Query to find and merge the required files into a single table
Page 14 of 21
Solution
I need to track student registration progress and make sure they are registered to the correct modules in the right stage
Use Power Query to find and merge the required files into a single table
To remove data from your report, delete the source data, not the report-you can use it again when required
Page 15 of 21
Solution
I need to track student registration progress and make sure they are registered to the correct modules in the right stage
Use Power Query to find and merge the required files into a single table
To remove data from your report, delete the source data, not the report-you can use it again when required
To repeat, download updated source data from Infohub, save it in the same place you did before, open your Power Query file and refresh data.
Page 16 of 21
Problem
I want to see if students have been registered to the correct modules
Page 17 of 21
Solution
I want to see if students have been registered to the correct modules
Use Power Query to find and merge the required files into a single table
Page 18 of 21
Problem
I want to see if students have been registered to the correct modules
Use Power Query to find and merge the required files into a single table
Hang on, that’s the same thing I did before and now I have two tables when I just want one
Page 19 of 21
Problems
I want to see if students have confirmed attendance and whether or not they have been progressed to the next stage
Use Power Query to find and merge the 13 files into a single table
Hang on, that’s the same thing I did before and now I have two tables when I just want one
I need to know what stage of the programme each module is
My partnership colleagues in BJUT will want to know the Chinese name, year of entry and BJUT student number
BDIC1034J has 2.5 credits on the programme structure but the module registration list shows it as 3
Page 20 of 21
Solutions, using Power Query
Back to the baking analogy, here’s one I made earlier. However, I only need to find and mix the ingredients for this cake once. When I want another cake, I just add a fresh set of ingredients, hit refresh and Power Query does the rest.
Page 21 of 21
Power Query
Can be used with data from a multiplicity of sources both internal and external to UCD
Enables you to create connections to data that are repeatable-you set up your information once and just renew the source data when required
You don’t have to learn programming languages or formulas
But if you already know formulas, coding etc, then combined with Power Query they become even more powerful
If you really want to torture yourself with yet another programming language, Power Query has this too (M)
If you want even more, there’s Power Pivot, Power Bi, DAX….