Hello all,
I've created a query for transactional data that I'm downloading pointing the query to a folder that contains the downloads that are Excel workbooks. The data is not formatted as a table and they are not in named ranges.
Like most data downloads, you can choose the fields you want data for. I've recently increased the columns that I have been downloading as my default setting (this was for another piece of work that required these additional fields). I was wondering if there was a way that I could maintain this default download setting (with the extra fields) but still be able to drop these into the query folder with out getting error messages. As it stands, I've either got to do a new query that allows for this new data download format or change my data download to the original number of columns that the first query was designed for. Any ideas on this one?
Thank you
Tim
Hi Tim,
If you have used the automatic file combiner (From Folder), that method is not flexible.
See this topic for a flexible alternative: https://www.myonlinetraininghub.com/excel-forum/power-query/combine-files-with-different-header-names#p18090
Many thanks for your response Catalin. I'll have a look at this and see how to use it.
Thank you
Tim
Hi Catalin/everyone else
I've had a look at the file that you created but I'm new to PQ so could do with some help please. First of all, would you still use the command, get data--> from folder option? Do we then skip the combine option and just edit the query? What then? The file that you have created looks a bit like what Mynda is doing in lesson 7.03 of the PQ course but I'm not sure how to implement what you are suggesting.
In summary though, once I implement your solution, it will allow different files in a folder to be combined even if they have different column headers? Also, my files are .xlsx. Is that a problem?
Thank you
Tim
Hi Tim,
Please upload some sample files so I can see your structure variations, will send an example of how it should be done.
Yes, the solution allows you to combine files with a different header structure, still based on get data from folder, but more flexible.
Hi Catalin
Thanks for your response. Please find attached the column headers for the data I am currently downloading on the first worksheet and then how they might be downloaded in future on the second worksheet. Thank you.
Tim
I used this function:
(FilePath as text)=>
let
Source = Excel.Workbook(File.Contents(FilePath), null, true),
#"Filtered Rows" = Table.SelectRows(Source, each [Kind]="Sheet"),
#"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Custom", each Table.PromoteHeaders(Table.SelectRows([Data], each [Column3] <>null), [PromoteAllScalars=true])),
Custom1 = Table.Combine(#"Added Custom1"[Custom])
in
Custom1
Note that I added a filter on Column 3 (Period) to remove nulls, this should remove the first rows with extra details.
The folder query:
let
Source = Folder.Files("C:UsersCatalinDesktopData"),
#"Added Custom" = Table.AddColumn(Source, "Custom", each GetFileData([Folder Path]&[Name])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Name", "Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", Table.ColumnNames(Table.Combine(#"Removed Other Columns"[Custom])), Table.ColumnNames(Table.Combine(#"Removed Other Columns"[Custom])))
in
#"Expanded Custom"
The only thing you need to change is the folder path where you keep the files, it's pointing to my computer.
