Forum Discussion

sdjensen's avatar
sdjensen
Icon for Solution Sage rankSolution Sage
8 years ago
Solved

Schedule Refresh of Excel files in folder

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. 

Need Help
  • sdjensen's avatar
    sdjensen
    8 years ago

    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

     

2 Replies

  • sdjensen's avatar
    sdjensen
    Icon for Solution Sage rankSolution Sage
    8 years ago

    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