Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

This dataset includes a dynamic data source.

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:
error.jpg

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

4 REPLIES 4
GilbertQ
Super User
Super User

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?





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

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

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?





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

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

Sorry, I meant to say, I am already doing that. The IDs are in a table.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors