Thanks Mark. This is the best and quick way to update the file path into Power Query. I have just amended a bit to have something fully automated by using the following formulas to get the file name and the file path without no manual change: =CELL("filename") then =MID(B4,FIND("[",B4)+1,FIND("]",B4)-FIND("[",B4)-1) assuming the fomula above is in the cell B4 =LEFT(B4,FIND("[",B4)-1) assuming the fomula above is in the cell B4 Thats all. Simply but very efficient. Cheers

@ExcelOffTheGrid

Жыл бұрын

Great work. That’s a nice addition 👍

Most helpful video ever. Not even ChatGPT was as clear as you. Thank you!

@ExcelOffTheGrid

4 ай бұрын

I find that Actual Intelligence always beats Artificial Intelligence 😂

thaks for this - need to do this for a folder content PQ

@dominickgregorio8157

Ай бұрын

Second this!

Thanks!!! I used this tutorial and had complete success!!!

@ExcelOffTheGrid

Жыл бұрын

Woop woop - good work. 👍

So far the easiest and simplest method I found in youtube! Thank you!

@ExcelOffTheGrid

3 жыл бұрын

I had used a harder version for years. Then I found this. It just takes a little knowledge with M code. 😀

So cool! Worked great for me!

@ExcelOffTheGrid

10 күн бұрын

Great to hear! 👍

This is brilliant and helped me from copying and pasting from a bunch of different workbooks. Thank you!!

@ExcelOffTheGrid

Жыл бұрын

You’re welcome. Glad I could help.

Thank you. This is by far the easiest explanation for this.

@ExcelOffTheGrid

4 жыл бұрын

Thanks - I've used a number of more complicated methods. But this seems to be the easiest and fastest.

Sweet! It worked perfectly. A simple and easy to follow tutorial. Thank you!!

@ExcelOffTheGrid

2 жыл бұрын

Thanks Peggy. Glad I could help 👍

This worked out perfectly. Thanks!

@ExcelOffTheGrid

3 ай бұрын

Great news, I’m glad I could help.

I was trying to do this with Parameter queries and then I saw this, great technique, thank you!

@ExcelOffTheGrid

4 ай бұрын

It's all the same methods, but applied in a slightly different way. Hopefully this way is easy to replicate.

Works brilliantly! And the video was easy to follow. Thanks so much!

@ExcelOffTheGrid

2 жыл бұрын

Thanks Chemelle, I’m glad you found it useful.

Brilliant!! I used this method to update a stored procdure call based on the values inserted into a couple of spreadsheet cells. Many thanks :)

@ExcelOffTheGrid

Жыл бұрын

Nice work! I’m glad you could put it to good use.

The best and simple way!

@ExcelOffTheGrid

2 жыл бұрын

Thank you 😀

Just what I was looking for! Thank you!

@ExcelOffTheGrid

Жыл бұрын

You are welcome!

Mark - thank you for these videos. They are really extremely well explained and usefull!! I've implemented Your tricks without any problems (works like a charm) and really appreciate for Your help. Well done! Jarek.

@ExcelOffTheGrid

Жыл бұрын

Thanks Jarek I appreciate that. I’m glad you’ve found them useful. 👍

Thank you, thank you, thank you! I have been looking for this all week!! So simple and useful - thanks a billion :D

@ExcelOffTheGrid

3 жыл бұрын

No problem, glad I could help 😀

@paulv3216

Жыл бұрын

@@ExcelOffTheGrid This is great information. Can you show how to do this for a SQL statement? Eg. I want to just update 1 value in SQL without having to open the editor for each query. So my SQL statement might say where value=100 and I want to update them to say where value=200 using this method or something as simple.Thanks.

Exactly what i need for my current project requiring flexibility for the file source. Thanks

@ExcelOffTheGrid

2 жыл бұрын

You're welcome. I love a bit of flexibility.

Very good explanation. Very useful!

@ExcelOffTheGrid

3 жыл бұрын

Thanks OZ :-)

It worked! Awesome! Will be using this with PowerAutomate Desktop, where PowerAutomate Desktop will be changing the paths and filenames based on another Excel file list.

