问Power Query中调用Power BI接口
EN

Stack Overflow用户

提问于 2021-05-28 20:31:12

回答 1查看 72关注 0票数 0

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

请找到下面的PowerShell代码，并让我知道，如果我们可以复制相同的电力查询。

# 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

谢谢。

api

powerbi

powerquery

powershell

回答 1

Stack Overflow用户

发布于 2021-09-30 16:54:47

有了包装器，你就会想要这样的东西。

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