Conditional Column

[Brock_Hardchest]

Hello,

New to power query, I need to change a column based off of another column. The logic is that if Data Tier List.Column1 is not null (in the first case it is 51985) then the quantity of 1 should be changed to 55.9140625 (same as the other quantity for the other 51985). Since the next item is 2596 then it's quantity of 1 should become 142.7138672. The next item is null and should remain unchanged.

Does anyone know if it is possible to do this? Would I have to write it out manually in M?

 

[Automatrix]

Do these always come in pairs, or can there be more than two subsequent rows with the same value in Data Tier List.Column1?

 

[Brock_Hardchest]

Do these always come in pairs, or can there be more than two subsequent rows with the same value in Data Tier List.Column1?

Always pairs, and one of the quantities is always 1

 

[Automatrix]

In that case, you can create two index columns, and have one start with 0 and the other with 1. Then do a merge with itself, but using the two different indexes.

Then when you expand the quantity from the table, it will have been offset one row down.

Then you can do a conditional column: