关于 Azure 应用授权及 API 调用

code换token就不讲了,自己看,,,
https://login.microsoftonline.com/common/oauth2/authorize?client_id=应用程序ID&response_type=code&redirect_uri=回调地址&response_mode=query&scope=Power BI rest API相关权限如Dashboard.ReadWrite.All&state=12345
不要scope的话用参数resource ="https://analysis.windows.net/powerbi/api
返回:回调地址/?code=*****
然后code换token
https://login.microsoftonline.com/common/oauth2/v2.0/token
POST参数
client_id=应用程序ID&
client_secret=应用秘钥&
scope=Power BI rest API相关权限如Dashboard.ReadWrite.All
或者resource ="https://analysis.windows.net/powerbi/api
code=上一步的code值&
redirect_url=回调地址&
grant_type=Authorization_code

注册Power BI应用可以去https://dev.powerbi.com/apps
会得到client_id和client_secret
重点:Azure授权的无人值守模式(隐式授权)
登陆创建资源:https://portal.azure.com
参考:https://docs.microsoft.com/zh-cn/power-bi/developer/embedded/register-app
file
或者
file
创建应用后添加对应你要使用的API权限:
file
选择你需要的权限打钩并进行授权,注意最多选30项,一般测试直接选每个栏的最后一个就差不多了!
以下权限全部设置好即可:

App.Read.All
Capacity.ReadWrite.All
Content.Create
Dashboard.ReadWrite.All
Dataflow.ReadWrite.All
Dataset.ReadWrite.All
Gateway.ReadWrite.All
Group.Read.All
Metadata.View_Any
Report.ReadWrite.All
StorageAccount.ReadWrite.All
Tenant.ReadWrite.All
UserState.ReadWrite.All
Workspace.ReadWrite.All

然后注意下获取客户端应用程序密码:
file
顺便配置下身份验证项:
file

Authorization= "Bearer "&Json.Document(Web.Contents("https://login.windows.net/应用目录ID或者租户ID/oauth2/token",
[Content=Text.ToBinary("client_id=客户端应用程序ID&scope=Files.ReadWrite.All%20Sites.ReadWrite.All&client_secret=客户端应用程序密码&grant_type=client_credentials")]))[access_token]

Power BI Rest API接口调用路由参考(没有Power BI Servise的人慌得一匹):

https://docs.microsoft.com/en-us/rest/api/power-bi/
接口地址中大括号代表的变量你要不会用API获取,就直接进你的PowerBI在线版看下各个项目网址最后一节的信息。
https://app.powerbi.com/

file
file
file
file
file
调用套路:

=Json.Document(Web.Contents("API接口地址",[Headers=[Authorization=上面生成的token]]))
需要POST参数的可以
=Json.Document(Web.Contents("API接口地址",[Headers=[#"Content-Type"="application/json",Authorization=上面生成的token],Content=Json.FromValue([参数值键值对])]))

当然上面接口文档内部有测试工具可以登录自己的账号,界面会自动获取token值,拿到token值后你也可以手动在PQ里面测试,,,
file
file
常用接口:
https://api.powerbi.com/v1.0/myorg/dashboards
https://api.powerbi.com/v1.0/myorg/groups
https://api.powerbi.com/v1.0/myorg/groups/{groupId}/users
https://api.powerbi.com/v1.0/myorg/dashboards/{dashboardId}
https://api.powerbi.com/v1.0/myorg/groups/{groupId}/dataflows
https://api.powerbi.com/v1.0/myorg/groups/{groupId}/dataflows/{dataflowId}
https://api.powerbi.com/v1.0/myorg/datasets
https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets
https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}
https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/tables
file
file
典型的给数据表插入记录
=Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/tables/{tableName}/rows",[Headers=[#"Content-Type"="application/json",Authorization=上面生成的token],Content=Json.FromValue([rows={[name="畅心",分数=99],[name="吹水",分数=666]}])]))
推数据上传速度貌似不给力,,,

Power BI REST API 限制

https://docs.microsoft.com/zh-cn/power-bi/developer/automation/api-rest-api-limitations

file
外国佬开发了一个python库来读取power bi
拿token的方法也贴出他的代码:

import adal
authority_url = 'https://login.microsoftonline.com/testcompany.onmicrosoft.com'
context = adal.AuthenticationContext(
    authority_url,
    validate_authority=True,
    api_version=None
)

token = context.acquire_token_with_username_password(
    resource='https://analysis.windows.net/powerbi/api',
    username='john.doe@testcompany.onmicrosoft.com',
    password='secret',
    client_id='22a6fc5a-46fe-4f85-8168-281ece441742'
)

access_token = token['accessToken']

道高一尺 魔高一丈
https://pbihub.cn/users/44
M与DAX的恩怨纠葛