Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bloigge
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
nakia
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!

andreit
Frequent Visitor
nakia
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"

 

 

itelligent-i
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.

bloigge
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

?

bloigge
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

@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.

bloigge
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? 

lubosbednar
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)),
miguel
Community Admin
Community Admin

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

 

Greg_Deckler
Super User
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors