Rest APIs in Powerquery for PowerBI with directquery as live datasets

[link-ralf]

Hi all,

I want to integrate a PowerQuery in PowerBI, to genarate Reports with the result Data of an API endpoint. For me, the difficulty is, to setup this as a directquery, so that the report comes with fresh data, if someone opens the report in the published online version.

The power query takes care of the authentication and picks up the data. Has someone of you any idea, how to do this?

Here is my Powerquery for a better understanding, I would really appreciate any help or tips.

`
let
CreateHeaders = () => let

    ApplicationAuthorizationUrl = "https://api.dataserver.de/api/v1/authorize",
    ApplicationAuthorizationOptions =
        [
            Content = Json.FromValue(
                [
                    ClientId = ClientId,
                    ClientSecret = ClientSecret
                ])
        ],
    ApplicationAutorizationResponse = Json.Document(Web.Contents(ApplicationAuthorizationUrl, ApplicationAuthorizationOptions)),
    ApplicationToken = ApplicationAutorizationResponse[Data][Token],

    UserAuthorizationUrl = "https://api.dataserver.de/api/v1/token",
    UserAuthorizationOptions =
        [
            Content = Json.FromValue(
                [
                    Username = Username,
                    Password = Password,
                    ApplicationToken = ApplicationToken
                ])
        ],
    UserAuthorizationResponse = Json.Document(Web.Contents(UserAuthorizationUrl, UserAuthorizationOptions)),
    UserToken = UserAuthorizationResponse[Data][Token],

    CustomerListUrl = "https://api.dataserver.de/api/v1/customer",
    CustomerListOptions = [
        Headers = [
            Authorization = Text.Combine({"Bearer ", UserToken})
        ]
    ],
    CustomerListResponse = Json.Document(Web.Contents(CustomerListUrl, CustomerListOptions)),
    ActiveCustomerIndex = 0,
    CustomerId = CustomerListResponse[Data]{ActiveCustomerIndex}[CustomerId],

    Result = 
        [
            #"X-Customer-Id" = CustomerId,
            Authorization = Text.Combine({"Bearer ", UserToken}),
            #"X-l3c-Mandant" = MandantNr,                
            #"X-l3c-Filiale" = FilialeNr
        ]    
in
    Result,

FetchRows = (Servicename as text, Endpoint as text, Orderbycolumn as text) => let  

    BaseUrl = "https://api.dataserver.de/service/PDHub/",
   
    Options = [
        Headers = CreateHeaders(),
        RelativePath=Endpoint, //"v1/Personal",
        Query = [orderby=Orderbycolumn]   //"PersonalNr"
    ], 
    Url = Text.Combine({BaseUrl, Servicename, "/api"}),

    Result = Web.Contents(Url, Options)   
in
    Result,

RowsToTable = (Servicename as text, Endpoint as text, Orderbycolumn as text) => let
    initial = () =>
    [
        Top = 500,
        Count = 0,
        CountAll = 1,
        Rows = {}
    ],

    condition = each not ([Rows] is null),
    next = (state) => let
        Response = Json.Document(FetchRows(Servicename, Endpoint, Orderbycolumn)),
        Rows =  if (state[Count] < state[CountAll]) then
            Response[Data]
        else
            null
    in
        [
            Top = state[Top],
            Count = state[Count] + Response[Count],
            CountAll = Response[CountAll],
            Rows = Rows
        ],
    selector = each [Rows],
    allDataListList = List.Generate(initial, condition, next, selector),
    allDataFlat = List.Combine(allDataListList),
    allDataTableSingleColumn = Table.FromList(allDataFlat, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    allDataTableColumnsExpanded = Table.ExpandRecordColumn(allDataTableSingleColumn, "Column1", Record.FieldNames(allDataTableSingleColumn[Column1]{0}), Record.FieldNames(allDataTableSingleColumn[Column1]{0}))
in
    allDataTableColumnsExpanded

in
RowsToTable`

[ptyx507x]

Is this something that you're running through the Power Query SDK as a new connector? or is this an M script that you're trying to run inside of Power BI Desktop?