@ExcelOffTheGrid

Жыл бұрын

Yes!!! That’s exactly what I do 😁

Great video!!! I use only to point to the folder path and that was exactly what I was looking for!

@ExcelOffTheGrid

3 жыл бұрын

Excellent news :-) Glad you liked it.

This is simply a great method.

@ExcelOffTheGrid

2 жыл бұрын

Thank You 😊

Pure gold!! I have used your tutorial video to figure out how I can update a query web path name using a similar method. Now my data table changes based on the stock symbol entered without the limitations of the office365 data types. Thank you!

@ExcelOffTheGrid

2 жыл бұрын

Great news. Good work 👍

@NICENICENICENICENICE

Жыл бұрын

How? i still get errors

Best Solution. No firewall formula error

@ExcelOffTheGrid

3 жыл бұрын

I hate that error! Glad I could help.

This is great! I’m going to use this at work right away!

@ExcelOffTheGrid

2 жыл бұрын

Great news... have fun!! 😀

Thank you. A very useful trick.

@ExcelOffTheGrid

4 жыл бұрын

Glad it was helpful! :-)

Fantastic explanation !!! Tks

@ExcelOffTheGrid

3 жыл бұрын

Thanks - I’m glad I could explain it well enough. 😀

Big thanks Mark.

Brilliant. Really helpful and easy to understand

@ExcelOffTheGrid

2 жыл бұрын

Thanks Mark, I'm glad it was helpful 😀

I liked your video. That was exactly what I was looking for. Thanks!

@ExcelOffTheGrid

3 жыл бұрын

Great, I'm glad it helped :-)

Fantastic work! Thanks for sharing :)

@ExcelOffTheGrid

2 жыл бұрын

Thank you! Cheers! 😀

Thanks so much - very helpful.

@ExcelOffTheGrid

2 жыл бұрын

You're welcome! Glad it helped 😊

Thank you, thank you and thank you!

@ExcelOffTheGrid

2 жыл бұрын

No problem, glad I could help 😀

Thank you, really useful !!! I applied it to Dynamic Web Query using dynamic URL based on cell values and it works too.

@ExcelOffTheGrid

2 жыл бұрын

Well done - good work. The principles apply to so many areas.

Thanks a lot for most useful PQ trick 👍 Very well explained 🙏🙏🙏

@ExcelOffTheGrid

2 жыл бұрын

Thank you. I hope you can put it to good use 😀

Amazing! Thank you a lot!!!

@ExcelOffTheGrid

2 жыл бұрын

You’re welcome, I’m glad you found it useful.👍

Thank you for the awesome video! It's very very helpful

@ExcelOffTheGrid

2 жыл бұрын

Glad it was helpful! 😊

Awesome, very simple and usefull

@ExcelOffTheGrid

3 жыл бұрын

Glad I could help :-)

You made my day, thanks and keep posting something new !! :)

@ExcelOffTheGrid

2 жыл бұрын

Thank You. Glad I could help 😀

Works well. Thanks

@ExcelOffTheGrid

3 жыл бұрын

Thanks Mario 👍

Very useful, thank you very much 🙏🙏🙏

@ExcelOffTheGrid

Жыл бұрын

You’re welcome. It’s a really useful technique.

That was awesome bro!

@ExcelOffTheGrid

3 жыл бұрын

Appreciate it! :-)

It's awesome.. More videos needed In power query topic

@ExcelOffTheGrid

3 жыл бұрын

Yes, there will be more videos in Power Query coming. You can also check out my blog for more Power Query content - exceloffthegrid.com

Very Impressive!

@ExcelOffTheGrid

3 жыл бұрын

Thank you 😊

Parabéns, excelente conteúdo, Já fiz a minha inscrição. Muito obrigado, sanou uma duvida monstro que tinha.

@ExcelOffTheGrid

2 жыл бұрын

Ótima notícia, fico feliz em poder ajudar. (using Google Translate)

good stuff. Thanks for sharing.

@ExcelOffTheGrid

Жыл бұрын

Thanks for watching!

