Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.