Forum Discussion
Define and invoke function within query
That would be done like so:
let AssessmentsInfoFunction = (AreaAPI as text) as list => let Source = Json.Document(Web.Contents(“https://abc.xxx.com”, [ RelativePath= “/api/v1/gateway/platform/”&AreaAPI&”/assessmentcriteria” ] )) in Source, Source = {ClientDetails[RootAreaGuid]}, #"ListOfLists" = List.Combine(Source), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "AreaGuidsForFunction"}}), #"Expanded AreaGuidsForFunction" = Table.ExpandListColumn(#"Renamed Columns", "AreaGuidsForFunction"), #"Added Custom1" = Table.AddColumn(#"Expanded AreaGuidsForFunction", "Custom", each AssessmentsInfoFunction([AreaGuidsForFunction])), #"Merged Queries" = Table.NestedJoin(#"Added Custom1",{"AreaGuidsForFunction"},ClientDetails,{"RootAreaGuid"},"ClientDetails",JoinKind.LeftOuter), #"Expanded ClientDetails" = Table.ExpandTableColumn(#"Merged Queries", "ClientDetails", {"ClientName"}, {"ClientName"}) in #"Expanded ClientDetails"
Thanks ImkeF , that link definitely helped.
I always say " We always tell computers what to do" so if it works on PowerBI Desktop(As example we told it to ignore Privacy levels) then there must be someone( a developer :smileyhappy: ) that didnt tell the PowerBI cloud service to listen to our Privacy settings within our PowerBI Pbix file with regards to external data sources.
So how we overide it, by telling the PowerBI cloud service what to do, we manually Add the sources to the enterprise gateway,
The enterprise gateway allows one to choose under advance setting the Privacy level which in turns corrects the Privacy level.
So now it works and I've confirmed data changes.
I believe this needs to be corrected by the PowerBI team.
My idea, they need to add the ability to either take in the Privacy level configured within the pbix file or they need to provide the ability to set the Privacy level under the Data Set setting -> Data Source Credentials.
Otherwise for now, add the source connections to the enterprise gateway and set the Privacy Levels.
Thank you again for the article and for your guidance.
Kind regards,
Rayno
PS, Dont stage the source information when trying the now mentioned workaround.
It didnt work for me, so I rolled back to the orginal which was to,
Invoke the function within the same Dataquery
Kind regards,
Rayno