Gateway Issue

[visheshjain]

Hello everyone,

I am having issue configuring gateway for my file.

I have a folder which has different departments, each department has a folder with date and that folder has the files, which append.
E.g.
Department > 11th June 2020 > File1, File2, File3

I have defined a function to get the file and run ETL on it. Here is my function:

(filepath)=>
let
    Source = Excel.Workbook(File.Contents(filepath), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(Sheet1_Sheet,{"Column6", "Column7"}),
    #"Removed Top Rows" = Table.Skip(#"Removed Columns",2),
    #"Transposed Table" = Table.Transpose(#"Removed Top Rows"),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Trimmed Text" = Table.TransformColumns(#"Merged Columns",{{"Merged", Text.Trim, type text}}),
    #"Transposed Table1" = Table.Transpose(#"Trimmed Text"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Ref. No.", type text}, {"Party's Name", type text}, {"Claim No", type text}, {"Opening Amount", type number}, {"Final Balance", type number}, {"Overdue by days", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Final Balance] > 50),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Date] <> null))
in
    #"Filtered Rows1"​

Here is the issue and this is the error I am getting configuring gateways.

From the error I can see that the command in my function File.Contents is creating a problem, but I don't know how to replace the command and get this working.

Time is of the essence, so any help is welcome.

Thank you,

[v-yiruan-msft]

Hi @visheshjain ,

Currently schedule refresh will not work in Power BI Service when use function. You can refer the workaround in the following threads and check if it can resolve your problem.

Schedule Refresh of Excel files in folder

Best Regards

Rena