Reply
LJR Frequent Visitor
Frequent Visitor
Posts: 10
Registered: ‎01-30-2018
Accepted Solution

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!


Accepted Solutions
Highlighted
Super User
Posts: 1,626
Registered: ‎09-06-2015

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




View solution in original post


All Replies
Super User
Posts: 10,570
Registered: ‎07-11-2015

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!


Highlighted
Super User
Posts: 1,626
Registered: ‎09-06-2015

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
Posts: 10
Registered: ‎01-30-2018

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
Posts: 1,626
Registered: ‎09-06-2015

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