Power Query for Microsoft Lists
People say Power Query is such a useful tool for anyone to have under their belt even if their job title does not read “data” or “analyst” — mine doesn’t, either. What they say about Power Query is indeed true, especially if you want to equip yourself with quantitative literacy and be able to unearth new knowledge in a time-saving manner or do a data-backed verification of whatever claim that has been asserted upon you. It needs to be made clear, however, that the scope of Power Query is to handle the early stages of what I call the “self-service analytics lifecycle,” illustrated below:
As the abovementioned cake analogy goes, Power Query is all about acquiring and preparing the subject of quantitative analysis, not the analysis itself. More importantly, handling Power Query requires critical thinking and sound judgement particularly in terms of:
- What is currently lacking, unknown, or uncertain?
- What do you want to prioritise and get to the bottom of — and where is it?
- What manual labour do you need to eliminate in the process?
…which collectively determine the usefulness, validity, sustainability, and robustness of the outcome. My further thoughts on the topic of critical thinking in the face of AI, by the way, can be found in a previous article.
Recipe for discernment
The coverage of Power Query is vast, and different people use it in different ways to achieve the results they are after. One low-hanging fruit that I observe in many white-collar professions, however, is the task of analysing content stored in Microsoft Lists, also commonly known as SharePoint lists and libraries. Wherever modern Excel is used for business, Microsoft Lists are also in use, albeit at varying levels of user adoption from team to team.
While general use of Power Query almost always entails ad-hoc elements of problem-solving, using it for Microsoft Lists is usually straightforward and free of unforeseen challenges, to a point where a ‘recipe’ can be formulated. This article is that recipe, which is transferrable across Excel and Power BI where Power Query is commonly built in.
You can use any of your existing Microsoft Lists that you wish to prepare for analysis. The example list used here was created with a “Work progress tracker” template for quick illustration. I added one lookup column and one managed metadata column to the list to cover all bases in terms of data types. I then populated the list with a few dummy content items.
Getting started
To start Power Query in Excel, select the Data tab > Get Data > From Online Services > From SharePoint Online List.
To start Power Query in Power BI Desktop, select Get Data from the ribbon and do a quick search for “SharePoint”. Select SharePoint Online List.
Enter the URL of the site where the source list is located. Select the 2.0 implementation, which will give user-friendly column (field) display names. Click OK.
You may be asked to authenticate into the site. If so, select Microsoft account and sign in with the Microsoft account that has access to the source list.
In the Navigator dialogue, select the list(s) to bring into Power Query. Then, click Transform Data.
Power Query opens in its own window. The most useful step to take initially is to choose only the columns that are needed for analysis, and nothing else. Click Choose Columns from the menu and check only the columns you recognise and need. It is also a good idea to include the built-in ID column irrespective of the purpose and nature of the originating list. Click OK.
When done, pay attention to the columns of the following types: person, lookup, managed metadata, and choice. In most cases, these columns initially contain either “List” or “Record” instead of actual values that should have come from the source. They require further treatment in Power Query.
Lookup columns
Click on the expansion icon in the column heading. Select Expand to New Rows. Then, select lookupId and lookupValue. Click OK to complete the transformation. Confirm that the original column has now turned into two new columns, one containing numeric IDs and the other display text values. You can use either or both in your analysis.
Person columns (including Created By and Modified By)
Click on the expansion icon in the column heading. Select Expand to New Rows. Then, select title and email. Click OK to complete the transformation. Confirm that the original column has now turned into two new columns, one containing people’s display names and the other their email addresses. You can use either or both in your analysis.
Managed metadata columns
Click on the expansion icon in the column heading. Select Label and click OK. Verify that the column now shows the actual values seen in the originating list.
Choice columns
If a choice column accepts single values only, that is, rendered as a drop-down or radio buttons in the source list, the values are recognised and shown correctly and there is nothing further to do. If, however, the column is configured to accommodate multiple selections, i.e., rendered as a series of checkboxes in the source list, then it is a different story. Refer to the Beware of multiple-selection columns section below.
Ready for visualisation and analysis?
If no further transformation is required, click Close and Apply. The Power Query window closes, and you are back in Excel or Power BI Desktop with the transformed data successfully brought in.
The data is now fit for the next steps in the self-service analytics lifecycle. The job of Power Query is considered done at this point, even though there are many other transformation operations that can be performed additionally back in Power Query in order to get the data in optimal shape.
Beware of multiple-selection columns
One important caveat is with the treatment of columns that allow for multiple selections. For instance, a choice column or a managed metadata column may be configured to accommodate multiple selections in the settings of the originating list. When single selection is enforced through and through, things are straightforward. But if at least one column in the list does multiple selections, a different approach is required, as described below.
Before performing the Choose Columns step, duplicate the query so that the same originating list now manifests as two separate queries. Name the second query appropriately.
Then, perform Choose Columns on each of the two queries as follows:
- Original query: Choose the built-in ID column and all other necessary columns except any columns that take multiple selections.
- Second query: Choose the built-in ID column, plus ONLY the columns that take multiple selections.
When done, perform the usual expansion operations across all applicable columns in both queries.
Note that the number of rows in the second query may now differ from that of the original query if some of the list items actually had multiple associated selections. For instance, a single list item may be associated with two or more choices in the “Category” column if the column is designed for multiple selections. Each row in the second query represents a unique association; as such, an appropriate relationship between the original table and the second table will need to be established later in the data model. If the number of multiple-selection columns in the originating list is more than one, it may be advisable to create a third query, a fourth query, and so on, corresponding to each of those columns.
Bonus tip: make your BI project portable
Often, a Microsoft List is duplicated across multiple sites with the exact same column definitions and list title. For example, there may be three instances of the same list deployed to three different sites: Development, Test, and Production, each containing its own data.
By using a parameter in Power Query and making simple changes to some of the query steps, switching between the different instances to analyse different sets of data is made easy. (Note that this is available only in Power Query launched from Power BI Desktop.)
First, get back into Power Query by selecting Transform data in the Power BI Desktop menu. From the Power Query menu, open the Manage Parameters dialogue. Create a parameter of Text type and name it appropriately. Set Suggested Values to List of values. Then, enter each possible choice or URL in the desired order. Select the default and current values and click OK to save the parameter.
Next, with the focus set on the original query, head over to the Applied Steps pane on the right-hand side and select the first item named Source.
In the step’s formula, replace the hard-coded site URL with the name of the newly created parameter. Ensure that this parameter is recognised in the IntelliSense-like help that pops up.
Next, select the Navigation step immediately below Source. In its formula, replace the list ID property with the Title property and its value, which persists across the different sites.
Repeat the above Source and Navigation step changes if there are other queries present. When done, click Close and Apply to exit Power Query.
There are no changes that are immediately visible. To see the effect of the parameterisation, click the down-arrow in the Transform data menu item and select Edit parameters. Note that this is done in Power BI Desktop, not Power Query.
To switch to a different instance of the source list, select the site URL you want in the dropdown, and click OK. You may be asked to refresh the data after the switch-over.
Not the end
Even when you are done with Power Query, further work on data modelling is probably required before you embark on visualisation and analysis, especially when two or more source lists were processed as multiple queries in Power Query. Data modelling may involve establishing relationships between tables, defining measures and calculated columns, applying specific formatting to individual columns, or creating an auxiliary calendar table to facilitate time-intelligence analysis. Such data modelling is typically outside the scope of Power Query, although certain operations can be done either upstream (in Power Query) or downstream (in Excel or Power BI Desktop, post-Power Query) with identical results. Experience and critical thinking will help determine the best course of action on a case-by-case basis.
