Hi Alan.. love this video. Very clear and concise. I agree with Bart below.. nice tip on checkbox to ignore errors. I'll remember that one. Thanks and Thumbs up!
F1 F1 please i'm using power query (import form file) for connect between 4 excel file. (A,B,C,D). result of A send to B and consequence send to C and D. user B refresh data to catch new data from A and after add new field send to C. (this is a product line in a factory) but sometimes when user B going to save data occured to error (someone else is working in "file name" right now. Please try again later.)
Thank you very much for effort, really useful video.. I want to import the data from folder same as above in video but from PDF file to excel trying to look for the clip on UA-cam but couldn't find any... please help
Hi, Alan, thanks a lot for your excellent videos. Do you know how to solve the "operation has timed out error" when downloading data using power query?
Hey There. I kept 3 files in the my folder. After locating the folder location, I can not see edit but I found transformation. Further from transformation, I can see only the first file and other files are not uploading in the query. Need your suggestion.
By checking the skip files with errors, you refresh all other data sheets but you DO NOT refresh the sheet that is open. So the issue there is some elements of the report will show old data not new data. Is that correct? Thank you
HI Alan this is a great video since this is a common issue that we face when importing several file from a folder , but i have another question here.. what about if one or more HEADERS from some of your files are in Germany.. or portuguese languague? and the order of the columns are different but the content of the table is the same as the one you just showed in this video? how to solve that problem? besides this what happen if tomorrow the table increase the |# of columns?
I have an excel file with power query which was saved to folder on laptop. Now the files have been moved to a folder in SharePoint 2016 and the queries have yellow explanation mark due to wrong source. I am not able to change the source to a SP folder as it wont accept url address .... how do i fix the problem?
I have a folder containing 19 Excel worksheets with each worksheet containing 16 sheets inside it. While the name of the excel files are different, the name of individual sheets inside them is same i.e. they start from "Sheet 1" and go till "Sheet 16". What I have to do is append each sheet at the end of the next one and continue the same to get a new excel file containing only one sheet. (In other words, sheet 1 from file two (say, B) will be appended at the end of sheet 1 from file one (say, A) and sheet 1 from file three (say, C) will be appended at the end of the above two files containing sheet 1 from files A and B, respectively and so on untill every file is added).
Hi, great tutorial. However, I would like to load the data into an existing sheet, but the option is greyed out. What could it be? The source is a Folder.
good day, i have a problem of importing files (with inconsistent number if columns) in a folder but it ends up with Error File 1 - contain date, Sales, File 2 - Contain date, Sales Rep can you advise me how to. If i import separately by table, it is ok.
Hi. Tried this with three files. The problem is each file has a row at top I first need to delete, each file then has in the second row same headers. So how do I tell Query to first do the first two steps for each individual file before combining. If I follow your steps in this video, I get all files combined, but in between I have rows with the headers. I first need to delete certain rows and promote headers in each before combining.
Hi is it possible to query all excel files with in a folder which is located in SharePoint / OneDrive. I can´t work with the "C\Users\xxxx\OneDrive...." Directory. Because if you start working in Team, you´ll get an Error. Does someone have an Idea? Thanks in Advance!
How can I use Power Query using a relative folder path? My files are stored/synced using OneDrive for business so the beginning part of the folder path is different on each computer I use. Is there a way to resolve this so my power query will work on all my computers?
Hi Alan how do import all those files in power query without appending them? I.e. I would like to have those files uniquely in PQ where I can create a master file and create relationships
can you guide me how to merge two folders data from diff workbooks already been loaded. For Example. Multiple Excel Files from a Folder 1 using Power Query and Multiple Excel Files from a Folder 2 using Power Query. Want to merge them in another file dynamically.
Sir I m trying it but whenever i click on the on double arrow as you show there is an error "data format error external table is not in the expected format" I found this error I will be highly thankful to you if you solve this problem
Hi Alan, I tested it. Does not work. Please investigate and tell us a way to do that. My individual sheets have sensitive information and has passwords
Here’s my dilemma. I export income statement data from our ERP on a quarterly basis. The rows are GL accounts and the columns are departments. As our company has grown, we have added departments so the older excel files have fewer columns than the newer files. My workflow has been to import the data, select the first 2 columns (Period and GL account), then “unpivot other columns.” This leaves me with 4 columns: Period, GL account, Department, Amount. After the unpivot, I append the file to my merged table. When I try to automate the import by telling Power Query to import and transform all files in a folder, it fails because the files all start out with a different number of columns prior to being “unpivoted.” Is there a way I can how Power Query import, unpviot, then append each table AFTER the unpivot?
Hi Jason, you can perform the unpivot step on the 'Sample File' to include it in the process before combining. Check this does not break anything in the main query such as headers. When you say it fails. Do you get an error or just a bad result? I hope this helps. Give me shout.
Thank you Allan, well done. Nice tip for the checkbox " ignore errors" !!
Thanks Bart.
You Saved my life with this easy straight forward steps, Thanks a million
Great to hear. No worries.
Many thanks for the share. A most useful query to address the some of the issues with updates in folders.
Thank you.
Hi Alan.. love this video. Very clear and concise. I agree with Bart below.. nice tip on checkbox to ignore errors. I'll remember that one. Thanks and Thumbs up!
Thank you Wayne.
Great presentation! Very useful, many thanks!
Thank you Muuip.
F1 F1 please
i'm using power query (import form file) for connect between 4 excel file. (A,B,C,D). result of A send to B and consequence send to C and D.
user B refresh data to catch new data from A and after add new field send to C. (this is a product line in a factory)
but sometimes when user B going to save data occured to error (someone else is working in "file name" right now. Please try again later.)
Very helpful video. Thank you.
You're welcome👍
Great video. Easy to follow thank you so much !
You're welcome. Thank you.
amazing tip and video. Very apreciated!
You're very welcome. Thank you.
Thank you very much for effort, really useful video..
I want to import the data from folder same as above in video but from PDF file to excel trying to look for the clip on UA-cam but couldn't find any... please help
I can't understand where are you clicking, and which option you are selecting, please show your mouse moves.
Very helpful, thank you.
My pleasure Kevin.
Good Day, thanks for clear explanation. I find some missing pieces of puzle in this video. Thanks!
You're welcome Aleksandrs. Thank you.
Thank you!
You're welcome!
Very helpful.
Great to hear.
Hello Sir I am getting error after clicking double arrow at content stating the file is not at expected excel format
This was superb. Thank you
Thank you 😊
Hi, Alan, thanks a lot for your excellent videos. Do you know how to solve the "operation has timed out error" when downloading data using power query?
Great Video! Is it possible to import the data to individual sheets, i.e. File1 = Sheet1, File2 = Sheet2 etc.
hi,thanks,can i add URL as CUSTOM column in my Query in each Row
Hey There. I kept 3 files in the my folder. After locating the folder location, I can not see edit but I found transformation. Further from transformation, I can see only the first file and other files are not uploading in the query. Need your suggestion.
how do you load the two files to PQ? I got errors
By checking the skip files with errors, you refresh all other data sheets but you DO NOT refresh the sheet that is open. So the issue there is some elements of the report will show old data not new data. Is that correct? Thank you
Hi. I have error message "initialization of the data source failed" after I click load data. thanks
Can you have/ use multiple sheets in this Query, if yes, how do you add another sheet?
HI Alan this is a great video since this is a common issue that we face when importing several file from a folder , but i have another question here.. what about if one or more HEADERS from some of your files are in Germany.. or portuguese languague? and the order of the columns are different but the content of the table is the same as the one you just showed in this video? how to solve that problem? besides this what happen if tomorrow the table increase the |# of columns?
I have an excel file with power query which was saved to folder on laptop. Now the files have been moved to a folder in SharePoint 2016 and the queries have yellow explanation mark due to wrong source. I am not able to change the source to a SP folder as it wont accept url address .... how do i fix the problem?
I have a folder containing 19 Excel worksheets with each worksheet containing 16 sheets inside it. While the name of the excel files are different, the name of individual sheets inside them is same i.e. they start from "Sheet 1" and go till "Sheet 16". What I have to do is append each sheet at the end of the next one and continue the same to get a new excel file containing only one sheet. (In other words, sheet 1 from file two (say, B) will be appended at the end of sheet 1 from file one (say, A) and sheet 1 from file three (say, C) will be appended at the end of the above two files containing sheet 1 from files A and B, respectively and so on untill every file is added).
very very helpful thx
Great to hear. Thank you.
Thanks
You're welcome, Salvador.
I Try its worked in desktop, but unable to load in network drive the using the power query
Thanks to this video, I completed my project, thanks lot !
You're welcome, Eda.
Does google share have this function?
Hi, great tutorial. However, I would like to load the data into an existing sheet, but the option is greyed out. What could it be? The source is a Folder.
good day, i have a problem of importing files (with inconsistent number if columns) in a folder but it ends up with Error
File 1 - contain date, Sales, File 2 - Contain date, Sales Rep
can you advise me how to. If i import separately by table, it is ok.
1.35x the ideal speed
which i try in desktop the excel power query , unable to move it in network drive
Hi. Tried this with three files. The problem is each file has a row at top I first need to delete, each file then has in the second row same headers. So how do I tell Query to first do the first two steps for each individual file before combining. If I follow your steps in this video, I get all files combined, but in between I have rows with the headers. I first need to delete certain rows and promote headers in each before combining.
I have your message Chris. I'll get back to you soon.
At 3:33, I can't quite catch what you clicked on to "double expand those in this editor."
At that time I clicked on the Ok button in the window
Should you safe the workbook with the query in the same file location as the other data files? Or is it best safe in a different location?
*save
Different location is best. It will import all file from that folder, unless you filter out unnecessary files.
Hi is it possible to query all excel files with in a folder which is located in SharePoint / OneDrive. I can´t work with the "C\Users\xxxx\OneDrive...." Directory. Because if you start working in Team, you´ll get an Error.
Does someone have an Idea?
Thanks in Advance!
I am looking for the same answer
How can I use Power Query using a relative folder path? My files are stored/synced using OneDrive for business so the beginning part of the folder path is different on each computer I use. Is there a way to resolve this so my power query will work on all my computers?
I have not done this before Anthony. An interesting scenario to look into. I hope you are able to solve this issue.
Hi Alan how do import all those files in power query without appending them? I.e. I would like to have those files uniquely in PQ where I can create a master file and create relationships
Good question Roderigo. I do not know at this time.
can you guide me how to merge two folders data from diff workbooks already been loaded.
For Example. Multiple Excel Files from a Folder 1 using Power Query
and Multiple Excel Files from a Folder 2 using Power Query.
Want to merge them in another file dynamically.
Good idea. We can reference queries in Power Query. I think I will plan a video on this.
Sir I m trying it but whenever i click on the on double arrow as you show there is an error "data format error external table is not in the expected format" I found this error I will be highly thankful to you if you solve this problem
Hi Afzal, there is a solution provided by Maxim Zelensky here - bit.do/pq-error
Hi Alan. Will the refresh button work if the source files are password protected? If not, how do you refresh them?
That is a good question Rajan. I do not know. I would have to test it.
Hi Alan, I tested it. Does not work. Please investigate and tell us a way to do that. My individual sheets have sensitive information and has passwords
Here’s my dilemma. I export income statement data from our ERP on a quarterly basis. The rows are GL accounts and the columns are departments. As our company has grown, we have added departments so the older excel files have fewer columns than the newer files. My workflow has been to import the data, select the first 2 columns (Period and GL account), then “unpivot other columns.” This leaves me with 4 columns: Period, GL account, Department, Amount. After the unpivot, I append the file to my merged table. When I try to automate the import by telling Power Query to import and transform all files in a folder, it fails because the files all start out with a different number of columns prior to being “unpivoted.” Is there a way I can how Power Query import, unpviot, then append each table AFTER the unpivot?
Hi Jason, you can perform the unpivot step on the 'Sample File' to include it in the process before combining. Check this does not break anything in the main query such as headers.
When you say it fails. Do you get an error or just a bad result?
I hope this helps. Give me shout.
i have office 365 on mac and i dont have 'from folder' option
Ah, I don't use a Mac so am not sure where or if this option is available.
Can we do this in vba?
Sure