PROCX or EARLIER function in Power Query

[angelobruch]

Hello community

I developed a chemical consumption telemetry project in an industrial laundry and I have the table below.

I need to know at the moment when the logical state = 1 (column [Estado Lógico]), what is the next instant (Column [Data/Hora]) when the [Id] in question becomes Logical State = 0. Also, if the logical state of a given [Id] repeats = 1, I need it to only show the column [Date/Time] for the next logical state = 0 in the oldest row. An [Id] below as an example using a calculated column based on EARLIER:

Earlier_Desligado = 
CALCULATE(
    MIN('Medição de Químicos'[Data/Hora]),
    FILTER(
        ALL('Medição de Químicos'),
        'Medição de Químicos'[Id]=EARLIER('Medição de Químicos'[Id])&&
        //'Medição de Químicos'[Ranking]=MIN('Medição de Químicos'[Ranking])&&
        'Medição de Químicos'[Estado Lógico]="0"&&
        'Medição de Químicos'[Data/Hora]>=EARLIER('Medição de Químicos'[Data/Hora])
    )
)

Note that the first line already has in the column [Earlier_Desligado] the next moment when the [Id] in question has a logical state = 0. In the other hatched columns, the column [Earlier_Desligado] repeats for the two lines in which the logical state is equal to 1, when it should only show on the first line. From this I intend to calculate the consumption of laundry chemicals based on the time the metering pump contactors remain on.

Here's the problem

Since I will have hundreds of millions of lines, the EARLIER function ends up consuming all the computer's RAM. I managed to get the expected result using the PROCX function in excel, but I would like to do these calculations using Power Query. It's possible?
Here is the test file:

https://1drv.ms/u/s!AmyNP08tqhqpwB7dWxk7HFD0ocMW?e=UdTJhY

Thanks in advance!!!

[v-angzheng-msft]

Hi, @angelobruch 

I'm not sure I'm quite clear on your question, is the second image above the output you expect and you have implemented it with calculated column but want to optimize it?
Generating a calculated column will take up RAM and when you have a large amount of data using a calculated column will take up a lot of computer's RAM, will using a measure work? I created a measure using your formula to get the same result, does the measure help?

__Earlier_Desligado2 = 
CALCULATE(
    MIN('Medição de Químicos'[Data/Hora]),
    FILTER(
        ALL('Medição de Químicos'),
        'Medição de Químicos'[Id]=max('Medição de Químicos'[Id])&&
        'Medição de Químicos'[Estado Lógico]="0"&&
        'Medição de Químicos'[Data/Hora]>=MAX('Medição de Químicos'[Data/Hora])
    )
)

You mentioned that you used a function in Excel to get the desired result, there is a similar function LOOKUPVALUE  in PowerBI, can you get the desired result if you use it in your formula instead?
By the way, if you wish to use PowerQuery for this, here is the PowerQuery  forum, where there are many PowerQuery experts, and maybe you can get a quick solution there.
If you find a better solution, please let me learn it here.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @angelobruch ,

To what I can understand from your description you want to pick up the values of the first datetime after value 1 correct?

So has you present if you first line is picking up line 2 value and the lines 13 and 14 are picking up value of line 15.

To do this in the query editor I believe the best option is to follow the steps below:

• Sort your data by Date and the other columns that make this change in date readable (for example I assume that this is done by Produto)
• Add the following custom column:

if [Estado Lógico] = 0 then [Data/Hora] else null

• Right click the new column that was created and do a FILL UP

This will fill the values that are null with the values that are on the row below.

Can't replicate this on your file since it's connected to your database.