Power BI
Needs VotesMake functions refreshable when the data source is a parameter of the function
Bernd Loigge on 14 Aug 2015 03:16:28
Like discussed here: http://community.powerbi.com/t5/Integrations-with-Files-and/Refreshing-queries-with-functions-doens-t-work/td-p/2450 - queries where data access happens inside a function and where the data source is dependent on parameters to the function should be refreshable.
- Comments (117)
RE: Make functions refreshable when the data source is a parameter of the function
Yikes, this breaks my 26 reports for auto updates. Would be nice to have parameters included in the data src. Looks like I will manually link each src file.
RE: Make functions refreshable when the data source is a parameter of the function
If use static web query link - gateway and report works well
(
http://10.10.10.10/ERP-Orchids/hs/getting/20150101
)
But when I added calculated variable to link, PowerBI Desktop report works well, but I cant add gateway to report:
(
v = Text.From( DateTime.LocalNow () )
"http://10.10.10.10/ERP-Orchids/hs/getting/" & v
)
Is there way to use calculated variable in web query with gateway?
RE: Make functions refreshable when the data source is a parameter of the function
Was really happy with my Power BI dashboard that used functions to build tables, but was then disappointed when I couldn't schedule an update. I had to go and re-build the dashboard again using the long method just so it could refresh.
RE: Make functions refreshable when the data source is a parameter of the function
Please get this sorted! Cant believe I spent all this time building a report in BI Desktop and it is now useless in the online service!
RE: Make functions refreshable when the data source is a parameter of the function
Same issue for me, works well in Power Desktop but not when published on the portal. I cannot refresh my datamodel anymore and so this great solution for Desktop is useless for Power BI portal. So a solution to this problem is very much appreciated.
RE: Make functions refreshable when the data source is a parameter of the function
Hi Gautier,
Could you explain in more detail how you used joins instead of the function?
thank you
Sebastian
RE: Make functions refreshable when the data source is a parameter of the function
Hey Guys, I found a workaround. Instead of using function to call another query you can use joins.
At least it worked for me and then the refresh works in PBI service as well
RE: Make functions refreshable when the data source is a parameter of the function
Same for me, even though the power bi service doesn't report any refresh error (when looking at the refresh history) the result after refreshing is completely different from what I obtain if I refresh the same model on power bi desktop.
So I assume that this is still not supported by Power BI service. Is there any plan to let us refresh data models that contain powerquery functions ?
RE: Make functions refreshable when the data source is a parameter of the function
'@sdjensen: Refreshing of a dataset that contains functions in the power query still doesn't work for me. Everything runs fine using Power BI Desktop but as soon as I publish to the Power BI Portal and try to refresh the data from there I get an error.
Below is an example of the power query that I am trying to run. Please excuse the lack of indentation as it seems to get removed after posting the comment.
/* Gets the transaction file data as a table by url */
let getTransByFileUrl = (sourceTransFileUrl as text) =>
let sourceTransWeb = Web.Contents(sourceTransFileUrl) in
let sourceTrans = try Csv.Document(sourceTransWeb, null, ",", null, 1252) in
if sourceTrans[HasError] then
#table(type table [],{})
else
let sourceTransWithHeaders = Table.PromoteHeaders(sourceTrans[Value]) in
Table.TransformColumnTypes(
sourceTransWithHeaders,
{
{"Account", type text},
{"Account Number", type text},
{"Transaction Date", type date},
{"Transaction Amount", type number},
{"Transaction Description", type text},
{"Transaction Code", type text}
}
) in
/* Combines all the transaction file data for all source transaction files as a table */
let getTransByTransFiles = (sourceTransFiles as list) =>
Table.Combine(
List.Transform(
sourceTransFiles, (sourceTransFile) => getTransByFileUrl(sourceTransFile)
)
) in
/* Generates a list of daily transaction file names starting from the specified start date with the specified folder and file name */
let getSourceTransFiles = (startDate, sourceTransFolderUrl, fileNameWithExt) =>
let dates =
List.Dates(startDate, Number.From(DateTime.LocalNow () ) - Number.From(startDate), #duration(1, 0, 0, 0)) in
List.Transform(
dates,
(date) =>
let fileUrl = sourceTransFolderUrl & Date.ToText(
RE: Make functions refreshable when the data source is a parameter of the function
I believe this issue is still not fixed as I tried it post May 16 update. It still doesn't allow me to refresh when I use a function. @sdjensen can you confirm if it worked for you and if it how did you approach it ?
Cheers,
Anthony