Nasim el Masoudi on 07 Apr 2022 14:29:18
Hi,
We already raised a request to Microsoft in regard to making user defined functions available in the PowerBI REST API. I Below are the details which we provided to Microsoft:
For example we create the following Power Query function:
Custom function definition:
= (fileName as text) =>
let
filePath = "D:\work\power_bi_cloud\" & fileName
in
filePath
and then we use this function inside some table Power Query script as below:
Custom function usage:
let
Source = Csv.Document(File.Contents(GetFilePath("Source.txt")),[Delimiter=",", Columns=2, Encoding=1250, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"S1", Int64.Type}, {"S2", Int64.Type}})
in
#"Changed Type"
We are using the following API to scan workspaces: https://docs.microsoft.com/en-us/rest/api/power-bi/admin/workspace-info-get-scan-result and we can see the following result:
Exported json excerpt:
{
"id": "f4eaa351-3276-4b0d-b231-c3b295a9948b",
"name": "Custom Function",
"tables": [
{
"name": "Source",
"columns": [
{
"name": "S1",
"dataType": "Int64",
"isHidden": false,
"columnType": "Data"
},
{
"name": "S2",
"dataType": "Int64",
"isHidden": false,
"columnType": "Data"
}
],
"measures": [],
"isHidden": false,
"source": [
{
"expression": "let\n Source = Csv.Document(File.Contents(getFilePath(\"Source.txt\")),[Delimiter=\",\", Columns=2, Encoding=1250, QuoteStyle=QuoteStyle.None]),\n #\"Promoted Headers\" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),\n #\"Changed Type\" = Table.TransformColumnTypes(#\"Promoted Headers\",{{\"S1\", Int64.Type}, {\"S2\", Int64.Type}})\nin\n #\"Changed Type\""
}
]
}
]
But there is no source code of getFilePath function available anywhere in this json file. We haven’t found any other API that will get us this information.
Please vote for this idear! Thank you in advance.