Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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"
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 🙏
Hi All,
Can someone please tell me how to connect the Azure Devops REST API into the power bi.
Thanks,
Himanshu Jain
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
Hi artemus - how can I use your code for quering .xlsx files form a zipped folder in Power Query? Thank you!
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!
I would appreciate if you could help me with this error.
Greetings
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?
What do you mean, dont work with dev ops?
Could you show an example that will work with this URL?
@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.
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... !
Hi @artemus , any chance you have the code for decompressing zip64 files? Thanks
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 |
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"
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"
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)
Thank you my friend. It worked😀
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
26 | |
12 | |
11 | |
11 | |
8 |
User | Count |
---|---|
53 | |
28 | |
16 | |
14 | |
13 |