Import data from multiple SharePoint lists at once in Power BI and Power Query

This is a quick walkthrough on how you can easily import multiple SharePoint lists at once, just like the import from folder method.

Start as usual

You start your import like this:

Pass the URL to the folder where your lists are located:

In the next step you would normally choose all the multiple SharePoint lists you want to import:

But that would create one query per file. You would then have to apply the transformations on each of them and append them to retrieve one consolidated table.

Then use a trick

To make this procedure much easier, you just select ONE (sample) file instead. This will create a query with 3 steps automatically generated:

The trick is to delete the last 2 steps, so that just the Source-step remains:

This will generate a convenient selection-table that you can simply filter for the desired tables. In my case, I apply a text-filter that will select all lists whose names start with “Import”. That way my upcoming desired lists will automatically be included as well:

 

Then click on the 2 arrows to expand the list columns and select the columns you want:

Result

That way you will maintain the list/file-name with your data and have a dynamic table that will be future-proof as well (as long as you’ve applied the correct filter syntax).

Edit 12th June 2018: Thanks to Miguel Escobar for pointing out that there is actually an easier method to do this: https://www.poweredsolutions.co/2018/06/11/navigation-window-folder-in-power-query-and-power-bi/ (in Power Query in Excel, you can achieve the same with just a direct click on the file name, but in PBI, you have to right-click your mouse … these are the things one has to know 😉 )

Enjoy & stay queryious 😉