data flow error dynamic data source

[Joerg]

Hello all

I have a pro licence and want to setup a data flow.

My table has a column with Id-nos from a sharepoint list.

I add a column to retrieve for each Id-no the version history from sharepoint.

In PBI desktop this works fine, but in service - when I try to save the flow - I get the error that it can't be saved because of reference to dynamic data source.

As far as I understand, this is due to the field "Id" which I use in the URL and makes it dynamic.

I searched a lot and tried to use relative path in den Web.Contents() part, but I do not get it working.

My script looks like this:

let
Source = PowerBI.Dataflows(null),
workspaceId = Source{[workspaceId="XYZ"]}[Data],
dataflowId = workspaceId{[dataflowId="ABC"]}[Data],
NPAP = dataflowId{[entity="NPAP"]}[Data],
AddColumn = Table.AddColumn(NPAP, "Xml.Tables", each Xml.Tables(Web.Contents(
"https://myaddress.sharepoint.com/sites/mysite/_api/web/Lists/getbytitle('mylist')/items("&Text.From([Id])&")/versions")))
in
AddColumn

How does my Web.Contents part need to be modified to get this working in PBI service ?

Thanks a lot for your help.

Joerg

[Joerg · Accepted solution]

Got now help from a colleague and added the RelativePath to get it working.

Xml.Tables(Web.Contents("https://myaddress.sharepoint.com/sites",[RelativePath="/mysite/_api/web/Lists/getbytitle('mylist')/items("&Text.From([Id])&")/versions"]))

[lbendlin]

Needs more details. Where does the [ID] field come from in this particular Power Query? Is it  a declared parameter? or is it a result column from a previous step?  You said you have a list of IDs. Usually that list is small (sub 5000) so you could have a reference table with all IDs and then run a  "each" loop against these as you show already. 

[Joerg]

I have a sharepoint lst and query all Id from that list.

The Id numbers are in my first column.

Then I add a column to retrieve for each Id of the first column the version history.

The approach I found from here:

https://community.powerbi.com/t5/Desktop/Getting-SharePoint-List-items-with-full-history-version/td-... 

Working fine in PBI desktop, but in a data flow it comes up with the mentioned error.

Thanks

Joerg