Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I apreciate this question has been asked before, but none of the solutions have worked for me.
There's a funciton, which I use to expand a list of teams from Azure devops. The source "Teams" is loaded from dataflows and it gives me a list of IDs. The Query looks like this:
let
Source = Teams,
#"Removed Other Columns" = Table.SelectColumns(Source,{"TeamId", "TeamName"}),
#"Invoked Custom Function" = Table.AddColumn(#"Removed Other Columns", "MembersCall", each MembersCall([TeamId])),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Invoked Custom Function", {"MembersCall"}),
#"Expanded MembersCall" = Table.ExpandTableColumn(#"Removed Errors", "MembersCall", {"displayName", "id"}, {"displayName", "id"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded MembersCall",{{"displayName", "User"}, {"id", "UserID"}, {"TeamName", "Team"}, {"TeamId", "TeamID"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"User", type text}, {"UserID", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Normalisation", each "Distinct Active User", type text)
in
#"Added Custom"
The function looks like this:
let
Source = (Team_ID as text) => let
Path = "https://dev.azure.com/",
Repath = "vfuk-digital/_apis/projects/digital/teams/"&Team_ID&"/members?",
Source = Json.Document(Web.Contents(Text.From(Path), [RelativePath=Repath, Query=[#"api-version"="6.0"]])),
value = Source[value],
#"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"identity"}, {"identity"}),
#"Expanded identity" = Table.ExpandRecordColumn(#"Expanded Column2", "identity", {"displayName", "id", "uniqueName"}, {"displayName", "id", "uniqueName"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded identity",{{"displayName", type text}, {"id", type text}, {"uniqueName", type text}})
in
#"Changed Type"
in
Source
It works fine in the Desktop, but in the service it gives me this error:
As you can see I have already tried to use the relative path, but it didn't seem to change anything. The source won't refresh in the service.
Resources I tried so far:
https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...
https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...
https://medium.datadriveninvestor.com/setting-a-scheduled-refresh-on-a-dynamic-data-source-in-power-...
https://hatfullofdata.blog/power-query-dynamic-data-source-and-web-contents/
Please help
Hi @Anonymous
have a look at this blog post it might help Power Query Errors: Please Rebuild This Data CombinationThe Excelguru Blog
Another alternative is to rather store the ID's in a table and then get the function to call it from the table?
Thanks, but I know this link and I have tried this already.
The IDs are stored in a table, no?
Hi @Anonymous
Yes they would be stored in a table. And then your table will call the function?
Sorry, I meant to say, I am already doing that. The IDs are in a table.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.