Thanks Goodly. Honestly i looked through several videos online for this and yours was by far the best. You were short methodical and clean. Not going back and forth with several alternate explanations during the main steps. Keep up the good work.
Thank you so much for this video! You just saved my life. I needed to share a file with my work colleague so she could refresh the data during my absence and publish to other departments in my company. Only now I can go on holidays without taking my laptop with me.
Great tutorial. Would be great if someone could share how to retrieve the data from these files. I know you have mentioned another video, I tried and could not make it work. Anyone in this group that could share the file retrieving all the data in the file would be great. Many thanks.
This video save my life hahaha. i needed to change file by file, now i can save excel files in different folders and use the path as filter in power query many many many many thx
Thanks for this trick. Tricky part is to make on a file stored on a "One Drive" . Since the File Path formula gives you a web address and then through Power query you need to Login using Microsoft / Windows / Organizational Credentials. It worked well for the 1st time when I chose the path for the Source as below Source = Csv.Document(Web.Contents(FilePath & FileName)... But failed on subsequent refresh, and returns a 404 error not found... Any thoughts on how would you handle this?
Thank you! I have another problem. Perhaps you can help me with that? I have one a dashboard built on a source with one excel file. Now it is becoming bigger and i would like to change the source to a folder with multiple monthsfiles in it. Afterwards i would like tot combine these files in the dashboard and filter on these months, but how do i change the source from "file" to "folder"? Tnx in advance! Ria Thomas
Hello, I find this very useful in sharing my workbook to others. But can you please direct me to your video where you discuss how to combine the contents after filtering out "Consolidate All Data"? I'm getting an error that says: "Expression.Error: We couldn't find an Excel table named DynamicPath"
@@cjw3688 search for this video name: Change the Power Query source based on a cell value |Data refresh automation | Excel Off The Grid The dynamic part is when you change the value of a cell, then the query will change the source based on the cell value. This also works on filters. I also use this method on filtering my Months columns
@@johnzafe Thanks, this worked for me, cheers. However now I'm stuck at combining multiple files (see my reply to goodly above), Tried multiple solutions for multiple files, however they don't go to the level of combining files and that is where it is going wrong.
Please help! I followed the instructions but when I try to combine the files I got the following error: Parameter1 we couldn’t find an excel table names dynamic path
Hi, After I filter the file list and try to expand binary column it shows Expression Error: we couldn't find an Excel table named 'Table2'. How to resolve? Thanks.
Hello there, thanks for the wonderful video. I'm wondering if similar can be done in Excel VBA Vlookup Dynamic File Path, how would the code looks like?
Hi, I want to pickup similar name type files only from the folder path. Its like let say at a single folder path there is many type of like A file have Jan , Feb , mar whereas B have jan , Feb < mar also. Now i just want to pick up all files starting with A*. Please help me.
I know you probably don't care anymore, but I had the same issue. He deletes the second step that is automatically done, so the formula bar changes to the import step.
I am trying to create an inventory of folders/subfolders and the files contained within from File Explorer. I'm using Excel > Get Data > From Folder. I'm finding there errors sometimes occur. How is this preventable? Is there another way to obtain a file inventory with its path?
Is there a way to get the entire folder structure of a proyect, including possible empty folders? I am trying to write a macro that automatically reads and update it as and for a graphical representation of it, like an org chart linked to all of it from where the scope of a project could be access and browse in a much customized and moded way
Thanks for the video. I tried something similar but I keep getting the Formula.Firewall error. The M Code that I am trying is as follows let Source = tbl_folderpathsettings, Custom1 = tbl_folderpathsettings[Location]{List.PositionOf(tbl_folderpathsettings[Worksheet], "dtSales")}, Custom2 = Folder.Files(Custom1) in Custom2 Can you help
Will this method works in other's machine where files were placed in their local machine. Let's say I have connected to the files on my local machine and developed the report by following this method to access multiple files. After that I would like to share the PBIX file to others where they have their own folder on their local machine. Will this method works in this scenario as well.
A great Tutorial, very useful trick, are you able to get pass by privacy issues with this method? there are no Forluma Firewall errors with this dynamic file path trick?
Will the same technique be applied when we copy the file in different name and share it. Thanks. It will be really helpful when the file is shared to someone who is an end-user of the file & changes the file name
I have built a complex data model in Excel which combines hundreds of data files in multiple folders, and multiple lookup files, so there's about 20 different queries in the one model. All of those folders are in one folder in OneDrive under a different user who shared the folder with me. My data model and dashboard works perfectly for me no matter where I save the excel file. But if anyone else opens it all the queries break. How do I create a dynamic file path for all the queries so anyone can open the excel dashboard and refresh the data? It's insane there's all this tech to work from the cloud and build data models in so many platforms, but it seems impossible to share the work even within a business. I'd really appreciate the help please.
what if some days the folder you're getting your source files from is empty and some days it's not. on the days i have the folder is empty (because there was no data for that day), it's erroring my query. how to fix that!!! anyone!!!
I have multiple workbooks and each one has multiple worksheets, I want to keep specific worksheets from each book. How to do that in Power Query? How to easily learn Power Query, its functions are very hard to learn.
@goodly, or anyone who knows, I'm getting "Expression.Error: We couldn't find an Excel table named 'Table1'. Details: Table1" after combining files which are the result of the code below: let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], EnergyDataFilePath = Source{0}[EnergyDataFilePath], GetFilesFromFolder = Folder.Files(EnergyDataFilePath), #"Filtered Rows" = Table.SelectRows(GetFilesFromFolder, each Text.StartsWith([Name], "elektriciteit_") and Text.EndsWith([Name], ".xls")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content"}) in #"Removed Other Columns"
Hi, i tried your path formula and it didn't work for me (probably becouse of excel verion). Here is one that worked =LEFT(CELL("filename"); FIND("\[";CELL("filename")) -1)
At ua-cam.com/video/LHNVkiGlUbE/v-deo.html, you mention getting data from the file path. It's likely you answer this in other videos, which I'll search for after posting, but here's what I'm up against: I have over 30k Excel files in 2 folders. The files have an identical format. I need PQ to make a data table of specific cells from each file.
Been stuck for several days each 8 hours on this issue, you solved it in an instant, from A to Z solution in 10 minutes. Thank you.
Thanks Goodly.
Honestly i looked through several videos online for this and yours was by far the best.
You were short methodical and clean. Not going back and forth with several alternate explanations during the main steps. Keep up the good work.
Chandeep is the best of the best in PQ. So proud an Indian is leading the way ahead
Thank You !
Love you man I've been searching for this for last 3 days and solved my problem within 2 mins,
Great, Appreciated.
Most appreciative for this trick! Clear and concise. Seems to work with Sharepoint locations aswell!
Thank you so much for this video! You just saved my life. I needed to share a file with my work colleague so she could refresh the data during my absence and publish to other departments in my company.
Only now I can go on holidays without taking my laptop with me.
Glad it helped! Happy Holidays my friend :)
This video is seriously underrated !!
Great tutorial.
Would be great if someone could share how to retrieve the data from these files.
I know you have mentioned another video, I tried and could not make it work.
Anyone in this group that could share the file retrieving all the data in the file would be great.
Many thanks.
This video save my life hahaha.
i needed to change file by file, now i can save excel files in different folders and use the path as filter in power query
many many many many thx
Thanks for this trick.
Tricky part is to make on a file stored on a "One Drive" . Since the File Path formula gives you a web address and then through Power query you need to Login using Microsoft / Windows / Organizational Credentials.
It worked well for the 1st time when I chose the path for the Source as below
Source = Csv.Document(Web.Contents(FilePath & FileName)...
But failed on subsequent refresh, and returns a 404 error not found...
Any thoughts on how would you handle this?
This trick is so helpful - Thank you very much for sharing !!!!
Glad it was helpful!
Tks so much! Its very very useful for me! I love how you edit the code in Power query of Power BI
Thanks for posting, never considered the formula to tease out the file path!
Thankyou very much for making this video brother, really useful.
Thanks so much, this was helped in sharing my folder with others.
Great method 🙏
Thanks so much for sharing. Well done!
Very usefull tip. Thanks
Tks for your sharing... don't forget to name the cell of the path at first
Thank you very much!! Excellent tip, helped me a lot
Glad it helped!
How to change power query's source of data through VBA?
Thank you very much. Awesome tip!
You're welcome!
This will link excel and power bi and also if we changes data in excel it will change in power bi dashboard???
Can we input a date on pbi report which will hit the data base and refresh the report?
Worked great-thanks!
Glad it helped!
Thank you! I have another problem. Perhaps you can help me with that?
I have one a dashboard built on a source with one excel file. Now it is becoming bigger and i would like to change the source to a folder with multiple monthsfiles in it. Afterwards i would like tot combine these files in the dashboard and filter on these months, but how do i change the source from "file" to "folder"?
Tnx in advance!
Ria Thomas
Use Folder.From("Folder path") in your source step
Another cracking solution Goodly 😉
Thanks again!
Hi, How would I get latest files from the sharepoint folder? or latest 16 files based on the months number?
Thanks. How we can select more than one file dynamically to compare thos etwo files
Hello, I find this very useful in sharing my workbook to others. But can you please direct me to your video where you discuss how to combine the contents after filtering out "Consolidate All Data"?
I'm getting an error that says: "Expression.Error: We couldn't find an Excel table named DynamicPath"
same issue when I share the file to my colleagues
@@shalyyu-anonuevo932 I found a different method which uses named ranges, which I find simpler
@@johnzafe Can you point me/us to that method? Thanks
@@cjw3688 search for this video name: Change the Power Query source based on a cell value |Data refresh automation | Excel Off The Grid
The dynamic part is when you change the value of a cell, then the query will change the source based on the cell value. This also works on filters. I also use this method on filtering my Months columns
@@johnzafe Thanks, this worked for me, cheers. However now I'm stuck at combining multiple files (see my reply to goodly above), Tried multiple solutions for multiple files, however they don't go to the level of combining files and that is where it is going wrong.
It helped me a lot, thanks
Please help! I followed the instructions but when I try to combine the files I got the following error: Parameter1 we couldn’t find an excel table names dynamic path
getting the same issue up for this query
@@amanphilipI wonder if you ever resolved 😅
thx you for nice Video and for let your file
Great, thank you!
Hi, After I filter the file list and try to expand binary column it shows Expression Error: we couldn't find an Excel table named 'Table2'. How to resolve? Thanks.
Hi, pls post the video to bring data from single Excel from a folder using powerquery
Hello there, thanks for the wonderful video.
I'm wondering if similar can be done in Excel VBA Vlookup Dynamic File Path, how would the code looks like?
How to do a dynamic path from one drive... Please suggest
For pdf conversion, is there a dynamic method?
Hi, I want to pickup similar name type files only from the folder path. Its like let say at a single folder path there is many type of like A file have Jan , Feb , mar whereas B have jan , Feb < mar also. Now i just want to pick up all files starting with A*. Please help me.
What is happening in 3:17. Something is clicked that does not show in the camera and the formula totally changes? (Change, type, separate)?
I know you probably don't care anymore, but I had the same issue. He deletes the second step that is automatically done, so the formula bar changes to the import step.
I am trying to create an inventory of folders/subfolders and the files contained within from File Explorer. I'm using Excel > Get Data > From Folder. I'm finding there errors sometimes occur. How is this preventable? Is there another way to obtain a file inventory with its path?
Is there a way to get the entire folder structure of a proyect, including possible empty folders? I am trying to write a macro that automatically reads and update it as and for a graphical representation of it, like an org chart linked to all of it from where the scope of a project could be access and browse in a much customized and moded way
You can use the function
Folder.Files("inputfolderpath") to get all files and sub folders in a folder!
@@GoodlyChandeep Thanks, but it doesn´t get every folders, since the empty ones are still ignored
@@AniManuSCh Try working with Folder.Contents
Thanks for the video. I tried something similar but I keep getting the Formula.Firewall error. The M Code that I am trying is as follows
let
Source = tbl_folderpathsettings,
Custom1 = tbl_folderpathsettings[Location]{List.PositionOf(tbl_folderpathsettings[Worksheet], "dtSales")},
Custom2 = Folder.Files(Custom1)
in
Custom2
Can you help
Will this method works in other's machine where files were placed in their local machine. Let's say I have connected to the files on my local machine and developed the report by following this method to access multiple files. After that I would like to share the PBIX file to others where they have their own folder on their local machine. Will this method works in this scenario as well.
No. In PBIX youll need to change the path to match the local computer.
@@GoodlyChandeep Once I share the file with them, they can change the path and run the report. am I correct
If the file "Year 2006.xlsx" moves, will an error code appear/data cannot be refreshed?
How to read parquet files form azure data lake storage dynamically.
A great Tutorial, very useful trick, are you able to get pass by privacy issues with this method? there are no Forluma Firewall errors with this dynamic file path trick?
No the privacy issues still remain :(
@@GoodlyChandeep Here is the solution from Mike Gervin ua-cam.com/video/0NX-GctfZuU/v-deo.html
@@ExactProBi hey thanks for sharing, I saw the video and my solution here does get over the privacy problem. I wasn't aware of it.
Thanks man!
Cheers
what about pdf and txt formatting of files?
I have an URL from web where date is changing daily basis how can change the date dynamic in power query from web URL option?
Will the same technique be applied when we copy the file in different name and share it. Thanks. It will be really helpful when the file is shared to someone who is an end-user of the file & changes the file name
If the source is a folder path. The file name won't matter :)
Hope this helps
I have built a complex data model in Excel which combines hundreds of data files in multiple folders, and multiple lookup files, so there's about 20 different queries in the one model. All of those folders are in one folder in OneDrive under a different user who shared the folder with me. My data model and dashboard works perfectly for me no matter where I save the excel file. But if anyone else opens it all the queries break. How do I create a dynamic file path for all the queries so anyone can open the excel dashboard and refresh the data? It's insane there's all this tech to work from the cloud and build data models in so many platforms, but it seems impossible to share the work even within a business. I'd really appreciate the help please.
how can i do the opposite? i need to export the power query result to a file. But without the "EVALUATE" pop up
thanks really helpful
Glad it helped
cant we use parameter?
How i can import locked file in Power query
Thank you so much
After I drill down I don’t get the “source” step it turns into “changed type” step. Therefore I cannot write the formula
what if some days the folder you're getting your source files from is empty and some days it's not. on the days i have the folder is empty (because there was no data for that day), it's erroring my query. how to fix that!!! anyone!!!
Fantastic
Indians always saving my a$$ with YT tutorials
Thanks but this has nothing to do with being an Indian 🤣
Hahaha i know, greetings from Venezuela bro!! @@GoodlyChandeep
Hmm, a simple "get current path" function requirement will be resolved by MS in an extremely complicated way.
You are awesome
Thanks Satya, for being generous with words :)
When I try to combine file I got message "we couldn't find table name" any suggestions?
I have multiple workbooks and each one has multiple worksheets, I want to keep specific worksheets from each book. How to do that in Power Query? How to easily learn Power Query, its functions are very hard to learn.
Thanks
@goodly, or anyone who knows, I'm getting "Expression.Error: We couldn't find an Excel table named 'Table1'. Details: Table1"
after combining files which are the result of the code below:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
EnergyDataFilePath = Source{0}[EnergyDataFilePath],
GetFilesFromFolder = Folder.Files(EnergyDataFilePath),
#"Filtered Rows" = Table.SelectRows(GetFilesFromFolder, each Text.StartsWith([Name], "elektriciteit_") and Text.EndsWith([Name], ".xls")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content"})
in
#"Removed Other Columns"
Hi, i tried your path formula and it didn't work for me (probably becouse of excel verion). Here is one that worked
=LEFT(CELL("filename"); FIND("\[";CELL("filename")) -1)
"-1" not required since "\" required is required at the end of the path
Do you know how to do a Folder Path ?
At ua-cam.com/video/LHNVkiGlUbE/v-deo.html, you mention getting data from the file path. It's likely you answer this in other videos, which I'll search for after posting, but here's what I'm up against:
I have over 30k Excel files in 2 folders. The files have an identical format. I need PQ to make a data table of specific cells from each file.