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

Рет қаралды 20,705

Күн бұрын

Get Demo Files here
ko-fi.com/s/53268b181b
In this video we go through how you can connect your SharePoint data to Power BI, if the data is in a SharePoint list, a file within SharePoint, or how to combine and work with multiple files in a SharePoint folder.
-
🚩 Support us to get perks such as demo files, early access and more!
/ solutionsabroad
MY FAVOURITE POWER BI BOOKS
📕 The Definitive Guide to DAX - amzn.to/3ruYs2f
📗 DAX Patterns - amzn.to/3oVgOYs
MY GEAR
🎥 Canon 4000D DSLR Camera - amzn.to/3tCqDOw
💻 Cyberpower Wyvern PC - amzn.to/3q04QOw
🪑 Infamous Gaming Chair - amzn.to/3q3iUXw
💡 LED Studio Lighting - amzn.to/3q3ftQz
🎙 Lavalier Microphone - amzn.to/3jwKutG
📺 Monitor - amzn.to/2LuHhhO
GET IN TOUCH
📺 Website - www.solutionsabroad.co.uk
📧 Email me - fernan@solutionsabroad.co.uk
🤵 LinkedIn - / solutionsabroad
📘 Facebook - / solutionsabroad
📸 Instagram - / solutions_abroad
🐤 Twitter - / abroadsolutions
🤖 Reddit - / fernanespejo
🌍 WHO IS SOLUTIONS ABROAD
Hi, my name is Fernan, the one-man band of Solutions Abroad. I work as a Data Scientist based in London, UK and have over 6 years of experience working in Business Intelligence. Solutions Abroad these days is dedicated to teaching the ins and outs of Power BI, through the power of KZfaq.
If you want to learn more, I’ve conveniently created playlists to help you find the right topic for you.
🐎 First, watch this video about how a typical Power BI workflow looks like
• Power BI from START to...
🗺 Learn DAX to extend your Power BI calculations
• Power BI DAX Basics OLD
📆 Stay up-to-date on all the features Power BI releases monthly
• Power BI Monthly Featu...
🧪 Get certified as a Microsoft Data Analyst Associate
• Power BI DA 100 Prepar...
💡 Learn Power BI Features to improve your dashboards
• Power BI Feature Highl...
📊 WHAT IS POWER BI?
Power BI is a business analytics tool by Microsoft. It creates a working environment suited for normal users, meaning tasks such as data extraction, cleansing, analysis and storytelling; these are made easily-accessible to citizen-users, and extendable for technical users. At face value it provides visually-stunning, interactive dashboards, but it’s built on top of so much more.
🔻 Get started with Power BI Desktop for FREE
powerbi.microsoft.com/en-us/d...
#PowerBI #DataAnalytics #BusinessIntelligence

Пікірлер: 29

@jpg6832 2 жыл бұрын

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

@ArifulIslam-gs6uy 11 ай бұрын

your tutorial is best always

@lifeuncut1781 3 жыл бұрын

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 3 жыл бұрын

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!

@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!

@rajuthumpala5436 3 жыл бұрын

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

@SolutionsAbroad 3 жыл бұрын

You're welcome!

@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!

@BISAACk 2 ай бұрын

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.

@selinsakin1 2 жыл бұрын

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 4 ай бұрын

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.

@dhruvpunetha1108 2 жыл бұрын

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?

@PhillipLearnTeach 2 жыл бұрын

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 2 жыл бұрын

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

@gauthierbouckaert6620 2 жыл бұрын

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 2 жыл бұрын

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?

@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.

@devinrai2814 3 жыл бұрын

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 3 жыл бұрын

Hi Devin, bit of an old video but i cover this exact thing here: kzfaq.info/get/bejne/nZ2arad-mJ-9noE.html It's the same solution that will work even if your data source is SharePoint. Hope that helps!

@VidharthiRanjan 2 жыл бұрын

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 2 жыл бұрын

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

@SolutionsAbroad 2 жыл бұрын

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: kzfaq.info/get/bejne/kJ-GZZViyrTGmWQ.html

@oludamolaaromofisomo5553 2 жыл бұрын

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

@SolutionsAbroad 2 жыл бұрын

Hi, yes of course! You can use the Subscribe feature or Power Automate to power thisL kzfaq.info/get/bejne/d9ifos9_rcu9aKM.html

@mbhagyasri88 2 жыл бұрын

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 2 жыл бұрын

Hey there, You can have a look at using some text manipulations to organise the names:kzfaq.info/get/bejne/e9xdoLKcv92WoXk.html 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: kzfaq.info/get/bejne/rq-ja9qd2Z-VfZs.html

@rajuthumpala5436 3 жыл бұрын

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 3 жыл бұрын

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 2 жыл бұрын

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!