The ULTIMATE GUIDE to getting data from SharePoint List / Folder / Multiple files to Power BI

[@dhruvpunetha1108]

Hello sir i had a doubt at 9:29 you are selecting only one sheet what if there are multiple sheets in each excel file present in the folder can we automate it as well?

[@leonardforny1770]

Thanks!! Always useful videos.

[@PhillipLearnTeach]

Thanks for this. When getting data from Sharepoint Online list. I pull in the data but the tables created have several columns with [Table] in them. I need the data inside these columns as well. How can I get them the best and easiest way? As the data is constantly changing for the report being build. Thanks

[@SolutionsAbroad]

Hi there, as long as you import the correct columns the first-time around, your data will update automatically when you refresh your report. Not sure if that helps, let me know

[@lifeuncut1781]

Thanks for sharing these kind of best practices. Just one request though, can you please include the full header and footer in your videos? Both are cropped so folks might miss some important clicks.

[@SolutionsAbroad]

Thanks for the feedback, did you mean the zoom in? I do it to zoom into the clicks I do since the editing is so basic, but definitely will take this feedback into account for my next videos!

[@oludamolaaromofisomo5553]

Hi Thanks for this. Can I automate sending the reports to my email address monthly?

[@SolutionsAbroad]

Hi, yes of course! You can use the Subscribe feature or Power Automate to power thisL kzbin.info/www/bejne/d6PSoJ-EfcqMbtE

[@gauthierbouckaert6620]

Hi thanks for the great video! In addition to this, I want to retrieve the metadata from my sharepoint folder but this only gives me the rootfolders. For example I have a library “products”, within this library I have some folders for example bikes, cars… and within the folder bikes, there are subfolders wherein I have my product files. Now I want to retrieve the metadata of list wherein my productfiles are stored but when connecting to sharepoint with online lists, I get only to see my libraries and the name of the folders in the library but can’t accomplish to go on a further level. Do you have any thoughts on this?

[@SolutionsAbroad]

Hi, when you connect to SharePoint, it will typically give you every single file in that site, regardless where it is. You will find the "Path" column you can use to filter, if you filter to only show any paths within your root folder, it should give you everything under your library, is this what you're after?

[@MohsinAli-bk7hy]

Hello - I have 50+ (and continually growing) SharePoint sites with the same set of SharePoint lists with data on each individual site which I want to consolidate and visualise using PowerBI. 1. What is the most efficient (and best practice) method of consolidating the data into one dashboard? 2. Will I need to update the consolidated dashboard manually to include new sites are they are provisioned through the year? Thanks in advance!

[@einyv]

How can you call to a specific SharePoint list view? I only see the defaults. If I don't use power query and have a different type of connection set in excel I can then pull the SharePoint list view. Otherwise power Q seems to only pull back the default view.

[@VidharthiRanjan]

Hello thanks for sharing this kind of concept in power bi, just one question here, In my case I am importing the excel data from google Drive 2 different folder and creating a report inside power bi desktop and published it on power bi cloud my workspace also setup the personal getaway but when I refresh data there then it is showing message like "invalid data source"

[@VidharthiRanjan]

Can you help, how can I refresh google Drive folder data on power bi cloud ?because it work fine on power bi desktop

[@SolutionsAbroad]

Hi there, sorry for the late reply on this, you have to make sure you connect your reports to the online source, not to a file in your local folder. Here's an example, of how to connect to a google sheets online: kzbin.info/www/bejne/kGq5Y2VnmrOVn5I

[@xcetam]

Is there a work around for , excel sheet placed in SharePoint site where the name remains the same but the date keep changing weekly. Like XXXXXXXXXXXXXX-17-Oct-2021 this week and next week the date will 24-OCT-2021. ??

[@SolutionsAbroad]

Hey there, You can have a look at using some text manipulations to organise the names:kzbin.info/www/bejne/e6eQnoKhj9xlp6c For that scenario I would use the RIGHT in order to extract the days, months, years and then convert them into dates, similar to this video: kzbin.info/www/bejne/rnrWaaqiqZ5kg8k

[@laxminayak2641]

Hi Fernan, I love all your videos and the way you explain in simple terms makes it even more interesting. So thank you for that :) This is an awesome video to get started with the 'Get Data' function in Power Bi. I was looking at exactly the same solution. But when I connect to the SharePoint Online List' I get an error Details: "The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server." Do you know why it happens? Thanks in advance!

[@jpg6832]

Thank you very useful! Doing a great job with your videos

[@selinsakin1]

Hi, thanks for sharing this valuable content. I have a question how we can get the latest excel into PBI instead of combining them ? Cause I created a power automate rule that takes the excel from an email that comes daily but since this excel is year to date I want to use the latest. Thanks in advance for the input.

[@reachankitsobti]

Hi @Selinsakin1, good question, did you find any solution to this. if not, how about you bring all data to PBI file through email and then in Power query remove the duplicates rows? That would keep the records.

[@BISAACk]

Hi thank you for this video, do you have a related video or maybe create a new video with more details on the 3rd option. "Ultimate guide to import JSON files from M365 SharePoint document folder using POWERFLOW how to phrase the JSON Structure data and finally How to create a PowerBI report from that new PowerFlow in your online workspace" this video is great but is talks about CSV files, the i have tones of JSON files with different structure data each JSON file maybe different meta data. i am having issues with PowerFLOW transforming the JSON file.

[@ArifulIslam-gs6uy]

your tutorial is best always

[@rajuthumpala5436]

Thank you very much for your prompt reply, I really impressed....

[@SolutionsAbroad]

You're welcome!

[@devinrai2814]

Hi Can you may be show a video on how to add new monthly file every month and what needs to happen other than refresh. So each month you just add the same format file to that folder representing that months data. And how that will work with the visuals, and what data transformation will be needed each month when that months files is added to that folder?

[@SolutionsAbroad]

Hi Devin, bit of an old video but i cover this exact thing here: kzbin.info/www/bejne/nWjNq3eDaJ6MpK8 It's the same solution that will work even if your data source is SharePoint. Hope that helps!

[@rajuthumpala5436]

1. I created the PBI report on my desktop and published into PBI Premium work space, here the source file is from SP folder. 2. when I am refreshing data (add + update) in SP, the desktop PBI report is working fine. 3. After that I am again publishing the refreshed PBI into Premium work space. Question: is there any possibility to create a PBI report in PBI premium work space with the source as SP folder? please help me.., thank you.

[@SolutionsAbroad]

Hi Raju, when you publish your report to your Power BI Workspace, it should already be connected to your SharePoint folder, you can refresh directly from the Power BI web version without having to do it on Desktop

[@jonprendergast7009]

I feel like the only thing missing is the one thing I can't seem to find anywhere - how to connect to a specific sharepoint list view. I'm trying to connect to a view that is one of dozens of views, but can't find answers anywhere....Please help!