cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LJR Frequent Visitor
Frequent Visitor

Define and invoke function within query

I am struggling with refreshing data coming off multiple web API’s which I combine using a parameter in the URL. The data refreshes fine in Power BI desktop, but I cannot get it to refresh within the service. The error message is: 

 

[Unable to combine data] Section1/V7ClientsStaging2/Added Custom1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. Table: V7Clients.

 

I have tried privacy settings, staging tables (as per https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/) and still cannot get it to work.

 

A recommendation off another blog was to add the function definition into the main query. I'm struggling with the syntax however to get this to work. Any assistance would be appreciated.

 

Main Query (the function is AssessmentsInfo, bolded below):

 

let
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 AssessmentsInfo([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"

 

AssessmentsInfo function definition:

 

(AreaAPI as text) as list =>

let
Source = Json.Document(Web.Contents(“https://abc.xxx.com”,
[
RelativePath= “/api/v1/gateway/platform/”&AreaAPI&”/assessmentcriteria”

]

))
in
Source

 

AssessmentsInfo function definition within the Main Query (not working, gives a column of non invoked functions):

 

let
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 

 

(AreaAPI as text) as list =>

let
Source = Json.Document(Web.Contents(“https://abc.xxx.com”,
[
RelativePath= “/api/v1/gateway/platform/”&AreaAPI&”/assessmentcriteria”

]

))
in
Source

),

#"Merged Queries" = Table.NestedJoin(#"Added Custom1",{"AreaGuidsForFunction"},ClientDetails,{"RootAreaGuid"},"ClientDetails",JoinKind.LeftOuter),
#"Expanded ClientDetails" = Table.ExpandTableColumn(#"Merged Queries", "ClientDetails", {"ClientName"}, {"ClientName"})
in
#"Expanded ClientDetails"

 

Any assistance on the syntax of adding the function definition within the query would be much appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: 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"

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




13 REPLIES 13
Super User
Super User

Re: Define and invoke function within query

@ImkeF might be able to assist.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Super User
Super User

Re: 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"

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




LJR Frequent Visitor
Frequent Visitor

Re: Define and invoke function within query

@Greg_Deckler Thanks for the referral.

 

@ImkeF Thanks so much for showing me how to define and and invoke a function within a query. This worked correctly. 

 

I'm still however having the issue when uploading to Power BI service: [Unable to combine data] Section1/V7ClientsStaging1/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. Table: V7ClientsStaging1.

 

This suggestion to add the definition of the main function came from Chris Webb's blog. 

https://blog.crossjoin.co.uk/2017/06/26/data-privacy-settings-in-power-bipower-query-part-3-the-form...

 

At this point I'm at a complete loss on how to fix this error, I've tried privacy settings, staging tables in multiple forms, rewriting queries. Any other suggestions I could pursue would be most welcome. 

Super User
Super User

Re: Define and invoke function within query

Please check out this article: 

http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/

 

Don't know if it contains sth useful for your case, though.

 

Have you tried integrating the content of your "Source"-step as well?

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Rayno Frequent Visitor
Frequent Visitor

Re: Define and invoke function within query

Hi There,

 

I'm also struggling with the same error in powerbi.com. I'm trying to reverse geolocation information based on Lat and Long through my google maps API and source of the information(lat & long) is located on a sharepoint folder.

 

My PowerBi pbix file has the Privacy setting adjusted. 

 

This following error happens when I invoked a custom function with the same data source query,
"[Unable to combine data] Section1/Location/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination"

 

This following error happens when I first stage the source information and then creating a secoud query to referrence the stage source with the added customer function
[Unable to combine data] Section1/Query1 (2)/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination

 

As you can see, stagging for not stagging the error persists in the cloud 

 

let
Source = SharePoint.Files("https://oursharepointfolder/", [ApiVersion = 15]),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
Navigation1 = Source{0}[Content],
#"Imported Excel" = Excel.Workbook(Navigation1),
#"Expanded Data" = Table.ExpandTableColumn(#"Imported Excel", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Sheet1", type text}, {"Site Name", type text}, {"Address", type text}, {"Co-Ordinates", type number}, {"Co-Ordinates_1", type number}, {"Supplier", type text}, {"Solution", type text}, {"Curcuit ID", type text}, {"Sheet1_2", type text}, {"Sheet", type text}, {"false", type logical}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Filtered Rows" = Table.SelectRows(#"Removed Top Rows", each ([#"Co-Ordinates"] <> null)),
#"Inserted Merged Column" = Table.AddColumn(#"Filtered Rows", "Location", each Text.Combine({Text.From([#"Co-Ordinates"], "en-US"), Text.From([#"Co-Ordinates_1"], "en-US")}, ","), type text),
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted Merged Column",{{"Location", type text}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type1", "Query2 (2)", each #"Query2 (2)"([Location])),
#"Expanded Query2 (2)" = Table.ExpandTableColumn(#"Invoked Custom Function", "Query2 (2)", {"street_address", "route", "postal_code", "localitypolitical", "administrative_area_level_1political", "countrypolitical"}, {"street_address", "route", "postal_code", "localitypolitical", "administrative_area_level_1political", "countrypolitical"})
in
#"Expanded Query2 (2)"

 

###########################################################################################
Query 2 is my custom Fuction query

 

let

Findaddress = (addresslocation) =>


let
Source = Json.Document(Web.Contents("https://maps.googleapis.com",
[RelativePath="maps/api/geocode/json?latlng="&addresslocation&"&key=Keyhasbeeneditedout"])),
results = Source[results],
#"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"formatted_address", "types"}, {"formatted_address", "types"}),
#"Extracted Values" = Table.TransformColumns(#"Expanded Column1", {"types", each Text.Combine(List.Transform(_, Text.From)), type text}),
#"Filtered Rows" = Table.SelectRows(#"Extracted Values", each ([types] <> "administrative_area_level_2political" and [types] <> "administrative_area_level_3political" and [types] <> "politicalsublocalitysublocality_level_1")),
#"Transposed Table" = Table.Transpose(#"Filtered Rows"),
#"Reversed Rows" = Table.ReverseRows(#"Transposed Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Reversed Rows", [PromoteAllScalars=true])

in
#"Promoted Headers"

in

Findaddress
########################################################################################

I'm not sure how else to structure the query so that it works in powerbi.com service. Hope there is a way?

 

Kind regards,

Rayno

 

 

Super User
Super User

Re: Define and invoke function within query

The article I referenced in my previous post states that you have to use query parameters to make this successful:

 

so instead of this:

 

Source = Json.Document(Web.Contents("https://maps.googleapis.com",
[RelativePath="maps/api/geocode/json?latlng="&addresslocation&"&key=Keyhasbeeneditedout"])),

you have to write it like so:

 

Source = Json.Document(Web.Contents("https://maps.googleapis.com",
[RelativePath="maps/api/geocode/json",
Query=[latlng=addresslocation, key=Keyhasbeeneditedout]])),

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




LJR Frequent Visitor
Frequent Visitor

Re: Define and invoke function within query

@ImkeF 

 

I had a look at the blog entry you suggested without any luck. Thanks for the input on this though. 

 

When you say "Have you tried integrating the content of your "Source"-step as well?" Are you referring to the Source as below? 

 

If so, I tried adding the query content into the step directly, with the source being a dataflows query. Still no change Smiley Sad

 

 

Power BI Image.pngSource

 

 

Rayno Frequent Visitor
Frequent Visitor

Re: Define and invoke function within query

Hi ImkeF,

Thank you for this, I made the changes as advised and it works for PowerBI deskop but still errors in the PowerBI cloud service.
I've read alot of forms and articles, (saw you also commented in a few I read - nice one).

 

A college has shared an excel document from their one drive for business and shared it with me. The link takes me to the sharePoint destination.

 

The excel document has been successfully imported with a working custom function to find the addresses from lat's and long's ( 
-22.338304000000001,30.042366999999999) | PowerBI Desktop

I used the web connection to the excel document as per,
https://www.youtube.com/watch?v=t4TzHu8THoA 


This all works perfectly on the PowerBI desktop but keeps failing in the PowerBI cloud service.


From my understanding both sources are external
1.) SharePoint (which is the onedrive saved location)
2.) Google API call to retieve  

The following additional steps where test
1.) Non Staged Query 
Works perfectly on PowerBI desktop | Fails in the PowerBI Cloud Service

This is the error from the PowerBI Cloud Service:
##[Unable to combine data] Section1/Query1/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination##

2.) Staged Query as per https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/ 
Works perfectly on PowerBI desktop | Fails in the PowerBI Cloud Service
This is the error from the PowerBI Cloud Service:
##[Unable to combine data] Section1/Sheet1 (2)/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination##

3.) Excel document was moved to sharedfolder + to "c:\PorjectFolder\data.xlsx | Both results are as follows
Works perfectly on PowerBI desktop | Fails in the PowerBI Cloud Service
This is the error from the PowerBI Cloud Service: Note - A Personal gateway was used and this is a NON Staged data query.

-2147467259 Table: Sheet1.
Underlying error message:[Unable to combine data] Section1/Sheet1/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.
DM_ErrorDetailNameCode_UnderlyingHResult:-2147467259


Conclusion:

It all works via PowerBI Deskop and fails on PowerBI cloud.

External sources                                                                 | Failed
Moved source to a local folder via the personal gateway | Failed


Super User
Super User

Re: Define and invoke function within query

Hi @Rayno & @LJR 

I suggest you read this article: https://social.technet.microsoft.com/Forums/en-US/ca434e2d-88fe-4962-b46a-a1db51e8bd89/feedback-want...

 

give good feedback and try to motivate Ehren to continue his series, where he will hopefully come up with the solution-part as well Smiley Wink

 

Maybe you get some ideas how to tweak your code further, but considering the complexity of the matter, I don't see how I can help you any further in the forum here unfortunately.

 

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries