Schedule Refresh of Excel files in folder

[sdjensen]

Hi,

I am trying to build a model that should include data from Excel files in a folder. All files are structured the same but have data for different years. I have installed the Data Gateway and added the folder as a source, but when I try to schedule refresh on the model I get this message: "You can't schedule refresh for this dataset because one or more sources currently don't support refresh".

I have used a technique that I have previously used for merging data from multiple SQL databases into a single table.

This is my code:

let
    Source = Folder.Files("NameOfFolder"),
    MergeFolderFile = Table.AddColumn(Source, "Files", each [Folder Path] & [Name]),
    FilesToLoad = Table.Column(MergeFolderFile, "Files"),
    FilesLoop = (FilesToLoad as text) =>

let

    Source = Excel.Workbook(File.Contents(FilesToLoad), null, true),
    Sheet = Source{[Item="NameOfSheet",Kind="Sheet"]}[Data],
    PromotedHeaders = Table.PromoteHeaders(Sheet, [PromoteAllScalars=true])

in    

    PromotedHeaders,
    LoadFiles = List.Transform(FilesToLoad, each FilesLoop(_)),
    CombineFiles = Table.Combine(LoadFiles)
in
    CombineFiles

It's it possible at all to schedule refresh of files in a folder? If it isn't it doesn't make sence that the gateway allows to me to add a folder as a source. 

[sdjensen · Accepted solution]

I was able to solve it with this workaround: https://www.excelando.co.il/en/power-bi-cant-schedule-refresh-when-source-is-multiple-excel-files/

It's really amazing that MS hasn't added this feature to the gateway yet.

So I created another function using where I get the content of the files in the folder instead of looping then with folder and filename.

Function:

(Content) =>
let

    Source = Excel.Workbook(Content),
    Sheet = Source{[Item="NameOfSheetToLoad",Kind="Sheet"]}[Data],
    PromotedHeaders = Table.PromoteHeaders(Sheet, [PromoteAllScalars=true])

in    

    PromotedHeaders

and then invoke the function to load my table:

let
    Source = Folder.Files("FolderName"),
    InvokeCustomFunction = Table.AddColumn(Source, "Custom", each fnGetContent([Content])),
...
...
in
   NameOfFinalStep

[mail2vjj]

Hi @sdjensen ,

I am having the same problem and cant seem to figure out the solution even after tryin to replicate your solution.

When you use the Content function you invoke the function on the column Content, but where is this column coming from.

Can you please have a look at the following thread and suggest a solution.

https://community.powerbi.com/t5/Service/Gateway-Issue/m-p/1161835#M99431

Thank you,

Vishesh Jain