Automation from a folder.

[David_M_W]

Greeting from the north of Sweden. 

From a folder with 10 Excel files, each with 3 sheets, I have managed to extract, transform and combine the information I require for a table the feeds a weekly report.

In a week from now I would like to replace all of the files with now ones and noe break the query. How would I do that? If I just add the new files the resulting table and report would include two weeks of information, which is not what I requre. 

Is there an obvious way to resolve this that I'm missing. 

Thanks for any tips, links or suggestions.

[PwerQueryKees]

And if you remove your old file anyway, no need to do extra filtering. Just fix your query and you should be good to go...

[PwerQueryKees · Accepted solution]

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"

[David_M_W]

Thanks 

I will give this a try.

[PwerQueryKees]

I think I know what your problem is. You have created your queries through the UI and you now have some helper queries that are giving you trouble.

Something like this!

[David_M_W]

Exactly like this! I seemed to have made a working solution by leaving the helper file in the folder. So I delete 9 of the 10 files when adding the next bundle of files. Then as many suggested I filter out that file I a final step. It seems to be working but I love to learn the M Code way!

[Anonymous]

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.

[David_M_W]

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

[Anonymous]

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. 

[Omid_Motamedise]

By replacing the files and refresh the query, the old data will replace by the new one, so you do not need to do any thing, except you have filtered the files based on the names

[David_M_W]

Hi Omid. I tried this but as the transformations, prior to combining the other 9 files, was based of the first of the first file, the query brakes as it cannot find the first file. Is there a way to have a dynamic naming system implemented so that any file with the same structure is used as the 'base' file. 

I hope this makes sense.

David W

[PwerQueryKees]

Your question is rather general and so will my answer be.

I assume you use something like SharePoint.Folders.

Now you get alll the files and continue.

BUT you can add a filter step in before you actually start processing the contents.

I normally have a naming convention that consists of a prefix followed by a date in YYYY-MM-DD format. Like "Entitlements - 2024-11-20.csv" 

I then first filter

Then sort Descneding to get the most recent one(s) first.

Then use a Table.FirstN() (see https://learn.microsoft.com/en-us/powerquery-m/table-firstn) to get the files you need.

Hope this helps!

Did I answer your question? Then please mark my post as the solution and make it easier to find for others having a similar problem.
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel

[David_M_W]

Thanks for your reply. I will look into Table.First and the filtering technique you use and see if I can work out a solution. The files I am using are exported from Microsoft Navision as .xml and converted to .xlsx as I save the locally. 

Thanks again.