Thanks a lot!

@ExcelOffTheGrid

2 жыл бұрын

No problem 👍

Brilliant, thank you for the simple but most effective method of explaining how to do this, I already have many ways of using this to create great reports.

@ExcelOffTheGrid

3 жыл бұрын

Fantastic news - I’m glad you could put it to immediate use. 👍

@daytonrowen4515

2 жыл бұрын

i realize it is kinda off topic but do anyone know of a good site to stream newly released series online ?

@kartermorgan5795

2 жыл бұрын

@Dayton Rowen Flixportal

@daytonrowen4515

2 жыл бұрын

@Karter Morgan Thanks, signed up and it seems like a nice service :) I appreciate it !

@kartermorgan5795

2 жыл бұрын

@Dayton Rowen You are welcome xD

Thanks a lot 😊

@ExcelOffTheGrid

2 жыл бұрын

No problem, glad I could help. 👍

Nice & Easy

@ExcelOffTheGrid

2 жыл бұрын

Thanks, I try to make things easy (it doesn't always happen though 🤪

Thank you so much! I needed a method to change what the source was at a certain time every night because the live source defaults to the wrong day for a few hours every night. I initially tried a “try/otherwise” method, but it still got an error (“couldn’t convert to number”). This worked great!

@ExcelOffTheGrid

Жыл бұрын

That sounds like a really annoying scenario. I’m glad I could help out.

thank you! I have 6 files that I have to import to power query, and have to this manually for the last 4 weeks now. The only difference is there's a diff folder each day, folder names are named after the date ie. 070722. This code makes it easy.

@ExcelOffTheGrid

2 жыл бұрын

Great news, I'm glad it helped.

Thank you. it's very helpful. This method also could apply to get data from the web, with parameter/source from cell. I've tried it. Thanks.

@ExcelOffTheGrid

3 жыл бұрын

Good news, I’m glad it worked 😀

Nice Sir

@ExcelOffTheGrid

4 жыл бұрын

Thanks Madhu - I'm glad you liked it.

There are multiple ways of soing this. This is the 3rd I’ve seen. Maybe the simplest. Another nice one was a custom M function that returned parameter values based on name. The source was a table in the worksheet.

Hi Mark. Great video! Question: what if you have multiple files that you use inside your Power Query ( I currently have three)? May I just define the folder instead and not define the file(s)? Thank you and keep up the great work!

@ExcelOffTheGrid

3 жыл бұрын

In Excel go to: Data > Get Data (drop down) > From File > From Folder. Check out this post: exceloffthegrid.com/power-query-import-all-files-in-a-folder/ This will enable you import files in a folder. Once you've created the query, you can apply the same principles as the video to use a cell value to point to a folder (rather than files).

@WJA5

3 жыл бұрын

@@ExcelOffTheGrid Thank-you Mark...I've tried the steps you mentioned, however I get the error "Expression Error" We Cannot covert a value of type Record to type List. Details: Value = [Record] Type = [Type]. Note: I am Merging files from Folder and not Appending. Thanks Again!!

@ExcelOffTheGrid

3 жыл бұрын

@@WJA5 I’m not sure why you are merging when combining files from a folder. I might be misunderstanding something. I don’t have a video for this technique, but you can check out this post: exceloffthegrid.com/power-query-import-all-files-in-a-folder/

I've looking for how to do this. Thank you.

@ExcelOffTheGrid

3 жыл бұрын

Glad to help :-)

@prajac

3 жыл бұрын

Can you do something on XML data sources. I'm having so much difficulty trying to remove XML tags

Thank You so much for the simple explanation, Can you please explain a little bit the explanation of the M code, or point me to where can I understand it ?

@ExcelOffTheGrid

2 жыл бұрын

To fully understand M code takes quite a bit of learning. There is a great resource by Ben Gribaudo which gives a solid introduction to the language. bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressions-let

Hi Mark, what tweaks would I need if the source is a PDF?

Brilliant idea. Thanks so much for sharing. How about if the data source is a SQL Server instance name? Can I use the same method in the video?

