Power BI

Needs Votes

Make functions refreshable when the data source is a parameter of the function

Vote (1233)

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 (120)

Comments (120)

Gautier on 05 Jul 2020 22:25:10

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 ?

JeremyH on 05 Jul 2020 22:24:05

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(

Power BI User on 05 Jul 2020 22:23:19

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

Raj on 05 Jul 2020 22:21:49

RE: Make functions refreshable when the data source is a parameter of the function

When i create a power query report with function to retrieve data from muliple project sites the refresh/schedule option is not working. It should be resolved soon

ryans on 05 Jul 2020 22:21:09

RE: Make functions refreshable when the data source is a parameter of the function

This is so needed! I wish I wouldn't of spent a day building out what I thought was a brilliant ETL approach for multiple servers with identical schemas. Please find a way to make this happen.

Sasha Chizhova on 05 Jul 2020 22:20:14

RE: Make functions refreshable when the data source is a parameter of the function

3 votes! To let Power BI to be a really powerful tool we have to enable this. The situation when some features only work for a desktop client is truly unacceptable

Mark Henderson on 05 Jul 2020 22:17:20

RE: Make functions refreshable when the data source is a parameter of the function

I call the same data source (URL) over multiple API endpoints, in case the URL needs to change I have each data source reference a single table parameter. I've now discovered this means refresh cannot be scheduled.. Seems like an obvious thing really.. 3 votes!