Connect Excel / CSV files from SharePoint to Excel or Power BI
Ғылым және технология
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!
Пікірлер: 58
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.
@cristianprifti
3 ай бұрын
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.
@ryanwitte3475
3 ай бұрын
@cristianprifti the same is true for SharePoint.Files as well in the folder filter step.
@Scott-lc5kh
3 ай бұрын
@@ryanwitte3475 exactly. plus any dev worth a grain of salt should parameterise the SP root anyway.
@antoniofelez4674
Ай бұрын
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…..
@antoniofelez4674
Ай бұрын
If there is a worker around please share, I’m new to this thing
Good to see you after 12 years, I was part of your excel camp in noida.
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 👍
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.
Great video. Thanks
Thanks Chandeep, looking for this content
Excellent.. Thanks🙏
Very good, congratulations
Very Nice Video..
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
Super 👌
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
Hi Chandeep, is there a way to have dynamic sharepoint folder path?
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.
Nice video as always! What about a way to preserve the name of the file as new column?
Does these processes address inclusion or exclusion of columns automatically in the files?
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 KZread tutorials, I'm still struggling to find the folder path. Any suggestions?
Please create video on embedded SharePoint
How or Dose it can refresh power query automatically or by scheduled refresh in the excel?
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
@GoodlyChandeep
2 ай бұрын
kzread.info/dash/bejne/mKWgmq9xoce8gto.html
how to make it to automatically refresh on a daily basis from my shared files?
His eyes be like: "open this video will you!". 😂
@GoodlyChandeep
2 ай бұрын
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?
@user-uz7ie6pe1v
3 ай бұрын
Is to create a new team to upload files in teams
Hi! How to get data from SharePoint where one has read access only.
If I know power query then Chandeep thought me.
What is the underscore _?
@cristianprifti
3 ай бұрын
It is the technical way of saying, each (row)
There is no folder in my sharepoint website, is there any setting I need to do
@user-uz7ie6pe1v
3 ай бұрын
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
@user-uz7ie6pe1v
3 ай бұрын
Fixed, is to create a new team to upload files
I prefer sharepoint.contents method - to me it gives faster result, and easier navigation than filtering to folder/files, etc.
I have been doing it from last 2 years
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
@cristianprifti
3 ай бұрын
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.
@vansree
3 ай бұрын
@@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
@ttilta
3 ай бұрын
@@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.
@vansree
3 ай бұрын
@@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 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.
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
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).