Create a Dynamic File Path in Power Query
Ғылым және технология
In this I’ll share, how can you create a dynamic folder or a file path, both in Excel Power Query and in Power BI.
Download Files - goodly.co.in/dynamic-file-pat...
- - - - My 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...
- - - - Blog - - - -
www.goodly.co.in/blog
Пікірлер: 101
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.
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.
Love you man I've been searching for this for last 3 days and solved my problem within 2 mins, Great, Appreciated.
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.
@GoodlyChandeep
2 жыл бұрын
Glad it helped! Happy Holidays my friend :)
Tks so much! Its very very useful for me! I love how you edit the code in Power query of Power BI
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
Most appreciative for this trick! Clear and concise. Seems to work with Sharepoint locations aswell!
Thanks so much for sharing. Well done!
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.
Thanks so much, this was helped in sharing my folder with others.
Thankyou very much for making this video brother, really useful.
Thanks for posting, never considered the formula to tease out the file path!
Very usefull tip. Thanks
It helped me a lot, thanks
This trick is so helpful - Thank you very much for sharing !!!!
@GoodlyChandeep
2 жыл бұрын
Glad it was helpful!
Great, thank you!
Thank you very much. Awesome tip!
@GoodlyChandeep
Жыл бұрын
You're welcome!
This video is seriously underrated !!
Thank you very much!! Excellent tip, helped me a lot
@GoodlyChandeep
Жыл бұрын
Glad it helped!
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 so much
thx you for nice Video and for let your file
Worked great-thanks!
@GoodlyChandeep
Жыл бұрын
Glad it helped!
Another cracking solution Goodly 😉
@GoodlyChandeep
4 жыл бұрын
Thanks again!
Chandeep is the best of the best in PQ. So proud an Indian is leading the way ahead
@GoodlyChandeep
Жыл бұрын
Thank You !
thanks really helpful
@GoodlyChandeep
3 жыл бұрын
Glad it helped
Fantastic
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?
You are awesome
@GoodlyChandeep
4 жыл бұрын
Thanks Satya, for being generous with words :)
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.
Tks for your sharing... don't forget to name the cell of the path at first
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
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"
@shalyyu-anonuevo932
3 жыл бұрын
same issue when I share the file to my colleagues
@johnzafe
3 жыл бұрын
@@shalyyu-anonuevo932 I found a different method which uses named ranges, which I find simpler
@cjw3688
3 жыл бұрын
@@johnzafe Can you point me/us to that method? Thanks
@johnzafe
3 жыл бұрын
@@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
@cjw3688
3 жыл бұрын
@@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.
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?
@GoodlyChandeep
4 жыл бұрын
No the privacy issues still remain :(
@ExactProBi
4 жыл бұрын
@@GoodlyChandeep Here is the solution from Mike Gervin kzread.info/dash/bejne/YoKMj6mcpMnApbg.html
@GoodlyChandeep
4 жыл бұрын
@@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
Thanks. How we can select more than one file dynamically to compare thos etwo files
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 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?
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
@amanphilip
Жыл бұрын
getting the same issue up for this query
@Kuralai95
Ай бұрын
@@amanphilipI wonder if you ever resolved 😅
Hi, pls post the video to bring data from single Excel from a folder using powerquery
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
@GoodlyChandeep
3 жыл бұрын
You can use the function Folder.Files("inputfolderpath") to get all files and sub folders in a folder!
@AniManuSCh
3 жыл бұрын
@@GoodlyChandeep Thanks, but it doesn´t get every folders, since the empty ones are still ignored
@GoodlyChandeep
3 жыл бұрын
@@AniManuSCh Try working with Folder.Contents
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
@GoodlyChandeep
3 ай бұрын
Use Folder.From("Folder path") in your source step
How to change power query's source of data through VBA?
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
@GoodlyChandeep
3 жыл бұрын
If the source is a folder path. The file name won't matter :) Hope this helps
Hi, How would I get latest files from the sharepoint folder? or latest 16 files based on the months number?
This will link excel and power bi and also if we changes data in excel it will change in power bi dashboard???
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?
For pdf conversion, is there a dynamic method?
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.
@GoodlyChandeep
2 жыл бұрын
No. In PBIX youll need to change the path to match the local computer.
@govardhanbola1195
2 жыл бұрын
@@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 can i do the opposite? i need to export the power query result to a file. But without the "EVALUATE" pop up
Can we input a date on pbi report which will hit the data base and refresh the report?
What is happening in 3:17. Something is clicked that does not show in the camera and the formula totally changes? (Change, type, separate)?
@jehoff
2 жыл бұрын
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.
How to read parquet files form azure data lake storage dynamically.
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"
How to do a dynamic path from one drive... Please suggest
How i can import locked file in Power query
what about pdf and txt formatting of files?
When I try to combine file I got message "we couldn't find table name" any suggestions?
After I drill down I don’t get the “source” step it turns into “changed type” step. Therefore I cannot write the formula
cant we use parameter?
Indians always saving my a$$ with YT tutorials
@GoodlyChandeep
9 ай бұрын
Thanks but this has nothing to do with being an Indian 🤣
@canzos
9 ай бұрын
Hahaha i know, greetings from Venezuela bro!! @@GoodlyChandeep
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)
@priyeshsanghvi8424
10 ай бұрын
"-1" not required since "\" required is required at the end of the path
Do you know how to do a Folder Path ?
At kzread.info/dash/bejne/fnyCuM2id8-7kqg.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.
Hmm, a simple "get current path" function requirement will be resolved by MS in an extremely complicated way.