How to get data in Power BI Desktop from a personal SharePoint List (aka Microsoft Lists)

[Anonymous]

Even though there is a Power BI integration option in Microsoft Lists, its functionality is limited, and I often experience issues. Therefore, I was looking for a way to import the data from a List in Power BI Desktop. This is simple for Lists that are connected to a Collaboration Site on SharePoint as you just need the SharePoint Site Web Address.

However, Lists that are saved to "My Lists" in Microsoft Lists do not appear in any collaboration sites on SharePoint and it is a bit tricky to get data in Power BI Desktop from these personal Lists. However, it can be done and below is a step-by-step guide for how to do this: 

1. Go to the personal List in question and click the "List settings" in the top right corner

2. Mark the first part of the Web Address and copy it

3. Go to Power BI Desktop, click "Get Data", select "Other", select "SharePoint List", and click "Connect"

4. Paste the first part of the Web Address from step 2 and click "OK"

5. Select the title of the List in question and click either "Load" or "Transform Data"

Now you can create Power BI reports based on the data SharePoint List and this does not require a Data Gateway to enable scheduled data refresh in the Power BI Service. 

Hope this helps other people in the same situation as me!

[KonradZawel]

You can try using the SharePoint Folder connector with just the base site URL, then filter to your file in Power Query. If that feels clunky, I’ve seen people use tools like Skyvia to sync the data to a database first and then connect Power BI to that - makes things easier long term.

[Anonymous]

Hi @Anonymous ,

Thanks for your sharing !

Best Regards,

Neeko Tang