Forum Discussion

ITSNev's avatar
ITSNev
Frequent Visitor
4 years ago
Solved

Unable to Combine Data - Custom Function

Hi,   At first I was unable to combine data due to reference other queries to steps, so I split this one query in to 3 seperate queries, but now the Power BI Refresh GW is complaining Processing...
Data Protection
Need Help
  • ImkeF's avatar
    ImkeF
    4 years ago

    Hi ITSNev ,
    this can indeed be a pain.
    Try combining these queries into one like so:

    let
FnGetProjects_ = 
(offset as number) =>
let
    //start_date=eRSActualsStartOfMonth,
    //end_date=MaxProjectEndDate,
    records = Number.ToText(offset),
    header = [  #"Authorization"="Bearer XXXXXXXXXXXXXXXXX",
                #"Content-Type"= "application/json"],
    content = "", //"{""last_date:ex"": [null, ""2022-05-1""]""}",
    url = "https://app.YYYYYYYYYYYYYYYY.cloud/rest/v1/",
    response = Web.Contents(url, 
        [RelativePath = "projects/search?",
            Query=[
                offset = records,
                limit = "500"
                ],
        Content=Text.ToBinary(content),Headers=header]),
    out = Json.Document(response,1252)
in
    out,
    Source = List.Generate (
    () => [offset = 500, actuals = #"FnGetProjects"( 0 ) ] ,
    //each [actuals][total_count] <> 0,
    each not List.IsEmpty([actuals][data]),
    each [offset = [offset] + 500, actuals = #"FnGetProjects_" ([offset]) ],
    each [actuals]
),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"data"}, {"data"}),
    #"Expanded data" = Table.ExpandListColumn(#"Expanded Column1", "data"),
    #"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"XXXXXX"})
in
    #"Expanded data1"
Element115's avatar
Element115
Memorable Member
3 years ago

I wasted weeks!!!  And still after trying to decouple the REST API call from the local queries, it doesn't work.  My issue though is that in order for the first API call to succeed, I need to get IDs from an on-prem DB, so I have no choice but to refer to a local M query to iterate over 100s of IDs using 'each', like so:

each getHandleID([ID_External], getToken())

The function getHandleID() encapsulates the call to the REST endpoint as a POST request. 
The full code is:

//vs 2.0
let
    handleID                = Table.AddColumn(
                                            OLC
                                        ,   "handleID"
                                        ,   each getHandleID([ID_External], getToken())
                                        ,   type text
                                        )
in 
    handleID

So you see that in order to be able to add a new column of IDs coming from a REST API to table OLC, there is no choice when using the Table.AddColumn() function since you need to provide a reference to the source table (OLC).  
I can't simply put the handleIDs in a static file or a DB table because these IDs change over time and it is the API provider that manages that, hence why I need to initiate a REST call everything single time when I want to update the values associated with the IDs because I need the latest IDs to get those values. Talk about an asinine API design.