Nesting This Function

[schwiggiddy]

I'm attempting to have PBI do some linear regression for me. From a helper query that iterates over multiple files, I'm calling this custom function to determine the highest reading above and lowest reading below the linear regression line over a variable length period for each row of data. It's working, but I'm getting an error on the service that says it cannot schedule a refresh because of (I think) the source of this query/function (it says Query1; I called this fnQuery1. The error is about a dynamic data source; this seems dynamic).

How would/could I go about integrating this function back into the helper query's main line of operations so as to eliminate the cause for the error? Two of the variables being passed to it are lists (reading and antiindex) and the other two are decimal numbers.

Have I overcomplicated the matter? Is there an easier way to do this that I've overlooked? Any help in the right direction would be most appreciated.

(reading as any, slopeb, intercepta, antiindex) =>
let
// Convert single values to single entry lists
    listslopeb = {slopeb},  
    listintercepta = {intercepta},

// Combine 4 lists into a 4 column table
    Source = Table.FromColumns({reading,listslopeb,listintercepta,antiindex},{"Reading", "Slopeb", "Intercepta", "AntiIndex"}), 

// Fill the single value columns down to all rows
    FillDown = Table.FillDown(Source, {"Slopeb", "Intercepta"}),

// Calculate the position of the linear regression line at each antiindex value
    AddY = Table.AddColumn(FillDown, "YValue", each [Intercepta] + [AntiIndex] * [Slopeb]),

// Determine highest and lowest values from the linear regression line 
    SubtractReading = Table.AddColumn(AddY, "Delta", each [Reading] - [YValue]),
    MaxDelta = Table.AddColumn(SubtractReading, "Max", each List.Max(SubtractReading[Delta])),
    MinDelta = Table.AddColumn(MaxDelta, "Min", each List.Min(SubtractReading[Delta])),

// Return just a single row of data since the max and min are the same for each row
    Return = Table.FirstN(MinDelta, 1)
in
    Return

 

[JGordon11]

I'm not sure about what you are asking, but if you are asking how to incorporate a custom function into a query, then here's an example:

Here's an example of an independent function that multiplies each number in a row of a table by a multiplier:

(Source as table, rw as number, multiplier as number) as table =>
let
    ttr = Table.ToRows(Source),
    ltt = List.Transform(ttr{rw},each _*multiplier),
    lstNew = List.FirstN(ttr,rw) & {ltt} & List.Skip(ttr,rw+1),
    tfr = Table.FromRows(lstNew,Table.ColumnNames(Source))
in
    tfr

I can incorporate it into another query like this:

let
    Row_Mult = (Source as table, rw as number, multiplier as number) as table =>
        let
            ttr = Table.ToRows(Source),
            ltt = List.Transform(ttr{rw},each _*multiplier),
            lstNew = List.FirstN(ttr,rw) & {ltt} & List.Skip(ttr,rw+1),
            tfr = Table.FromRows(lstNew,Table.ColumnNames(Source))
        in
            tfr,
    Tbl = Table.FromRecords({
                                [Col1 = 1, Col2 =2, Col3 = 3],
                                [Col1 = 4, Col2 =5, Col3 = 6],
                                [Col1 = 7, Col2 =8, Col3 = 9]}),
    Result = Row_Mult(Tbl,1,.01)
in
    Result

 

[schwiggiddy]

I'm not sure about what you are asking, but if you are asking how to incorporate a custom function into a query, then here's an example:

Here's an example of an independent function that multiplies each number in a row of a table by a multiplier:

(Source as table, rw as number, multiplier as number) as table =>
let
    ttr = Table.ToRows(Source),
    ltt = List.Transform(ttr{rw},each _*multiplier),
    lstNew = List.FirstN(ttr,rw) & {ltt} & List.Skip(ttr,rw+1),
    tfr = Table.FromRows(lstNew,Table.ColumnNames(Source))
in
    tfr

I can incorporate it into another query like this:

let
    Row_Mult = (Source as table, rw as number, multiplier as number) as table =>
        let
            ttr = Table.ToRows(Source),
            ltt = List.Transform(ttr{rw},each _*multiplier),
            lstNew = List.FirstN(ttr,rw) & {ltt} & List.Skip(ttr,rw+1),
            tfr = Table.FromRows(lstNew,Table.ColumnNames(Source))
        in
            tfr,
    Tbl = Table.FromRecords({
                                [Col1 = 1, Col2 =2, Col3 = 3],
                                [Col1 = 4, Col2 =5, Col3 = 6],
                                [Col1 = 7, Col2 =8, Col3 = 9]}),
    Result = Row_Mult(Tbl,1,.01)
in
    Result

YES! This helped me move forward. Thank you.

Unfortunately, the PBI service still doesn't like the data source for Query1 to enable scheduled refresh. I had thought the issue was in the function, but no such luck, as I've deleted the external function and still get that error. The search continues.