Forum Discussion
How to connect Azure DevOps REST API in to power bi
= VSTS.Contents(<your url>)
Oh, that isn't zip I think. It should be JSON. Just apply the line I gave in the previous post of the zip function. The zip function is useful if you want to download a bunch of files out of git. I did this myself once to do a git blame summary
This is what your query should look like:
let Source = VSTS.Contents("https://vsrm.dev.azure.com/fe-tfs/_apis/release/definitions?api-version=5.1"), #"Imported JSON" = Table.FromRecords(Json.Document(Source,65001)[value]) in #"Imported JSON"
Thank you for the reply. As per the above solution, i tried the below function. but i get the error as below
DataFormat.Error: There was a problem reading the binary format at position 167780. The end of the input was reached before the value could be read.
Please correct me if i have done any changes incorrectly.
| let ZIPFile = VSTS.Contents ("https://vsrm.dev.azure.com/fe-tfs/_apis/release/releases?api-version=5.1"), // Unzip ushort = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian), uint = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian), EDOCfn = BinaryFormat.Record([ ZipContent = BinaryFormat.Binary(Binary.Length(ZIPFile) - 22), Magic = uint, DiskNum = ushort, CDirectoryDiskId = ushort, CDirectoryRecordCountOnDisk = ushort, CDirectoryRecordCount = ushort, SizeOfCentralDirectory = uint, CentralDirectoryOffset = uint, CommendLength = ushort ]), EDOC = EDOCfn(ZIPFile), BeforeCentralDirectory = BinaryFormat.Binary(EDOC[CentralDirectoryOffset]), CentralDirectory = BinaryFormat.Length(BinaryFormat.Record( [ ZipContent = BeforeCentralDirectory, Items = BinaryFormat.List(BinaryFormat.Record( [ Magic = uint, CurrentVersion = ushort, MinVersion = ushort, Flags = ushort, CompressionMethod = ushort, FileModificationTime = ushort, FileModificationDate = ushort, CRC32 = uint, BinarySize = uint, FileSize = uint, FileInfo = BinaryFormat.Choice( BinaryFormat.Record( [ Len = ushort, FieldsLen = ushort, FileCommentLength = ushort, Disk = ushort, InternalFileAttr = ushort, ExternalAttr = uint, PosOfFileHeader = uint ]), (fileInfo) => BinaryFormat.Record( [ FileName = BinaryFormat.Text(fileInfo[Len], TextEncoding.Ascii), Fields = BinaryFormat.Binary(fileInfo[FieldsLen]), FileComment = BinaryFormat.Text(fileInfo[FileCommentLength], TextEncoding.Ascii), Disk = BinaryFormat.Transform(BinaryFormat.Null, each fileInfo[Disk]), InternalFileAttr = BinaryFormat.Transform(BinaryFormat.Null, each fileInfo[Disk]), ExternalAttr = BinaryFormat.Transform(BinaryFormat.Null, each fileInfo[InternalFileAttr]), PosOfFileHeader = BinaryFormat.Transform(BinaryFormat.Null, each fileInfo[PosOfFileHeader]) ]) ) ]), EDOC[CDirectoryRecordCount] ) ]), EDOC[CentralDirectoryOffset] + EDOC[SizeOfCentralDirectory]), Contents = List.Transform( CentralDirectory(ZIPFile)[Items], (cdEntry) => let ZipEntry = BinaryFormat.Record( [ PreviousData = BinaryFormat.Binary(cdEntry[FileInfo][PosOfFileHeader]), Magic = uint, ZipVersion = ushort, ZipFlags = ushort, CompressionMethod = ushort, FileModificationTime = ushort, FileModificationDate = ushort, CRC32 = uint, BinarySize = uint, FileSize = uint, FileName = BinaryFormat.Choice( BinaryFormat.Record( [ Len = ushort, FieldsLen = ushort ]), (fileInfo) => BinaryFormat.Record( [ FileName = BinaryFormat.Text(fileInfo[Len], TextEncoding.Ascii), Fields = BinaryFormat.Binary(fileInfo[FieldsLen]) ]) ), FileContent = BinaryFormat.Transform( BinaryFormat.Binary(cdEntry[BinarySize]), each Binary.Decompress(_, Compression.Deflate) ) ])(ZIPFile) in [FileName=ZipEntry[FileName][FileName], Content=ZipEntry[FileContent]] ) in Contents |
Oh, that isn't zip I think. It should be JSON. Just apply the line I gave in the previous post of the zip function. The zip function is useful if you want to download a bunch of files out of git. I did this myself once to do a git blame summary
This is what your query should look like:
let
Source = VSTS.Contents("https://vsrm.dev.azure.com/fe-tfs/_apis/release/definitions?api-version=5.1"),
#"Imported JSON" = Table.FromRecords(Json.Document(Source,65001)[value])
in
#"Imported JSON"
Thank you my friend. It worked😀
Hi Sujeesh,
I am also facing a similar kind of issue. I believe it's a syntax error.
Expression.syntaxError: Token Eof expected
= VSTS.Contents("https://dev.azure.com/****/*****/***** Pod/_apis/work/teamsettings/iterations/*******/capacities?api-version=6.0"),
#"Import Json" = Table.FromRecords(Json.Document(Source,65001)[value])
In
#"Import Json"
hello,
please can you give an example on how to use the zip function
thank you
Create a new blank Query, name it Unzip. Then open the advanced editor and paste the contents of the Unzip function. Then in a new query, go to your binary data. Add a custom step, and put Unzip(previousStep)