@ExcelOffTheGrid

2 жыл бұрын

Yes, this approach should work with any text string within the M code.

@Harziona

2 жыл бұрын

@@ExcelOffTheGrid Thanks so much. Yes, it works.

Hi nice explanation. And how about changing the SQL statement parameter based on cell value in Power Query?

@ExcelOffTheGrid

3 жыл бұрын

Not being an SQL guy myself it’s difficult to answer. Presumably the SQL is a shown as a text string within the Advanced Editor, therefor the principles from the video would be similar. But in the M code you would replace the SQL statement rather than the file path.

Will this method work if I’m trying to pull a dynamic table name out of a static access database? The access database will not change but the table I’m pulling from will. All tables being dynamically pulled would have the same fields but different data?

@ExcelOffTheGrid

Жыл бұрын

Sorry, but I’ve not used it with an access database. Try it, and see.

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?

@ExcelOffTheGrid

2 жыл бұрын

Wyn Hopkins in the Access Analytics KZread channel has some videos covering that. You should check that out.

What if you want this to point a table on a separate tab in the same workbook? Anything special that I should be aware of?

@ExcelOffTheGrid

3 жыл бұрын

To use a table, everything is the same, except 1 thing. Instead of [Column1], you'll need to use the name of the column header from the table.

Thanks. This was a really useful video especially using named ranges. However, how do you solve the resulting Privacy error that occurs thereafter without setting the privacy settings to 'Ignore All'?

@ExcelOffTheGrid

3 жыл бұрын

That issue is because external queries with different privacy setting are both trying to land data in the same query. The best way to solve this is structurally. Have queries which import the raw data (load as connection only), then staging queries which manipulate the raw data query (load as connection only), then have final queries which provide the final format for loading the data to the Worksheet or Data Model. This also helps to manage queries in a structured way.

Hello, excellent video has been looking for this solution for a long time. Is this method possible now between an xlsx file and an odbc connection? Thank you

@ExcelOffTheGrid

3 жыл бұрын

I'm not sure to be honest. As I've not tried it with an ODBC. If the M code displays the the ODBC parameters separately then, Yes, I expect so. But you might encounter the Formula.Firewall error. If you do, you'll need to change the the Privacy Settings.

@luisganhao1316

2 жыл бұрын

@@ExcelOffTheGrid Thank you very much, i´l trie

Thanks can you please show the same for a SQL

@ExcelOffTheGrid

2 жыл бұрын

Sorry, SQL isn't my thing. 🤔

Im trying this for a Web query as i need to change the last letters of the URL: i saw (when dividing it in parts) that it does divide strings like this: ...page/catalogue/2022" & "/" & "January") i tried pasting my variable "month" like this: ... ...page/catalogue/2022" & "/" & Month) but it shows an error regarding a key not corresponding to a row in the table. Can someone help me find a solution? I'm new to excel so if im doing something really wrong im sorry 😅

I need help on changing column name on daily to filter, when i first did my filter on my source column name is [xxx 25/10/2020] and filtered with "5" next day my column name is changed to [xxx 26/10/2020] when i refresh, it shows error like column is not found. Note: i dont want it to rename, i need source header as it is in output as well. I tried using table.columnnames(source){1} to filter based on column index instead of static column name as [xxx 25/10/2020] however, after refresh, it shows table is empty. There is no error but data is not getting populated. I need support to fix this.

Hello Mark, Is it possible to update the power query name based on a cell value? I see that the step of renaming the power query is not recorded. So, is there a way or an option to update the power query name based on a cell value?

@ExcelOffTheGrid

3 жыл бұрын

Not that I'm aware. If you're using the data model in Excel, then changing names becomes a pain. So I'm not something you would want to do.

This is super useful, but I'm wondering how we would do this with SharePoint files? Is there a way to have a dynamic cell in Excel that calls to a SharePoint Folder path?

@ExcelOffTheGrid

5 ай бұрын

Yes that is possible. Here is how to connect to the file: kzread.info/dash/bejne/fHiYzKaQisfefKg.html Then apply the same techniques as this video to get the dynamic path.

@CuriousProfessional

5 ай бұрын

Thank you so much Mark, you’re amazing!!!

I did not like it.... I L O V E D it.... so useful...... Thanks.... Do you have something similar for Access, linked table manager?

@ExcelOffTheGrid

Жыл бұрын

Sorry, I’ve not used Access for about 20 years. Might be worth checking out this video for some more technical considerations: kzread.info/dash/bejne/kYGo19OIaabQYag.html It might help you out to apply it to other situations.

thank you for the tutorial! I got one file to work but adding a second file and second set of references is failing :( so I am building a comparison PQ of current month data to prior month data and it's a ton of data :) I just need to be able to import 2 files by updating the file path references and then comparing these two files which I can do but creating a second import is troublesome

@ExcelOffTheGrid

2 жыл бұрын

You can’t use the same names, but apart from that it, should be the same process.

@tsvetelinalalova9036

2 жыл бұрын

@@ExcelOffTheGrid Do you mean that you can't use the same name for the cell that is named FoderPath? What about for the begging of the code in the advanced editor where we must write FilePath=...Should it be changed?

@ExcelOffTheGrid

2 жыл бұрын

@@tsvetelinalalova9036 The name of each step must be unique within the query. So if you've already used: FilePath=..... Then you need another name, such as FilePath2=.....

@tsvetelinalalova9036

2 жыл бұрын

@@ExcelOffTheGrid Thank you. It works. I made three folder paths and named them "FolderPath", "FolderPath2", and "FolderPath3", and called the cells where it is placed the Folder paths in the same way. I made three queries from three folder paths and merged them in some stages in the data processing. Now I have the result I have wanted to achieve. I have only one idea - to set the formula in the cells where the folder path and when I put the file in a particular folder formula to generate the folder path, and there isn't any need to paste a link with the folder path.

how can I do this with filepath and filename in a separate excel sheet? (rather than the current one)

I want to do it in my query, but the issue is that my table path is actually web path. So it is web query, is there a way to do the same for web query as well

Thank you for a good video. Unfortunately I cannot make it work, when I want to reference to a folder of files (Only FilePath)? Can you help?

@ExcelOffTheGrid

3 жыл бұрын

The methodology is the same for a file or a folder. The M code will create a text string. That's the part of the code you would replace.

This is great. What about if the file is located on SharePoint?

@ExcelOffTheGrid

3 жыл бұрын

This method works with any parameters, it’s not specific to File Paths. So, just replace the text string containing the SharePoint file path.

@iankr

3 жыл бұрын

@@ExcelOffTheGrid Thanks

@ExcelOffTheGrid

3 жыл бұрын

@@iankr No problem 👍

Hello Mark, what will be the code if i have to define a variable based on a cell value in the current workbook for a hardcoded text value for ex: some filter value which is hard coded but I would want to define a variable with a cell reference for it in my excel sheet what will be the exact code to define it? Thanks for your reply in advance

@ExcelOffTheGrid

3 жыл бұрын

It’s the same process, but instead of replacing the hard coded file path, you replace the hard coded filter value.

@rahulkalingeri1206

3 жыл бұрын

@@ExcelOffTheGrid Thanks a lot It worked :-)

I understand that it works only for the current workbook with the query.. What if am querying in a file which is connected to another database file? The database file is my source file. Is there any changes in M code when I change the database file into another location?

@ExcelOffTheGrid

3 жыл бұрын

You need to start with the query already operating correctly (i.e. linked to whatever the original source is). The code for that query will have a hardcoded value. Use this technique to replace the hardcoded value with a cell link. Provided your original query works, then this technique should work. If you've got a different scenario then it sounds like you'll need to play with it, to see what it can do.

@Sri-Nivas

3 жыл бұрын

@@ExcelOffTheGrid I will try it and let you know.. 😊

What if we have multiple file names that need to load all of them into multiple tabs

@ExcelOffTheGrid

8 ай бұрын

Try this… kzread.info/dash/bejne/ZGei186AorCocbA.htmlsi=bCC0Ck7AnjmSd5Vw

