Could you give me advice or share your experience on how to solve the error in refreshing data from Azure DevOps?
I try to get all the information about releases from different Projects from Azure DevOps. The parameter is the table with the list of projects.
My Source:
Source = Json.Document(Web.Contents("https://vsrm.dev.azure.com/organisation/", [RelativePath = Parameter & "/_apis/release/releases?api-version=7.1-preview.8"]))
When I try to refresh the data in the service (the desktop version is working) I get this error:
Data source error[ValidateMarkupTags][ccon]DataFormat.Error: [ccon]We found extra characters at the end of the JSON input.[/ccon]. Value = [ccon]<[/ccon]. Position = [ccon]4[/ccon]. [/ccon]. The exception was raised by the IDbCommand interface. Table: Release.
It happened when I added the new table and tried to use the Invoked Custom Function for expanding information by Projects
And one more question can I do it with Odata?
Table Release:
let
Source = Json.Document(Web.Contents("https://vsrm.dev.azure.com/organisation/", [RelativePath = Parameter & "/_apis/release/releases?api-version=7.1-preview.8"])),
value = Source[value],
#"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "status", "createdOn", "modifiedOn", "modifiedBy", "createdBy", "createdFor", "variables", "variableGroups", "releaseDefinition", "releaseDefinitionRevision", "description", "reason", "releaseNameFormat", "keepForever", "definitionSnapshotRevision", "logsContainerUrl", "url", "_links", "tags", "triggeringArtifactAlias", "projectReference", "properties"}, {"Column1.id", "Column1.name", "Column1.status", "Column1.createdOn", "Column1.modifiedOn", "Column1.modifiedBy", "Column1.createdBy", "Column1.createdFor", "Column1.variables", "Column1.variableGroups", "Column1.releaseDefinition", "Column1.releaseDefinitionRevision", "Column1.description", "Column1.reason", "Column1.releaseNameFormat", "Column1.keepForever", "Column1.definitionSnapshotRevision", "Column1.logsContainerUrl", "Column1.url", "Column1._links", "Column1.tags", "Column1.triggeringArtifactAlias", "Column1.projectReference", "Column1.properties"})
in
#"Expanded Column1"
refreshing is working
I created the Parameters list with the naming of the project
and function "test" for Expanded data
let
Source = (Parameter as any) => let
Source = Json.Document(Web.Contents("https://vsrm.dev.azure.com/organisation/", [RelativePath = Parameter & "/_apis/release/releases?api-version=7.1-preview.8"])),
value = Source[value],
#"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "status", "createdOn", "modifiedOn", "modifiedBy", "createdBy", "createdFor", "variables", "variableGroups", "releaseDefinition", "releaseDefinitionRevision", "description", "reason", "releaseNameFormat", "keepForever", "definitionSnapshotRevision", "logsContainerUrl", "url", "_links", "tags", "triggeringArtifactAlias", "projectReference", "properties"}, {"Column1.id", "Column1.name", "Column1.status", "Column1.createdOn", "Column1.modifiedOn", "Column1.modifiedBy", "Column1.createdBy", "Column1.createdFor", "Column1.variables", "Column1.variableGroups", "Column1.releaseDefinition", "Column1.releaseDefinitionRevision", "Column1.description", "Column1.reason", "Column1.releaseNameFormat", "Column1.keepForever", "Column1.definitionSnapshotRevision", "Column1.logsContainerUrl", "Column1.url", "Column1._links", "Column1.tags", "Column1.triggeringArtifactAlias", "Column1.projectReference", "Column1.properties"})
in
#"Expanded Column1"
in
Source
refreshing is working
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vV38leK1YlWckkt8y8oVoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}}),
Project = #"Changed Type"[Project],
#"Converted to Table" = Table.FromList(Project, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Invoked Custom Function" = Table.AddColumn(#"Converted to Table", "test", each test([Column1])),
#"Expanded test" = Table.ExpandTableColumn(#"Invoked Custom Function", "test", {"Column1.id", "Column1.name", "Column1.status", "Column1.createdOn", "Column1.modifiedOn", "Column1.modifiedBy", "Column1.createdBy", "Column1.createdFor", "Column1.variables", "Column1.variableGroups", "Column1.releaseDefinition", "Column1.releaseDefinitionRevision", "Column1.description", "Column1.reason", "Column1.releaseNameFormat", "Column1.keepForever", "Column1.definitionSnapshotRevision", "Column1.logsContainerUrl", "Column1.url", "Column1._links", "Column1.tags", "Column1.triggeringArtifactAlias", "Column1.projectReference", "Column1.properties"}, {"test.Column1.id", "test.Column1.name", "test.Column1.status", "test.Column1.createdOn", "test.Column1.modifiedOn", "test.Column1.modifiedBy", "test.Column1.createdBy", "test.Column1.createdFor", "test.Column1.variables", "test.Column1.variableGroups", "test.Column1.releaseDefinition", "test.Column1.releaseDefinitionRevision", "test.Column1.description", "test.Column1.reason", "test.Column1.releaseNameFormat", "test.Column1.keepForever", "test.Column1.definitionSnapshotRevision", "test.Column1.logsContainerUrl", "test.Column1.url", "test.Column1._links", "test.Column1.tags", "test.Column1.triggeringArtifactAlias", "test.Column1.projectReference", "test.Column1.properties"})
in
#"Expanded test"
refreshing isn't working
