Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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
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 , 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😀
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"
= 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 screenshot
Add this line as your next step:
= Table.FromRecords(Json.Document(Source,65001)[value])
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.