Forum Discussion

Solution 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 ...

Need Help

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

sdjensen

Solution 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