excel – Generic function for interpolating missing values in power query

I have written a (relatively) generic function for interpolating values in power query (also useful for power bi and m code).

I am looking for any improvements or input, but especially:

  1. speed
  2. handle more inputs correctly (more generic)
  3. documentation / readability of code
(Input as table, xColumn as text, yColumn as text) =>
//Interpolates missing yColumn values based on nearest existing xColumn, yColumn pairs
let
    Buffer = Table.Buffer(Input),
    //index for joining calculations and preserving original order
    #"Added Main Index" = Table.AddIndexColumn(Buffer, "InterpolateMainIndex", 0, 1),
    #"Two Columns and Index" = Table.RemoveColumns(#"Added Main Index", List.Select(Table.ColumnNames(#"Added Main Index"), each _ <> xColumn and _ <> yColumn and _ <> "InterpolateMainIndex")),
    #"Remove Blanks" = Table.SelectRows(#"Two Columns and Index", each Record.Field(_, yColumn) <> null and Record.Field(_, yColumn) <> ""),
    //index for refering to next non-blank record
    #"Added Sub Index" = Table.AddIndexColumn(#"Remove Blanks", "InterpolateSubIndex", 0, 1),
    //m = (y2 - y1) / (x2 - x1)
    m = Table.AddColumn(#"Added Sub Index",
                        "m",
                        each    (Number.From(Record.Field(_, yColumn))-Number.From(Record.Field(#"Added Sub Index"{(InterpolateSubIndex)+1}, yColumn))) / 
                                (Number.From(Record.Field(_, xColumn))-Number.From(Record.Field(#"Added Sub Index"{(InterpolateSubIndex)+1}, xColumn))),
                        type number),
    //b = y - m * x
    b = Table.AddColumn(m, "b", each Record.Field(_, yColumn) - (#"m") * Number.From(Record.Field(_, xColumn)), type number),
    //rename  or remove columns to allow full join
    #"Renamed Columns" = Table.RenameColumns(b,{{"InterpolateMainIndex", "InterpolateMainIndexCopy"}}),
    xColumnmb = Table.RemoveColumns(#"Renamed Columns",{yColumn, xColumn, "InterpolateSubIndex"}),
    Join = Table.Join(#"Added Main Index", "InterpolateMainIndex", xColumnmb, "InterpolateMainIndexCopy", JoinKind.FullOuter),
    //enforce orignal sorting
    #"Sorted by Main Index" = Table.Sort(Join,{{"InterpolateMainIndex", Order.Ascending}}),
    #"Filled Down mb" = Table.FillDown(#"Sorted by Main Index",{"m", "b"}),
    //y = m * x + b
    Interpolate = Table.ReplaceValue(#"Filled Down mb",null,each ((m) * Number.From(Record.Field(_, xColumn)) + (b)),Replacer.ReplaceValue,{yColumn}),
    //clean up
    #"Remove Temporary Columns" = Table.RemoveColumns(Interpolate,{"m", "b", "InterpolateMainIndex", "InterpolateMainIndexCopy"}),
    #"Restore Types" = Value.ReplaceType(#"Remove Temporary Columns", Value.Type(Input))
in
    #"Restore Types"

Lastly, there were no tags for power query, power bi, or interpolating. I’m not sure what other tags I can use for this post besides excel.

Returning DISTINCT values in a column in the middle of a result set

Hello everyone and thank you for your assistance.

I have limited permissions to the db in question but need to return a very specific result set with a specific format.

My example query is:

SELECT
” AS “EX1”, ‘0’ AS “EX2”, REPLACE(APMTO,’ ‘,’0′) AS “EX3”,
PANFPOS.APOURF4 AS “EX4”, PANFPOS.APKDNR AS “EX5”, PANFPOS.APGLNR AS “EX6”,
PBSTKOP.BKLIEF AS “EX7”, PBSTKOP.BKLNAM AS “EX8″, REPLACE(PBSTKOP.BKDTLD,’,’,”) AS “EX9″,
REPLACE(PANFPOS.APDTB,’,’,”) AS “EX10”, PANFPOS.APPCOD AS “EX11”, ‘-‘ AS “EX12″,
REPLACE(APBSTM,’.000′,”) AS “EX13″, ” AS “EX14”, ‘0’ AS “EX15″,
” AS “EX16”, ‘UNDELIVERED’ AS “EX17”

FROM PANFPOS, PBSTKOP

WHERE PBSTKOP.BKLSB = ” AND PBSTKOP.BKDTLD <= 20200925

Result Set looks like:

Result Set

My question is, how do I enforce DISTINCT values on the EX6 column? I cannot use DISTINCT in the middle of the select statement (as far as I know) so any alternative solutions are much appreciated and any suggestions on cleaning up my syntax/query performance would also be appreciated.

microsoft excel – Look for Date values in embedded in strings

I have a value in a cell that might have a 2-digit month and 2-digit year value embedded in it, and I need a function that would return TRUE if that is found.

Sample input data: sCa!1220LB6qHZbCnsEE where 1220 would be for December 2020, and the formula should return true for that input.

Sample range of “valid” digits to look at:

0120
0220
0320
0420
0520
0620
0720
0820
0920
1020
1120
1220

Reason: A certain company has some fairly complex password rules, to the point I have a spreadsheet to count things like number of UPPER CASE characters, lower case characters, certain special characters, etc. Well, they just added a check for a password like password0620 that has 0620 in it, for June 2020.

I do have access to the XLOOKUP and XMATCH functions, but I am not sure if those would help.

Linux Mint: Permamente measured values CPU as Sensor App?

I would like to have the processor temperature displayed within Linux Mint. Currently I only have the sensor command which shows the actual temperature but does not offer a permanent monitoring.

Currently I use the sensor command inside the terminal: sensors

Question: But how can I have a permanent monitoring, preferably as desktop APP as display?

Does anyone have any ideas ?

8 – Missing form state values after adding submit function

I added a submit function in the form_alter hook when inserting a node:

$form('actions')('save_as_draft') = (
  '#type' => 'submit',
  '#value' => t('Save as Draft'),
  '#submit' => ('save_as_draft'),
  '#limit_validation_errors' => (),
);

In the save_as_draft() function, I want to get the address value from the form state, but there are no values (except for the additional submit button).

function save_as_draft(&$form, DrupalCoreFormFormStateInterface $form_state) {
  var_dump($form_state->getValue('field_address'));                      // outputs NULL
  var_dump($form_state->get('field_address'));                           // outputs NULL 
  var_dump($form('field_address')('widget')(0)('value')('#value'));      // outputs 'Address value'
  var_dump(array_keys($form_state->getValues())); // outputs { (0)=>  "save_as_draft" (1)=> "op" }
}

How can I get the form_state values in this alternative submit function without validating the whole form?

Is there another way of retrieving the form values instead of digging through the widget arrays?

8 – How do I create a select field that looks up values from a database?

As the title says. How would I go about setting up a ‘lookup’ function to essentially allow a user to select values from a select or dropdown field (for example), where these values are stored in a database.

I know my way around the Webform module to a decent level but cannot see any options/settings to set up this kind of functionality, given it is quite a common requirement for forms.

The ‘Add webform options’ isn’t something I can use, as there are numerous values, too many to enter manually.

Any help would be appreciated!

functional analysis – Why does the argument principle of an ‘unknown’ complex-valued function always give non-zero values?

This question is motivated by a physics problem, and so uses non-standard notation.

I have a 2D parameter space $mathcal{M}=(x,y)$. Over it, I choose a smooth, closed, non-intersecting curve $partialmathcal{M}$ parameterized by $t$ (i.e. $x(t),y(t)$).
Only once this $partialmathcal{M}$ is chosen, I can calculate values of a loop-dependent function $z($x(t),y(t)$)inmathbb{C}$ at points on the loop. In other words, the function’s values depend on the tangential direction at each point of the loop $partialmathcal{M}$.

Then, I have the integral $$frac{1}{2pi i}oint_{partialmathcal{M}} frac{z'(t)}{z(t)} dt=ninmathbb{Z},$$ where the derivative $’$ is carried with respect to $t$. This integral gives me the complex winding number of $z($x(t),y(t)$)$ for the loop.
If this is a valid application of the argument principle (i.e. if $z$ is a meromorphic function, and no zeros/poles lie on $partialmathcal{M}$), then $n=Z-P$, where $Z$ and $P$ are respectively the number of zeros and poles inside $partialmathcal{M}$ counted with multiplicity.

Now, my goal is to understand what $z$ means and how it relates to the parameter space $mathcal{M}$ despite being dependent on $t$. This is because I do not know, a priori, of a meromorphic function $f(x,y)$ that is independent of $t$, whose zeros and poles the above integral might give if $z($x(t),y(t)$)rightarrow f(x,y)$. Such a function is desirable in my problem.

What bothers me is that if I numerically take several small $partialmathcal{M}$ over a finite $mathcal{M}$, I get non-zero $n (i.e. n=pm 1)$ almost everywhere except at a few points. Why could this be? This makes me worry that there are zeros or poles everywhere, making application of the argument principle invalid as there is bound to be at least one on the loop.
Otherwise, is the argument principle not applicable because $z$ depends on the chosen loop’s tangential direction? I have thoroughly verified the accuracy of my code.

Confusingly, in the physics context, the $(x,y)$ points with $n=0$ correspond to zeros of a different, well-studied loop-$textbf{in}$dependent function $g(x,y)$; but the $n=pm 1$ values observed everywhere else seem anomalous from an argument principle perspective. Yet, I found that $n=pm 1$ actually corresponds to the sign of another function $h(x,y)$.
So, I am having trouble making sense of these numerical experiments on black box results.

Clearly I have confused myself and am having trouble applying the argument principle to this physics problem, so I could use any advice in trying to think through this.

google sheets – How to query a dataset while calculating cumulative values (and filtering by thresholds)

I was hoping someone can help optimize this spreadsheet, where I need to QUERY a dataset while filtering by the cumulative sum of a column by certain thresholds.

Here’s the spreadsheet if you want to take a look but I just want to incorporate the columns in yellow in the query, so I just have to use one formula. The query result would be only the rows where Column O “in threshold” is “Yes”:
enter image description here

nginx – List of all Permissions-Policy header keys and values?

Does someone have a list of all Permissions-Policy header keys and values?

What I have:

more_set_headers "Permissions-Policy: camera=(self); fullscreen=(self); geolocation=(self); payment=()";

It was somehow (old – don’t use it):

more_set_headers "Feature-Policy: camera 'self'; fullscreen *; geolocation 'self'; payment 'none'";

Extra: How can I make fullscreen recursive again?