How to connect Azure DevOps REST API in to power bi

[sujeesh]

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

[artemus · Accepted solution]

= VSTS.Contents(<your url>)

[Yishai]

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]

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]

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

[Zrt99]

Hi artemus - how can I use your code for quering .xlsx files form a zipped folder in Power Query?  Thank you!

[nsalvay]

Hello @artemus , I have this case where I receive a zip file in json format via API. I have a column with the file name and another with the value (which is apparently in base64).
I tried to follow the steps I found on this forum to unzip it but I couldn't.
In summary:
1) I generated Unzip function with detailed code.
2) In the query that I get from the zip, I transformed the content to binary with the Text.ToBinary ([value]) function (I don't know if this step is correct)
3) I applied the ZIP function to the binary value and it gave me an error.

I'll take some screenshots to see if you can guide me, I would appreciate your help!

[nsalvay]

Hi, just in case I notice that I have already solved it, the error was in the conversion of the zip file to binary, I used this formula: Binary.FromText([value], BinaryEncoding.Base64).
With this I now see the compressed files, although I still cannot see their values, it gives me the following error about one of the csv:

I would appreciate if you could help me with this error.

Greetings

[Katr-PBI]

Hi,

I have used this funtion to unzip my files, but eventhough it shows the corect name of the file that was in the zip file it shows no content.

What can be the problem?

 

[StephenF]

What do you mean, dont work with dev ops?

Could you show an example that will work with this URL?

https://www.propertypriceregister.ie/website/npsra/ppr/npsra-ppr.nsf/Downloads/PPR-ALL.zip/$FILE/PPR...

[bkeefe]

@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?

[artemus]

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

[Stefano333]

Hi Artemus,

it would be great if you could also give the whole world a 64-bit unzip function 🙂 !! 
In my case, downloading data from uncompressed eurostat databases becomes a huge problem (very slow).
On the other hand, Eurostat returns zip files in 64bit at the speed of light.
many thanks if you can work magic... !

[filipperimis]

Hi @artemus , any chance you have the code for decompressing zip64 files? Thanks

[bkeefe]

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

[rHumble]

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.

[sujeesh]

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

[KATHERINE_BUSO]

Genius!!!!! 

I applied for Web.Contents and works!!! 

let
ZIPFile = Web.Contents ("https://www4.bcb.gov.br/fis/cosif/cont/plano/PLANODECONTASCONSORCIOSADM.ZIP"),
// 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]]
),
Contents1 = Contents{0},
Content = Contents1[Content],
#"CSV importado" = Csv.Document(Content,[Delimiter=";", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Encabezados promovidos" = Table.PromoteHeaders(#"CSV importado", [PromoteAllScalars=true]),
#"Tipo cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos",{{"#PLANO", type text}, {"COD_CONTA", Int64.Type}, {"NOME_CONTA", type text}, {"DT_INI_VIGEN", Int64.Type}, {"DT_FIM_VIGEN", Int64.Type}, {"NATUREZA", type text}, {"DOC_CD", Int64.Type}, {"SEGMENTO", type text}, {"NUM_DE_COLUNAS", type text}, {"CTA_GRUPO", Int64.Type}})
in
#"Tipo cambiado"

[artemus · Accepted solution]

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"

[Anonymous]

hello, 

please can you give an example on how to use the zip function

thank you

[artemus]

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)

[sujeesh]

Thank you my friend. It worked😀