Create a Dynamic File Path in Power Query

[@Ashen_Raven]

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.

[@JoaoLojaPereira]

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.

[@beastuzair9784]

Love you man I've been searching for this for last 3 days and solved my problem within 2 mins, Great, Appreciated.

[@MrAni311]

Chandeep is the best of the best in PQ. So proud an Indian is leading the way ahead

[@GoodlyChandeep]

Thank You !

[@casinoguy786]

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?

[@martinslevi1]

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.

[@Lyriks_]

This video is seriously underrated !!

[@erlendvanderveeken7882]

Most appreciative for this trick! Clear and concise. Seems to work with Sharepoint locations aswell!

[@4bg0n]

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]

Glad it helped! Happy Holidays my friend :)

[@kauanreis94]

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

[@nizmo1]

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]

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.

[@riathomasaaltink]

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]

Use Folder.From("Folder path") in your source step

[@iifim3812]

How to change power query's source of data through VBA?

[@rajnishkumarchoudhary7520]

Can we input a date on pbi report which will hit the data base and refresh the report?

[@sejalbhattad]

This will link excel and power bi and also if we changes data in excel it will change in power bi dashboard???

[@parvejmahmud1740]

Hi, How would I get latest files from the sharepoint folder? or latest 16 files based on the months number?

[@mariaalcala5159]

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 😅

[@smplfy]

Great method 🙏

[@rizkyaditya5523]

If the file "Year 2006.xlsx" moves, will an error code appear/data cannot be refreshed?

[@immanjohn3527]

Thankyou very much for making this video brother, really useful.

[@tomjoad7074]

This trick is so helpful - Thank you very much for sharing !!!!

[@GoodlyChandeep]

Glad it was helpful!

[@farooqueasad11]

Thanks. How we can select more than one file dynamically to compare thos etwo files

[@1am2syman]

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.

[@saboosudarsan1811]

For pdf conversion, is there a dynamic method?

[@aboutstudy4190]

How to do a dynamic path from one drive... Please suggest

[@sumanhans2175]

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.

[@venugopal-jt6jm]

How to read parquet files form azure data lake storage dynamically.

[@louise1282]

Tks for your sharing... don't forget to name the cell of the path at first

[@perrymoen1447]

Thanks for posting, never considered the formula to tease out the file path!

[@denden5150]

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?

[@taufiqalibhai2238]

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

[@mathproying]

what about pdf and txt formatting of files?

[@govardhanbola1195]

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]

No. In PBIX youll need to change the path to match the local computer.

[@govardhanbola1195]

@@GoodlyChandeep Once I share the file with them, they can change the path and run the report. am I correct

[@huongbui4376]

Tks so much! Its very very useful for me! I love how you edit the code in Power query of Power BI

[@legionssssss3865]

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?

[@johnzafe]

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]

same issue when I share the file to my colleagues

[@johnzafe]

@@shalyyu-anonuevo932 I found a different method which uses named ranges, which I find simpler

[@cjw3688]

@@johnzafe Can you point me/us to that method? Thanks

[@johnzafe]

@@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]

@@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.

[@duydiep3909]

Very usefull tip. Thanks

[@AniManuSCh]

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]

You can use the function Folder.Files("inputfolderpath") to get all files and sub folders in a folder!

[@AniManuSCh]

@@GoodlyChandeep Thanks, but it doesn´t get every folders, since the empty ones are still ignored

[@GoodlyChandeep]

@@AniManuSCh Try working with Folder.Contents

[@Nextwhatguru]

Thanks so much, this was helped in sharing my folder with others.

[@sirajmohammedali2790]

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?

[@FellTheSky]

how can i do the opposite? i need to export the power query result to a file. But without the "EVALUATE" pop up

[@noameed8522]

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.

[@SantoshKoirala]

Thank you very much. Awesome tip!

[@GoodlyChandeep]

You're welcome!

[@ambeauty2001]

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!!!

[@hkk4213]

Hi, pls post the video to bring data from single Excel from a folder using powerquery

[@sunilpanchal9559]

How i can import locked file in Power query

[@dhanicole]

Worked great-thanks!

[@GoodlyChandeep]

Glad it helped!

[@michaelstagner6688]

Thanks so much for sharing. Well done!

[@paspuggie48]

Another cracking solution Goodly 😉

[@GoodlyChandeep]

Thanks again!

[@cesartonoli19]

Thank you very much!! Excellent tip, helped me a lot

[@GoodlyChandeep]

Glad it helped!

[@Kuralai95]

After I drill down I don’t get the “source” step it turns into “changed type” step. Therefore I cannot write the formula

[@arunv1909]

cant we use parameter?

[@alexandrelimabaiao7050]

It helped me a lot, thanks

[@kamini2011]

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]

If the source is a folder path. The file name won't matter :) Hope this helps

[@ExactProBi]

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]

No the privacy issues still remain :(

[@ExactProBi]

@@GoodlyChandeep Here is the solution from Mike Gervin kzbin.info/www/bejne/Zn-7XnqZqcuNq7c

[@GoodlyChandeep]

@@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

[@engahmedelbendary1566]

thx you for nice Video and for let your file

[@marcwampfler2203]

Great, thank you!

[@bezimienna5699]

When I try to combine file I got message "we couldn't find table name" any suggestions?

[@canzos]

Indians always saving my a$$ with YT tutorials

[@GoodlyChandeep]

Thanks but this has nothing to do with being an Indian 🤣

[@canzos]

Hahaha i know, greetings from Venezuela bro!! @@GoodlyChandeep

[@AsfandMudassir]

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.

[@RohitSingh-nu7ek]

thanks really helpful

[@GoodlyChandeep]

Glad it helped

[@marinbotev5134]

Hmm, a simple "get current path" function requirement will be resolved by MS in an extremely complicated way.

[@cjw3688]

@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"

[@seshakv]

Thank you so much

[@ArifulIslam-gs6uy]

Fantastic

[@saisatya5525]

You are awesome

[@GoodlyChandeep]

Thanks Satya, for being generous with words :)

[@Lookatmahnick]

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]

"-1" not required since "\" required is required at the end of the path

[@jasond5872]

Do you know how to do a Folder Path ?

[@SaniGarba]

Thanks

[@Approx_99]

At kzbin.info/www/bejne/gnmxh56ffNGImKc, 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.