cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

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
Frequent Visitor

Anyone got any suggestions for how to get around this for web queries. My code is above.

This is really going to limit the use I can get out of Power BI if it can't do this!

Frequent Visitor
Frequent Visitor

Hi,

 

Seems I am running into the same hurdle.

 

Any advice on how I could modify my query?

let
    // Calculate the Start of the Month, Today then format the date parameter string as YYYYMMDD.
    MonthStart = Date.StartOfMonth(DateTime.LocalNow()),
    MonthStart_YYYYMMDD = Date.ToText(#date(Date.Year(MonthStart), Date.Month(MonthStart), Date.Day(MonthStart)), "yyyyMMdd"),  
    Today_YYYYMMDD = Date.ToText(#date(Date.Year(DateTime.LocalNow()), Date.Month(DateTime.LocalNow()), Date.Day(DateTime.LocalNow())),"yyyyMMdd"),
    // Store a list of the User ID's from Harvest (Only include the ID's of the people you need visalised on this data set)
    User_IDS = {"1219708","294662","337056"},
    // Intiate the query
    Data = List.Generate(
    ()=> [Counter = 0],
    each [Counter]<List.Count(User_IDS),
    each [Counter=[Counter] + 1],
    each Json.Document(Binary.Buffer(Web.Contents("https://equipmentmanagementsolutions.harvestapp.com/people/" & Text.From(User_IDS{[Counter]}) & "/entries?from=" & MonthStart_YYYYMMDD & "&to=" & Today_YYYYMMDD  ,[Headers=[#"Authorization" = Harvest_Credentials,#"Content-Type"="application/json",#"Accept"="application/json"]])))
    ),
    #"Converted to Table" = Table.FromList(Data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"day_entry"}, {"Column1.day_entry"}),
    #"Expanded Column1.day_entry" = Table.ExpandRecordColumn(#"Expanded Column2", "Column1.day_entry", {"id", "notes", "spent_at", "hours", "user_id", "project_id", "task_id", "created_at", "updated_at", "adjustment_record", "timer_started_at", "is_closed", "is_billed", "started-at", "ended-at"}, {"Column1.day_entry.id", "Column1.day_entry.notes", "Column1.day_entry.spent_at", "Column1.day_entry.hours", "Column1.day_entry.user_id", "Column1.day_entry.project_id", "Column1.day_entry.task_id", "Column1.day_entry.created_at", "Column1.day_entry.updated_at", "Column1.day_entry.adjustment_record", "Column1.day_entry.timer_started_at", "Column1.day_entry.is_closed", "Column1.day_entry.is_billed", "Column1.day_entry.started-at", "Column1.day_entry.ended-at"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1.day_entry",{{"Column1.day_entry.id", "Entry.ID"}, {"Column1.day_entry.notes", "Entry.Notes"}, {"Column1.day_entry.spent_at", "Entry.Date"}, {"Column1.day_entry.hours", "Hours.OnTask"}, {"Column1.day_entry.user_id", "User.ID"}, {"Column1.day_entry.project_id", "Project.ID"}, {"Column1.day_entry.task_id", "Task.ID"}, {"Column1.day_entry.created_at", "Entry.Created.DateTime"}, {"Column1.day_entry.updated_at", "Entry.Updated.DateTime"}, {"Column1.day_entry.started-at", "Entry.StartTime"}, {"Column1.day_entry.ended-at", "Entry.EndTime"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Column1.day_entry.timer_started_at"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Column1.day_entry.is_billed", "Is.Billable"}, {"Column1.day_entry.is_closed", "Entry.IsClosed"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"Column1.day_entry.adjustment_record"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns1",{"User.ID"},Harvest_Users,{"Column1.user.id"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Column1.user.first_name", "Column1.user.last_name", "Column1.user.department"}, {"NewColumn.Column1.user.first_name", "NewColumn.Column1.user.last_name", "NewColumn.Column1.user.department"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded NewColumn",{"Project.ID"},Harvest_Projects,{"Column1.project.id"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries1", "NewColumn", {"Column1.project.client_id", "Column1.project.name", "Column1.project.billable"}, {"NewColumn.Column1.project.client_id", "NewColumn.Column1.project.name", "NewColumn.Column1.project.billable"}),
    #"Merged Queries2" = Table.NestedJoin(#"Expanded NewColumn1",{"NewColumn.Column1.project.client_id"},Harvest_Clients,{"Column1.client.id"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn2" = Table.ExpandTableColumn(#"Merged Queries2", "NewColumn", {"Column1.client.name"}, {"NewColumn.Column1.client.name"}),
    #"Merged Queries3" = Table.NestedJoin(#"Expanded NewColumn2",{"Task.ID"},Harvest_Tasks,{"Column1.task.id"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn3" = Table.ExpandTableColumn(#"Merged Queries3", "NewColumn", {"Column1.task.name", "Column1.task.billable_by_default"}, {"NewColumn.Column1.task.name", "NewColumn.Column1.task.billable_by_default"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded NewColumn3",{"NewColumn.Column1.project.billable", "Is.Billable", "Entry.IsClosed"})
in
    #"Removed Columns2"

 

 

Helper I
Helper I

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

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.

Frequent Visitor

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

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

?

Frequent Visitor

Like the second one. In one of my reports I have a folder with alot of excel files, which are exported from another software. The structure of the files is always the same. The files are exported every day and in the naming of a file there is the current date - so the naming changes from day to day.

 

My approach is to query the parent folder, sort the files by date and remove all files except the newest. Then I insert a new column and call a function on the filepath of my filtered excel files. I have to do that, because the excel files have a bad structure so the table in a single file has to be rearanged before I can combine.

 

The problem is that the amount of files changes from day to day. So for example on monday there are more files to query than on a saturday.

 

My case is more or less like in this powerQuery tutorial: https://youtu.be/KXxUDWwo0pg?t=2015

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

@konstantinos Thank you for the link. It works - I am now able to refresh my folder data! Nevertheless I think it would be good option to be able to refresh data from a function as well!

I totally agree that it has to be solved. Althouth It has working workaround when you use „From Folder“ datasource instead of configuration table with file paths. But when I need to work e.g. with Web.Contents from different web or sharepoint locations then it doesn’t have solution yet. Thanks for solving.

Thanks for all the feedback. If there isn't already a request for this on the UserVoice, it would be great to add one.

Frequent Visitor

I'm using the same approach as bloigge. My client has many excel employee's timesheets with same structure. When will be this functionality supported for schedule refresh? 

Helper II
Helper II

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)),
MVP

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?

@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

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

 

Super User IV
Super User IV

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)


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors