cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Gateway Issue

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.

 

temp.png


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,

1 REPLY 1
Highlighted
Community Support
Community Support

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors