M language - Comparing two consecutive rows in the same colum

[Guinaba]

Hi experts,

I need to create in Power Query the column SKU Logic:

Using the following logic: if Week# col. has the lowest value and Code col. value (r) = value (r+1) then 0 else 1. Any help much appreciate it.

Week#	Code	SKU Logic
1	123	0
2	123	1
3	123	1
4	123	1
5	123	1
6	123	1
7	123	1
1	234	0
2	234	1
3	234	1
4	234	1
5	234	1
6	234	1
7	234	1
1	567	0
2	567	1
3	567	1
4	567	1
5	567	1
6	567	1
7	567	1

 

[Matt Allington]

Have a look at my video here and see if it gives you some inspiration.

 

[Guinaba]

Hi @Matt Allington ,

Thanks a lot! That's a great technique!! Definitely I'll adopt it from now on! May I ask you one last question, considering that I have the col Logic (0,1) now.
Would you know how I can do one more calculation step 4

step 1 - Sort Col A - Asc - Done

step 2 - Sort Col B - Asc - Done

step 3 - Create col. Logic - Done

step 4 - Create col. Coverage using the logic:

=IF Col.Logic=0 then (D1+E1+F1)-G1 else (I1+E2+F2)-G2)

The problem is when the Logic=1 (next row), then I have to replace D2 for I1 and do the calculation for the next rows having Logic=1, I have added the table below illustrating the scenario, sorry if it confusing.

Week#	Code	SKU-DESC	D	E	F	G	SKU Logic	CoverageFirstWk (Col.I)	CoverageNextWeek	CoverageWeeks
1	123	ProdA	30			16	0	14 (I1)		14
2	123	ProdA	D2			2	1		=(14+E2+F2)-G2	12
3	123	ProdA				5	1			7
4	123	ProdA		8		3	1			12
5	123	ProdA				3	1			9
6	123	ProdA				2	1			7
7	123	ProdA		5		4	1			8
1	234	ProdB	25			20	0	5		5
2	234	ProdB				4	1			1
3	234	ProdB				6	1			-5
4	234	ProdB		10		8	1			-3
5	234	ProdB				5	1			-8
6	234	ProdB				3	1			-11
7	234	ProdB		2		6	1			-15
1	567	ProdC	40			16	0	24		24
2	567	ProdC				2	1			22
3	567	ProdC				5	1			17
4	567	ProdC		1		3	1			15
5	567	ProdC				3	1			12
6	567	ProdC				2	1			10
7	567	ProdC		8		4	1			14

I've tried using the following formula:

if [Logic]=1 then
[CoverageFirstWeek]+[E]+[F]-[G] else null

But not sure how to get the value from the previous row, do I have to use to index col tech. again to get a record from one row above?

Kind Regards,
Gilly

 

[Guinaba]

Myabe I should do this step using excel or VBA (offset)