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:
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 😉
is it possible to do the same mechanism but with lists from different subsites?