在Power Query中调用Power BI API [英] Call Power BI API in Power Query

问题描述

我正在尝试从Power BI API服务检索数据，这是通过PowerShell代码完成的。但不幸的是，由于某些情况，我无法将其部署到生产中。因此，现在我正试图在Power BI桌面本身做同样的事情，这样我就可以只从Power query调用Power BI睡觉API。关于POWER QUERY中调用API的博文很多，但它们都需要Power BI App注册的客户端ID，而我没有。我能够在PowerShell中使用我的用户名和密码成功调用，甚至还收到了API的响应。

请找到下面的PowerShell代码，并告诉我是否可以在Power Query中复制相同的代码。

# User credential
$User = 'shahab***@*****.com'
$Pword = ConvertTo-SecureString –String '***password***' –AsPlainText -Force

$Credential = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $User,$Pword

# Connect to service
Login-PowerBIServiceAccount -Credential $Credential  

#Get Bearer token
$headers = Get-PowerBIAccessToken  

$uri = 'https://api.powerbi.com/v1.0/myorg/datasets/f52f2abc-6445-41ee-ce02-3908c6e18dd4/refreshes' 
$refreshes = Invoke-RestMethod -Uri $uri -Headers $headers -Method GET
$xs= $refreshes

谢谢。

推荐答案

使用包装时，您需要类似这样的东西。

WebRequest_Simple(
    "https://api.powerbi.com",
    "v1.0/myorg/datasets/f52f2abc-6445-41ee-ce02-3908c6e18dd4/refreshes",
    [
        Headers = [ Authorization = "Bearer OAuthTokenHere" ]
    ]
)

如果url有一个查询字符串(?之后的部分)，那么您将使用options[Query]

Web响应

完整查询

来源：WebRequest_Simple.pq

let    
    /*
    Wrapper for Web.Contents  returns response metadata 
        for options, see: <https://docs.microsoft.com/en-us/powerquery-m/web-contents#__toc360793395>

    */
    WebRequest_Simple
        =  (
            base_url as text,
            optional relative_path as nullable text,
            optional options       as nullable record
        )
        as record =>
        let 
    
            headers = options[Headers]?, //or: ?? [ Accept = "application/json" ],

            merged_options = [      
                Query = options[Query]?,
                RelativePath = relative_path,
                ManualStatusHandling = options[ManualStatusHandling]? ?? { 400, 404 },
                Headers = headers
            ],
        
            bytes = Web.Contents(base_url, merged_options),
            response = Binary.Buffer(bytes),
            response_metadata = Value.Metadata( bytes ),
            status_code = response_metadata[Response.Status]?,
            json = Json.Document(response),
            Final = [
                request_url = metadata[Content.Uri](),
                status_code = status_code,
                metadata = response_metadata,
                IsJson = not (try json)[HasError],
                response = response,
                json = json
            ]
        in 
            Final,

    tests = {
        WebRequest_Simple("https://httpbin.org", "json"), // expect: json
        WebRequest_Simple("https://www.google.com"),       // expect: html
        WebRequest_Simple(
            "https://api.powerbi.com",
            "v1.0/myorg/datasets/f52f2abc-6445-41ee-ce02-3908c6e18dd4/refreshes",
            [
                Headers = [ Authorization = "Bearer OAuthTokenHere" ]
            ]
        )
    },

    FinalResults = Table.FromRecords(tests,
        type table[
            status_code = text, request_url = text, metadata = record,
            response = binary, IsJson = logical, json = any],
        MissingField.Error
    )    
in
    FinalResults

这篇关于在Power Query中调用Power BI API的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！