How can you auto refresh after you change the File Path?

@ExcelOffTheGrid

Жыл бұрын

I’ve got a video here that explains how to do it. kzread.info/dash/bejne/Y6d4waqIiJO4g84.html

If the Power Query already has these as query Parameters is there a way of feeding the Parameters from a cell?

@ExcelOffTheGrid

2 жыл бұрын

There is no way to do a direct switch. You will need to switch it over in the M code so that everything points to the new parameter.

@frankief7111

2 жыл бұрын

@@ExcelOffTheGrid Thanks. I guess need to think about when to use meta parameters and when to use this named cell technique. When using with Power BI parameters are nice.

How to select multiple files

How can I change the code if my source data is not in Table format?

What if my data source is a web link? Will it work then?

Then how about we make the file name on drop down list? is that possible?

@ExcelOffTheGrid

Жыл бұрын

Yes, you can. Just add a named range to the cell with the data validation drop down list.

If you would want to add a browse button right beside the cell that contains the file path so that you won't need to manually copy and paste the file path in the cell, how you would do that?

@ExcelOffTheGrid

3 жыл бұрын

To do that you should use a macro which is triggered by a button or picture. Check out the VBA code here: exceloffthegrid.com/selecting-file-using-filedialog/

@ExcelOffTheGrid

3 жыл бұрын

Actually, I think I’ll create a separate video in this, as it’s a question that I’m sure lots of people will have.

@mariansdraila

3 жыл бұрын

@@ExcelOffTheGrid I already found the answer to my question but it turns out that for my case scenario it's actually better to have the file path relative to the file I'm working on! I already did that but both scenarios are good material for a video tutorial!

@ExcelOffTheGrid

3 жыл бұрын

@@mariansdraila My video maybe a few weeks away yet, so I’m glad you’ve got and answer already. Hopefully it will be useful to others 😀

What about getting the sheet name based on a cell value?

@ExcelOffTheGrid

3 жыл бұрын

The method is exactly the same. But rather than replacing the hardcoded file path in the M code, replace the hardcoded sheet name.

I get the following error: Expression.Error: The import Column1 matches no exports. Did you miss a module reference?

Does this work for web links?

@ExcelOffTheGrid

2 жыл бұрын

Yes it can work with web links, but you’ll probably need to deal with the Privacy settings and Formula.Firewall error.

@wiggumo

2 жыл бұрын

@@ExcelOffTheGrid thanks. So I've got an issue I'm using a table with 2 columns, one with a list of URL with the data source to these URL Is a csv. The issue I'm having is that if you change these URL, it's coming up with error message because it is keeping the headers of the first URL I did originally! What can I do to fix it? And I can't attach images here unfortunately

Trying to do this with a MDB file, sort of the same thing, but not getting it to work. any chance of a pointer to help?

@ExcelOffTheGrid

2 жыл бұрын

Hi Jim - Sorry, it's not something I have tried. It's been a while since I have used Access. Is all the information you need to connect to Access included in the text string in the PQ Advanced Editor?

@jimriseborough

2 жыл бұрын

@@ExcelOffTheGrid thanks. I’ve sorta got it working now

Why did we have to separate the folder path and the filename? What if we tried to enter them together, at once?

@ExcelOffTheGrid

2 жыл бұрын

Yes, you can do it all in a single cell. I just happened to set up the example with two separate elements 😀

@lisaschlukebir7669

2 жыл бұрын

@@ExcelOffTheGrid would you be able to show an example of this? I am trying to do so but then the next applied step for finding the sheet name within the file is giving me "Expression.Error: The key didn't match any rows in the table".

What if your Query pulls data from a data base and you want to be able to change databases

@ExcelOffTheGrid

2 жыл бұрын

Is the database name is contained in the M-code as a string within the advanced editor? If so, then I think this should be possible using this technique.

If i need more than one excel file in folder ???

@ExcelOffTheGrid

2 жыл бұрын

If you want all the files in a folder then use the Data > Get Data > From Files > From Folder method, then you only need a single parameter. Alternatively, you could: - Use the From Folder method - Create a separate Table with the file names listed - Apply a merge transformation which only keeps the files listed in the folder.

