Index of every other record in a table?

[alexanderlee]

Apologies if this has already been answered, I've been unable to find a solution to this. I'm working with a long string contained in a specific field (productivityLog_2.Text) that I'm splitting into a table based on a substring ("<!--splitKronos-->"), and I would like to get the sum of every other record in the table. I can do that by individually specifying the indices I want to sum, e.g. 

Sum(

    Index(Split(productivityLog_2.Text, "<!--splitKronos-->"), 2).Value,

    Index(Split(productivityLog_2.Text, "<!--splitKronos-->"), 4).Value,

    Index(Split(productivityLog_2.Text, "<!--splitKronos-->"), 6).Value,

    Index(Split(productivityLog_2.Text, "<!--splitKronos-->"), etc).Value)

But I have an indefinite number of records to sum. Is there a simple way to specify every other index? I'm thinking of the CSS selector :nth-child(even) but have not found an equivalent selector in the MS formula reference page (https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-first-last)

Thanks in advance!

[poweractivate · Accepted solution]

@alexanderlee 
No it's not possible directly, but it's still actually possible with a creative way like this:

1. App OnStart:
Assuming your Label Control is called productivityLog_2
Then also assuming the text contents (i.e. the Text property of productivityLog_2) look something like this

"10<!--splitKronos-->20<!--splitKronos-->30<!--splitKronos-->40<!--splitKronos-->50"

Your formula for App.OnStart might be something like this:

// Split the string into a collection
ClearCollect(colSplit, Split(productivityLog_2.Text, "<!--splitKronos-->"));

// Generate row numbers for colSplit
ClearCollect(
    colIndexedSplit,
    ForAll(
        Sequence(CountRows(colSplit)),
        Patch(
            Last(FirstN(colSplit, Value)),
            {RowNumber: Value}
        )
    )
);

2. Click App -> Run OnStart (and do this again if you ever change the input string contents). If you want a way to do it where the input can be changed live too, it can be done, but am not going into that for now to keep this response focused on getting it to work first. (Basically if you got this version to work, you may need to move the OnStart part to an OnChange of an Input control, or something like that, for it to work without having to press Run OnStart and just to work on changing the input string directly)

3. Have a new Label control on your screen, and in its Text property put this formula

// Sum values of every other item - use = 1 for every other odd element, = 0 for every other even element

Sum(Filter(colIndexedSplit, Mod(RowNumber, 2) = 1), Value)

Then it should work.

If you use = 1 it should show you 90 (adding items 1, 3, and 5)

and using = 0 should show you 60 (adding items 2 and 4)

for this specific example.

You may change the = 0 to = 1 in real time in the Power Apps Editor without having to click Run OnStart again. Only the input string changing would need the Run OnStart (and even this can be changed by moving the formula in App.OnStart to OnChange of what has your input string, but it may have to be an Input type of Control - I am only using a Label's Text property currently as the input text in this example for simplicity.

See if it helps @alexanderlee 

[alexanderlee]

Thanks a bunch @poweractivate works like a charm!

[poweractivate]

@alexanderlee 

Thanks for marking my response as the solution.
I thought about it more, and I now have a better version of this solution for you, after I thought more about it.

I can take the Patch out from inside the ForAll in an improved version of the formula below, since using Patch inside ForAll should be avoided whenever it is possible to do so. Here, I thought about it more, and it is possible to remove Patch from inside the ForAll and not even use Patch at all actually in this scenario, and to streamline it so we do not even need the indexed intermediate collection either any more.

You may do it without the Patch inside ForAll. First, we can remove the ForAll from the initialization and move it over to the Label actually. Then, we can even take out Patch altogether now (so not even Patch outside ForAll - we can remove Patch altogether) and just perform the summing directly inline.

We can do this by introducing your Index that you brought up in the beginning, actually.

Like below:

1. App.OnStart (or OnVisible - even potentially OnChange of an Input control)

// Split the string into a collection
ClearCollect(colSplit, Split(productivityLog_2.Text, "<!--splitKronos-->"));

Now it should be just the above, without the ForAll and the Patch inside ForAll anymore after it.

2. Now in your Label's Text property - the one that displays the sum, change it to this formula below:

// Sum values of every other item
//Use Mod(Value, 2) = 1 for every other odd element, 
//use Mod(Value, 2) = 0 for every other even element

Sum(
    ForAll(
        Sequence(CountRows(colSplit)),
        If(
            Mod(Value, 2) = 1,
            Index(colSplit, Value).Value,
            0
        )
    ),
    Value
)

As for the ForAll and the Sequence that is inside it, I don't believe this can be optimized further (such as not to use ForAll) as that is the way we can tell the inner part of ForAll (used now by Index) what is the current index of the Collection, and to do that we use the ForAll(Sequence(CountRows(colSplit)), part.

However there is no longer a Patch inside ForAll now, and I've also gotten rid of the intermediate indexed collection entirely, so this version is probably much better than the initial version I gave you.

See if this helps further @alexanderlee