Forum Discussion
Unable to Combine Data - Custom Function
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"
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
Other quick question re syntax: given the following function def:
addColumn = (T as table, col_name as text, f as function, col as list, token as text, col_type as type) =>
how can you refer to col, a column that belongs to table T?
For ex, this does not work as PQ complains that [ID_External] is an unknown identifier:
add_handleID = addColumn(Source, "handleID", getHandleID, [ID_External], getToken(), type text),
Is there a way to pass the name of column as a param instead of the column itself as a list? Or is that the wrong way of doing it?
I should add: [ID_External] belongs to Source and is of type number. So if I pass the param as Source[ID_External] instead of just [ID_External], I get the error: The column 'col' of the table wasn't found. The function definition looks like this:
addColumn = (T as table, col_name as text, f as function, col as list, token as text, col_type as type) =>
let
new_col = Table.AddColumn(
T
, col_name //"handleID"
, each f(T[col], token) //getHandleID([ID_External], getToken())
, col_type
)
in
new_col,I found the answer!
addColumn = (T as table, new_col_name as text, f as function, col_name as text, token as text, col_type as type) => let new_col = Table.AddColumn( T , new_col_name //"handleID" , each f(Record.Field(_, col_name), token) //getHandleID([ID_External], getToken()) , col_type ) in new_col,Credit goes to Lz-3068 who suggested this answer here:
https://learn.microsoft.com/en-us/answers/questions/253951/using-a-variable-as-a-column-name-in-power-query.html