cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sujeesh
Helper I
Helper I

How to connect Azure DevOps REST API in to power bi

Hi,

I am new to this power bi. Can someone please tell me how to connect the Azure Devops REST API into the power bi.

The API I am trying to connect is "https://vsrm.dev.azure.com/****/_apis/release/definitions?api-version=5.1".

 

Thanks & Regards,

Sujeesh

2 ACCEPTED SOLUTIONS
artemus
Microsoft
Microsoft

= VSTS.Contents(<your url>)

View solution in original post

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"

 

View solution in original post

15 REPLIES 15
Yishai
Regular Visitor

THANK YOU!!!!  I'm now able to connect to an online zipped database in Power BI, and now I'm a serious data badass.

 

Thank you 🙏

Anonymous
Not applicable

Hi All,

 

Can someone please tell me how to connect the Azure Devops REST API into the power bi.

 

POST https://dev.azure.com/{organization}/{project}/_apis/test/Results/testhistory?api-version=6.0-previe...

 

Thanks,

Himanshu Jain

artemus
Microsoft
Microsoft

If you need to unzip any files that you get use this function, the other ones I've seen online don't work with devops:

 

// Unzip
(ZIPFile) => 
let
    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
bkeefe
Frequent Visitor

@artemus - Is this function posted on its own anywhere?  Otherwise, my follow up question is what causes the error (the number below appears to be equal to what I'm getting for the length of the zip file in lbendlin's zip function😞

There was a problem reading the binary format at position 7843840. The end of the input was reached before the value could be read.

 The zip file contains csv and json files where some filenames contain characters like "é" which appear as "�" in Power Query.  Could those be causing the above error?

You are probably using a ZIP64 arcieve, which I didn't implement here.

bkeefe
Frequent Visitor

Thank you.  Much appreciated.  I will continue investigating to see if I can figure it out.

This worked excellent tfor what i needed and in fact did in in powerquery for excel before migrating to PowerBI. I will admit to just copy-pasting and filling in my details so i dont understand the storage aspect. Where does the zip file go? is it simply downloaded, extracted and discarded or am i missing it altogether.

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"

 

 

artemus
Microsoft
Microsoft

= VSTS.Contents(<your url>)

Thank you for clarifying my doubts. It worked

I got the result as a JSON file. Can you tell me how to convert this JSON file as a table so that I can use this data in my chart

 

Please find the screenshotPower bi.png

Add this line as your next step:

= Table.FromRecords(Json.Document(Source,65001)[value])

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors