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"
ImkeF's avatar
ImkeF
Community Champion
3 years ago

Hi Element115 ,
not sure I understand.
But it is a common pattern to combine different sources for API calls. 
What is important is that you combine them all into one query if you run into issues.
So in your OLC-query, include the function code like so:

 

OLC-Query:
let
 existing step 1 = ....,
 existing step 2 = ....,
 last step = .... 
 , myCustomFunction = (OLC as table) =>
   let
    handleID                = Table.AddColumn(
                                            OLC
                                        ,   "handleID"
                                        ,   each getHandleID([ID_External], getToken())
                                        ,   type text
                                        )
in 
    handleID,

, applyCustomFunction = myCustomFunction(#"last step"),

in
//last step
applyCustomFunction

 




Element115's avatar
Element115
Memorable Member
3 years ago

Hi Imke, 

 

other than the Microsoft documentation, are there any exhaustive resources (books or websites) where I could learn all these details about M?  I've been looking but not finding anything.