Forum Discussion
Automation from a folder.
Below is how you can fix.
The #"Expanded Table Columns1" step refers to your sample file.
You can replace that reference to a reference to the field created earlier representing your XLS file.
I commented my changes. You may have to adapt to your own situation....
let Source = Folder.Files("C:\ y"), #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "2024")), #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}), // --> This is causing the error: #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))), // Replace with #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Removed Other Columns1"{0}[Transform File])), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Name", type text}, {"Data", type any}, {"Item", type text}, {"Kind", type text}, {"Hidden", type logical}}) in #"Changed Type"
Hi David,
My answer would depend on whether you are:
a) Overwritting old files. In this case, as long as you keep your formatting EXACTLY the same, your transformations should be fine. However, that is easier said than done, especially if someone is hand jamming Excel sheets. I recently had some putting notes to themselves off to the side of a previously established format.
b) Adding new files with NEW data only that need to be appended to your existing data. e.g. You have a file for October's data already in the folder and now you're adding a file with just November's data. Or...
c.) Adding new files that have previously existing data and new data. e.g. You had a file in this folder with October's data in it and are now dropping in a file with October and November's data in it.
For general steps, I would:
Click Get Data>Folder
Once you navigate to the folder and are in power query, you will see all of your files listed in one field, along with extension type, date accessed, date modified, date created, attrubtes, and folder path.
The last time I had a project using folders like this, someone was putting a new file in daily called "DataDump" with the current date. e.g. "DataDump10.10.24, "DataDump10.11.24," and so on. These files had all the data gathered to date, so I only needed the most recent copy.
Next, filter the "Name" field by "Starts With" and enter the beggining of your file name. In my example I filtered by "Starts With: DataDump" because I had other files for other tables being dropped into the same folder.
Then, sort descending by Date Created. This brings the most recently created filter to the top row.
Now, click "Keep Rows" and keep the first row only.
The correct file should be the only one left. In the field "Transform File," click the two arrows that are pointing away from each otehr to exapand the file and you should now see all the columns from your file. Do transformations as normal from here.
Hi SGroff, thanks for the reply.
I am d) deleting the existing (used) files, they have served there purpose and am replacing them with new files with new data. These new files have exactly the same format as the old files but different names such as blah - blah - blah K04000247. The last segment is sequentially generated by Microsoft Navision as and when I create a Local Campaign.
As the query to transform and combine the 30 sheets (10 files * 3 sheets) was first carried out on '...K04000180' deleting this file from the folder causes the query to fail as it wants to follow the steps it carried out on 'K...180' which no longer exists. I managed yesterday to 'Macgyver' a workaround by manually changing every refernce to 'K...180' with a file name that was ectually in the folder. This worked but is not ideal.
Would the query hold if I started from scratch with a 'Transform File', never deleted it and then, as you and others have suggested, filter it out as a final step in the query.
Regards
David W
From what you're describing I don't think it matters whether you delete it or not. When you say 10 files, do the ten different file names start with something distinct? The "blah-blah-blah" portion of the file names? You could do seperate queries where you filter for Starts With "blah-blah-blah A," "blah-blah-blah B," and so on. That way, the generated K# that comes after doesn't affect your query.
