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
Yes. I was meaning to thank you in fact in the other post you made where you actually go over this. It helped steer me in the right direction. Nonetheless, it is annoying that one has to do this. It is not elegant and that is why I wrote up the comment for Microsoft because surely they can do better.
I say this because I have 3 custom functions under the Other folder. Then I call one function in a query in the Staging folder. Pass that query as a reference to the next query in Staging where I call another custom function, and repeat a 3rd time with a 3rd function.
But as things stand today, I have to cram all 3 custom function definitions inside 1 query. The query LOC now has exploded to over 100!Anyhow, thank you very much for your help, Imke.
I'm also 100% with you on this Element115 as its stupid that you have to do this. Some of my queries are HUGE now as a result of having to cover all REST API Calls into one query so we don't get the combine error. Even if say ignore privacy, it ignores this and fails on the combine. Very shortsighted of the designers.
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.
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
