Connect Excel / CSV files from SharePoint to Excel or Power BI
HTML-код
- Опубликовано: 28 май 2024
- In this video, we will learn how to Connect Excel / CSV files from SharePoint to Excel or Power BI. We will 1st Connect to the SharePoint Folder using Power Query. Then we will Connect & Combine Excel/CSV files from SharePoint.
===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co.in/blog/
Corporate Training 👨🏫 - www.goodly.co.in/training/
Need my help on a Project 💻- www.goodly.co.in/consulting/
Download File ⬇️ - goodly.co.in/connect-excel-cs...
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - goodly.wordpress@gmail.com
===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'll find a ton of interesting videos that I have created :) Cheers! 
Наука
![[adult swim] - Smiling Friends Season 2 Episode 5 Promo](http://i.ytimg.com/vi/uStNEeR8LT4/mqdefault.jpg)
I prefer and advise Sharepoint.Contents. This more navigable in the UI, like a traditional folder structure. Plus it doesn’t query a list of all the files on the Sharepoint site. Once you start getting into large quantities of files on a Sharepoint site, Sharepoint.Files tends to cause issues.
I also love this method, but it does come with strings attached, if the SharePoint is changed and the root path is not identical anymore.
@cristianprifti the same is true for SharePoint.Files as well in the folder filter step.
@@ryanwitte3475 exactly. plus any dev worth a grain of salt should parameterise the SP root anyway.
SharePoint.content gets tricky when you want to combine the binary in the content column. If there is a sub folder it shows as “table” so you can either combine the current folder or go into sub folder and combine that…..
If there is a worker around please share, I’m new to this thing
Love the way you combine without all the helper queries!! In the beginning when I just started with PQ, I struggled to underssstand what all these "automated" queries where.
Hii , chandeep , You are best teacher , and i can easily understand what content you deliver,
Kindly request you to create more content for SharePoint, how it works into Power BI , excel and what are security features of SharePoint..
This has really been needed! I've worked with people doing it so many different ways!
Excellent.. thanks for sharing 👍
Great video. Thanks
Thanks Chandeep, looking for this content
another great video, thanks Goodly! I have one small note though, before combining tables you need to make sure that there are not DefinedNames (this can come from ex. filtering source file), which will result duplicating the data
Excellent.. Thanks🙏
Very good, congratulations
Very Nice Video..
Hey Chandeep, thanks for helping us with the wonderful tutorial. Can you please let us know how to retain the file name as well? May be a video would be good. Thank you.
Great content. Can you tell me is there any performance difference between Table.Combine Vs Table.Expand?🤔 coz many times I see your videos, preferred with Combine. Please suggest
Super 👌
Hey Chandeep, I've been trying to retrieve the folder path for a folder in my OneDrive Personal account. I need it for Excel Power Query to append files from that folder. Despite Googling and watching RUclips tutorials, I'm still struggling to find the folder path. Any suggestions?
Nice video as always!
What about a way to preserve the name of the file as new column?
Great content
What if files have multiple headers and we want to promote second row as header row without deleting first header row
Please advice
ruclips.net/video/fql8M8qdVRw/видео.html
Does these processes address inclusion or exclusion of columns automatically in the files?
How or Dose it can refresh power query automatically or by scheduled refresh in the excel?
Hi! How to get data from SharePoint where one has read access only.
I prefer sharepoint.contents method - to me it gives faster result, and easier navigation than filtering to folder/files, etc.
Please create video on embedded SharePoint
His eyes be like: "open this video will you!". 😂
I am glad you opened. 😃
In last one if i want to combine 2 column not one , how ???
It Works when someone share a folder with one drive?
Is to create a new team to upload files in teams
I was doing this quite easily until Excel power query started to not read the CSV files properly and decided to interpret it as gobbledygook. I did the exact same power query but changed the source to internal drives and it was fine
There is no folder in my sharepoint website, is there any setting I need to do
I can't find share with us,Do I need to set up a shared folder, or is this file shared with me by someone else
Fixed, is to create a new team to upload files
If I know power query then Chandeep thought me.
What is the underscore _?
It is the technical way of saying, each (row)
I don't know why Microsoft is doing that. I tried to pull in a csv that is on the 5th level of folders (site/folder1/folder2/folder3/folder4/) and it would take like 30 minutes to even update the query in the editor, to list all the binaries etc. No one can work with that. The goal was to build an Excel File that is stored on sharpoint, and it always reads the newest csv from a folder. There will be a new file every day. I couldn't get it to work because powerquery never stopped loading. It tries to putll in ALL the folders and files first, before I can start to narrow it down. Why? There's roughly a million documents to go through, before I get to my csv.
Using this technique, I created the PBI dataflow and able to code to get CSV files from SHAREPOINT.
The csv files are like 2 MB to 10MB. Overall, all files size is around 2.2GB+.
Each csv file is like countries in a region, and for each country, order summary and detail rows. Power Query transformations are very simple and max of 20 lines in the combine file transformation. (header, fill down for detail rows, cleanup, data type ... nothing big there)
Since I have around 2GB+ CSV files on SharePoint and pretty slow while combining.
Performance or improvement question: Is there a better approach or solution when you have such large data? TIA
Try what Scott mentioned, of changing from .Files to .Contents connector. I tried it with a large SharePoint and it works faster. If you also need to enhance the data, you can also use 2 separate dataflows, one that just loads the data raw and one that will apply certain rules.
@@cristianprifti I tied that method it is even slow. I e., splitting two flows. First flow fetches files and add index for line number.
Second flow parse, transform, split and model the data as tabular
@@vansree I have a similar predicament and I intend to try something at the end of the month, if you have time you can perhaps try it as well.
I intend to output post-transformed data from prior months into a CSV file, lets call this file "Post-Transformed Historical Data.csv". In PowerQuery I'll only transform data for the current month and import the historically data from Post-Transformed Historical Data.csv which would not require any transformation. My thought process is if I can minimize the number of transformations required every time I refresh the data, then it should move quicker.
@@ttilta I used power shell to do that transformation and the outputs are used for power bi source
The business does not want this extra step of fetching, downloading, transforming
I even used table.buffer and list.buffer techniques
It all works great for .5gb and after that it is slow
Thanks
I have been doing it from last 2 years
Hi Chandeep, Thanks a lot for your videos, have learnt a lot from you.
We can obtain files from OneDrive online too into Power Query using the UI & having been doing it since a long time (hope I did not misunderstand you).