Hey I'm having trouble with this, even though i've triple checked and everything matches, every time i try to refresh, i get this error: "We couldn't find an Excel table named 'FileName'." Has anyone else run into this issue?

@ExcelOffTheGrid

2 жыл бұрын

It's likely there is a typo, or something hasn't been setup in the right way. I suggest deleting the steps and trying again. It should work, I did it yesterday :-)

if my FilePath are numbers, how can i insert them?

@ExcelOffTheGrid

3 жыл бұрын

You'll need to change the type of the input to be a decimal / integer. You should be able to steal the code from another change type step that you've got else where.

I am getting the could not find the file.. The file Path and file name are correct.. How can I fix it?

@ExcelOffTheGrid

2 жыл бұрын

Have you got a slash at the end of your file path? That is the most common error.

@devendrakoli9609

2 жыл бұрын

@@ExcelOffTheGrid I have checked.. It works for other files but I am getting error in some.. I have found some other alternative.. Thanks

I am getting this error: DataFormat.Error: The supplied file path must be a valid absolute path

@ExcelOffTheGrid

2 жыл бұрын

It’s because the file path you’ve given isn’t to a valid. Have you forgotten to include a slash at the end of the file path?

Well it kind of works. When I set it up it works. To avoid confusion, I use GetFolder as my named range. When the content of GetFolder changes it value (a new folder path) , Power Query don't register it....not even if I update all. The DataSource don't update to the new value at all...weirdest part is, if I open advanced editor and just briefly change GetFolder to Getfolder2 in FilePath = Excel.CurrentWorkbook(){[Name="GetFolder"]}[Content]{0}[Column1] I, of course get an error at first, but THEN when I the change it back to GetFolder, the new value is now registered. WTF!? Why can't it just do that initiarially, I really don't understand. It's like the Query can't update it's data source value automatic. I kind of ruins the whole purpose of doing this if the doesn't help the user. It's really not a bonus if I have to explain the users, that they just have do open this and this and enter advance editor and just change the Name="GetFolder" briefly and then back. It's REALLY annoying!!!! Can anybody pleeeeease explain this for me?

@ExcelOffTheGrid

Жыл бұрын

What you’re trying to do definitely works, I use it often. In the Source step, are you using the Folder.Contents or Folder.Files transformation?

@kennethfrandsen7187

Жыл бұрын

@@ExcelOffTheGrid I'm not sure of what you mean (I'm quite new at this) I think it contents. It doesn't say in the code, but in the GUI I can see that the first column is Content (Headline) and the rows says Binary... But the AE just show this. Is this what you mean? FilePath = Excel.CurrentWorkbook(){[Name="GetFolder"]}[Content]{0}[Column1], Source = Folder.Files(FilePath), The rest is in danish (to tired now to translate it) #"Skjulte filer er filtreret1" = Table.SelectRows(Kilde, each [Attributes]?[Hidden]? true), #"Aktivér brugerdefineret funktion1" = Table.AddColumn(#"Skjulte filer er filtreret1", "Transformér fil fra Månedlige csv (2)", each #"Transformér fil fra Månedlige csv (2)"([Content])), #"Omdøbte kolonner1" = Table.RenameColumns(#"Aktivér brugerdefineret funktion1", {"Name", "Source.Name"}), #"Fjernede andre kolonner1" = Table.SelectColumns(#"Omdøbte kolonner1", {"Source.Name", "Transformér fil fra Månedlige csv (2)"}), #"Udvidet tabelkolonne1" = Table.ExpandTableColumn(#"Fjernede andre kolonner1", "Transformér fil fra Månedlige csv (2)", Table.ColumnNames(#"Transformér fil fra Månedlige csv (2)"(#"Eksempelfil (2)"))), #"Ændret type" = Table.TransformColumnTypes(#"Udvidet tabelkolonne1",{{"Source.Name", type text}, {"Dato", type date}, {"Tekst", type text}, {"Beløb", type number}, {"Saldo", type number}}) in #"Ændret type"