Data Loading Tricks in Power Query
Vložit
- čas přidán 5. 09. 2024
- Check out our newly launched M Language course ↗️ - goodly.co.in/l...
- - - - My Courses - - - -
✔️ Mastering DAX in Power BI -
goodly.co.in/l...
✔️ Power Query Course-
goodly.co.in/l...
✔️ Master Excel Step by Step-
goodly.co.in/l...
✔️ Business Intelligence Dashboards-
goodly.co.in/l...
- - - Blog & Videos - - -
Blog - www.goodly.co....
1. Applying a Dynamic Filter. [While using Excel file in Power Query, you can apply 'Date filter latest' on the "Date Created" column in Power Query.
2. Creating a Dynamic Path. [Making use of manage parameters and using that parameter to pass the path for your queries]
3. Data Load Performance [Excel vs CSV . Csv is better. Refer Chris Webb blog
4. Creating Helper table [Use of 'Enter Data']
5. Creating a Date from the Name of a Excel File
Thank You!
Thank you
Haha! I’ve been taking original CSU data files and converting them to excel files and cringing on the upload speed. Thank you!
Enjoyed the path trick! It facilitates the life a lot, especially when you're building a dashboard on an environment that later might be changed.
Hi Chandeep. Awesome Power Query data loading tricks! Thanks for sharing them :)) Thumbs up!!
Excellent presentation! I am now a regular viewer.
Hi Chandeep, thank you for providing these tricks. I'm learning something new everytime I watch your videos.
I have a followup question on the date extraction. If i do not wish to combine the file, but just extract the name / date from the filename, what changes should be made?
Hi chandeep, is it possible to get data from a query that is stored in an another workbook?
Thanks and greetings from Turkey ❤
Does PowerQuery have a feature to link to a folder on a Sharepoint? I know each excel file has web path, but not sure if we can add path to the sharepoint folder.
Hi chandeep, those tricks are awesome,
Is there a way to load new files with out refreshing old files
ie :-old files are already loaded in ,
This is taking so much time to load
This is possible using incremental refresh
Hi Chandeep,
Trick no 2: here we are creating parameter for changeing source path right?
We can able to change source in Data source settings under home tab right!
Do both the options will work same or any differences we have.
Thanks!
Wow! The tips 1, 2 and 5 will solve the problem I ran into yesterday - THANKS!!
Chandeep, you are simply THE BEST! Thank you so much! 🙏
Is there any way to refer cells in power bi like we do in excel?
Hi Chandeep, thanks for another helpful set of tips. The helper table suggestion was timely; it was something I'd forgotten about but I just realized it would be the perfect solution for some static data that I need for my model. Really appreciate your content. Thanks!
Sir pls make a video on Table.buffer
very useful tricks, some of it I thought do not exist ..always created duplicate file an folders to for the dynamic folder path...🙏👌
One video, but full of tips. Thanks.
Glad you like them!
Many Thanks, Chandeep, this is very helpful stuff 😊
Great tricks
Thanks Chandeep, that was new to me and helpful.
Glad you liked it!
How can the second trick, creating a folder path, can work with a sharepoint folder?
Thank you!! it is posible to do a incremental refresh in power query with csv files?
Hi Chandeep, very nice as always...I am trying to load multiple pdf files which has multiple pages from a folder..In doing so, power query does not allow to select multiple pages (this feature is only available when I connect directly to one pdf.) Is there a way to overcome this?
Hi Goodly: Question: I am trying to open one file in Power Query that the first raw is empty "blank" and does not have the Headers of the data, the data starts on raw 3, Power query does not read the data and says that the table is empty, how can I fix this issue? Thanks so much, Jose Luis
Hi Chandeep, great video.
I was working on a problem yesterday that I'll now use a couple of those types on.
1 question, is there a way of getting a dynamic path that is the current location off the Excel file? That way if the Excel file is copied to another location and the data files are in the new location too, power query will look for the data in the that folder and not the original location.
Thanks, Ron
www.goodly.co.in/dynamic-file-path-power-query/
Hi ❤ that one too. Do you know whether there would be a difference between .CSV vs .txt performance wise. We extract data from SAP into Excel and then there are saved text tab delimited. As CSV uses ; as separator it can created problems when the posting text contains a ;
Can say for sure that csv has faster performance as compared to excel.
I want to combine 8 files into one using merging. Usually i do vlookup.
So can i do this with power query and repeat same steps everyday
How can I get data in excel power query from An old 2004 Sybase ASE Database version 12.5.2 already deprecated fro Sap sybase and can’t find any online odbc connectors
Hello need guidance
Hello i m from commerce complting now graduatiom at 28 can i be a data analyst
what are basic computer knwledge require to data analyst
can u suggest the road map it will be grt help
Unfortunately our software exports old really big in size htm files, any ideas for faster loading, I already used a macro to convert those files to xlsx, but it takes time and it’s not practical, any other solutions ?
You are amazing thank you❤
Love those tricks ❤
Hi Chandeep, while loading excel files into power query few duplicate rows are getting added automatically into the report and i don't know how these extra rows are getting generated and due to this issue visualization values are not matching & please let me know how to resolve this issue
heyy do you know a way to export power query to CSV - without loading query to table or pivottable ?
may you elaborate a basket analysis in powerbi if you may - thanks so much
Hi, I had loaded my query to table and have been using this for months. Now there is need to introduce another table and connect them. How can I load this query to connection?
Dude..... Thank u for this!
Very good tips. Thanks!
Great !
Thanks!
Hi Chandeep - brilliant tips
Is there a way i can have a dynamic filter?
I have column called ID - i have set a filter to remove a number of IDs and have hardcoded the IDs into the power query. Is it possible to have a table that has a list of all IDs and then use the ID column to filter out data from this table?
That way all i have to do is amend 1 table if Ids need to be added or removed
See this
czcams.com/video/eB-LBLS-BRg/video.html
Thank you so much
Chandeep, thanks for the informative video. Is there a way to perform something like the so called "incremental refresh" in Power Query, so that when you add a new excel file in a folder of 10 already existing files, Power Query will only add the new file, not refreshing all files in the folder.
do you have any solution for this .. ????? i'm also facing this issue so long.
@@viveksharma4193 Still not! The only workaround method that I am using is opening the file with Power Query as another instance, then refreshing it. In this way, I am still able to work in another excel files while the PQ one is refreshing in the background.
Very useful tutorial. Thank you :-)
Can you please link the data table video you were referencing?
Very nice as always
Thanks 💚
Sir if I have a folder having Name as Date
How we use that name into power query
Hi chandeep, need your one help as i want to download only top 1000 rows. How I can set this
Use the Table.Skip Function in Power Query
This should give you some ideas - czcams.com/video/c8HQOCbJAzs/video.html
he bro i have problem with my power Bi i am unable to load data i dont know whats problem can you suggest me any solution to it
Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/
How do I stop loading queries all at once?
great tricks