Forum Discussion

David_M_W's avatar
David_M_W
Frequent Visitor
1 year ago
Solved

Automation from a folder.

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 r...
Need Help
Tips and Tricks
Tutorial Requests
  • PwerQueryKees's avatar
    PwerQueryKees
    1 year ago

    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"

     

PwerQueryKees's avatar
PwerQueryKees
Super User
1 year ago

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's avatar
    David_M_W
    Frequent Visitor
    1 year ago

    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.