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

Refreshing queries with functions doens't work

Does anyone else experience that refreshing on-premise data via a gateway (for example amount of files in a folder) works if you hardcode the path to the folder, but when you create a function where the path is the input parameter powerbi says that you are not able to refresh this data source?

1 ACCEPTED SOLUTION

Accepted Solutions
konstantinos Senior Member
Senior Member

Re: Refreshing queries with functions doens't work

Maybe try this approach..and filter the latest file by date created..Needs more code but it worked - refreshed in PowerBI V1 so probably it will work in V2 as it doesn't use parameters..

 

http://www.poweredsolutions.co/2014/11/21/combining-data-from-multiple-excel-workbooks-with-power-qu...

Konstantinos Ioannou

View solution in original post

19 REPLIES 19
Super User IV
Super User IV

Re: Refreshing queries with functions doens't work

Have not tried that one, can you publish your function here and I'll play around with it and see if I can get it to function. (pun intended)


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

miguel
MVP

Re: Refreshing queries with functions doens't work

I've tried using a function from an Azure Machine Learning Web Service and it works perfectly. I've tried others that work over a set of tables from Excel Files and also from a SQL Database table and they work great as well.

 

Is there any chance that you can paste the M code here so we can take a look?

bloigge Frequent Visitor
Frequent Visitor

Re: Refreshing queries with functions doesn't work

Hardcoded Folder Path Query is working - Gateway options are shown:

 

 

let
    Source = Folder.Files("P:\MyFolder\MySubFolder"),
    #"Sorted Rows" = Table.Sort(Source,{{"Date created", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Index] = 0)),
    tempPath = #"Filtered Rows"{0}[Folder Path],
    folderPath = Text.Range(Text.Range(tempPath,0,Text.Length(tempPath)-1),Text.PositionOfAny(Text.Range(tempPath,0,Text.Length(tempPath)-1),{"\"},Occurrence.Last) + 1,(Text.Length(tempPath)-1) - (Text.PositionOfAny(Text.Range(tempPath,0,Text.Length(tempPath)-1),{"\"},Occurrence.Last) + 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Content", "Name", "Extension", "Date accessed", "Date modified", "Attributes", "Folder Path", "Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Date created", folderPath}})
in
    #"Renamed Columns"

 

 

Creating a function which I then recall multiple times is not working:

 

 

let
    Abfrage = (path as text) =>
let
    Source = Folder.Files(path),
    #"Sorted Rows" = Table.Sort(Source,{{"Date created", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Index] = 0)),
    tempPath = #"Filtered Rows"{0}[Folder Path],
    folderPath = Text.Range(Text.Range(tempPath,0,Text.Length(tempPath)-1),Text.PositionOfAny(Text.Range(tempPath,0,Text.Length(tempPath)-1),{"\"},Occurrence.Last) + 1,(Text.Length(tempPath)-1) - (Text.PositionOfAny(Text.Range(tempPath,0,Text.Length(tempPath)-1),{"\"},Occurrence.Last) + 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Content", "Name", "Extension", "Date accessed", "Date modified", "Attributes", "Folder Path", "Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Date created", folderPath}})
in
    #"Renamed Columns"
in
    Abfrage

 

 

Calling the function:

 

let
    Source = GetLatestFile("P:\MyFolder\MySubFolder")
in
    Source

 

The notification I get when using a function:

Capture.PNG

 

lubosbednar Regular Visitor
Regular Visitor

Re: Refreshing queries with functions doens't work

I experience exactly the same problem. When I use hardcoded path (local or OneDrive for Business), it allows me to configure refresh. However, when I use function which returns me file according to path specified dynamically, the refresh cannot be configured.

 

Function:

let GetSheet = (path as text, SourceType as text) =>
let
Source = if SourceType = "web" then Excel.Workbook(Web.Contents(path)) else Excel.Workbook(File.Contents(path)),
konstantinos Senior Member
Senior Member

Re: Refreshing queries with functions doens't work

@miguel I still didn't find a way to refresh the data in PowerBI service with AzureML OData source..Although in PBI desktop or excel is refreshing..

 

http://community.powerbi.com/t5/Service/Azure-ML-odata-amp-Refresh/m-p/2516#M694

 

I must do something the wrong way..

Konstantinos Ioannou
itelligent-i Regular Visitor
Regular Visitor

Re: Refreshing queries with functions doens't work

Yep, same issue here.  source wrapped in a function appears not to be refreshable from the portal.

Power BI Team curth
Power BI Team

Re: Refreshing queries with functions doens't work

This is currently correct; queries where data access happens inside a function and where the data source is dependent on parameters to the function can't currently be refreshed. That's because we're doing static analysis of the query to discover the data source, and our static analysis can't yet handle this scenario.

bloigge Frequent Visitor
Frequent Visitor

Re: Refreshing queries with functions doens't work

any timetable on that? depending on the size of my reports without functions code gets a mess really fast... 

Power BI Team curth
Power BI Team

Re: Refreshing queries with functions doens't work

Do you need the ability to call the same function multiple times? That is, does your code look more like

 

let

    function = (url) => Web.Contents(url),

    result = function("http://foo.com/bar"),

in

    result

 

or like

 

let

    function = (url) => Web.Contents(url),

    result1 = function("http://foo.com/bar"),

    result2 = function("http://foo.com/baz"),

    result = Binary.Combine(result1, result2)

in

    result

?

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors