Forum Discussion
Define and invoke function within query
- 7 years ago
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"
ImkeF might be able to assist.
- ImkeF7 years agoCommunity Champion
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"- Anonymous7 years agoNot applicable
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.
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.
- ImkeF7 years agoCommunity Champion
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?