topic Creating an IF statement using the previous cell as reference to keep track of different departures in Desktop
https://community.powerbi.com/t5/Desktop/Creating-an-IF-statement-using-the-previous-cell-as-reference-to/m-p/2572241#M908888
<P>I'm currently working with a dataset of bus stops. The data doesn't indicate which specific trip each one belongs to, but it does indicate the license plate of the bus in question, which is my only bet for sorting them out (which I do need to do as part of the job). The problem is these being buses they of course show up multiple times during each day because they aren't doing a single trip, they do a few.</P><P> </P><P>Their data is clustered by trips and as far as I could tell the same bus doesn't have its trips stored next to one another, so I thought of doing it this way: I make a new column with an If statement that checks the cell just before it. If it's the same plate then copy the last value used. If it's different then add 1 to the same value</P><P> </P><P>Something like this</P><P> </P><TABLE border="1"><TBODY><TR><TD>Route</TD><TD>Description</TD><TD><STRONG>PLATE</STRONG></TD><TD>Direction</TD><TD>Stop</TD><TD>Timestamp</TD><TD>People in</TD><TD>People out</TD><TD><STRONG>NEW COLUMN</STRONG></TD></TR><TR><TD><P>123</P></TD><TD>xxx</TD><TD>888</TD><TD>1-2</TD><TD>1</TD><TD>07:10</TD><TD>x</TD><TD>x</TD><TD>1</TD></TR><TR><TD>123</TD><TD>xxx</TD><TD>888</TD><TD>1-2</TD><TD>2</TD><TD>07:20</TD><TD>x</TD><TD>x</TD><TD>1</TD></TR><TR><TD>123</TD><TD>xxx</TD><TD>888</TD><TD>1-2</TD><TD>3</TD><TD>07:30</TD><TD>x</TD><TD>x</TD><TD>1</TD></TR><TR><TD>123</TD><TD>xxx</TD><TD>777</TD><TD>1-2</TD><TD>1</TD><TD>07:10</TD><TD>x</TD><TD>x</TD><TD>2</TD></TR><TR><TD>123</TD><TD>xxx</TD><TD>777</TD><TD>1-2</TD><TD>2</TD><TD>07:20</TD><TD>x</TD><TD>x</TD><TD>2</TD></TR><TR><TD>123</TD><TD>xxx</TD><TD>888</TD><TD>2-1</TD><TD>1</TD><TD>14:10</TD><TD>x</TD><TD>x</TD><TD>3</TD></TR><TR><TD>123</TD><TD>xxx</TD><TD>888</TD><TD>2-1</TD><TD>2</TD><TD>14:15</TD><TD>x</TD><TD>x</TD><TD>3</TD></TR><TR><TD>123</TD><TD>xxx</TD><TD>888</TD><TD>2-1</TD><TD>3</TD><TD>14:20</TD><TD>x</TD><TD>x</TD><TD>3</TD></TR><TR><TD>123</TD><TD>xxx</TD><TD>888</TD><TD>2-1</TD><TD><P>4</P></TD><TD>14:30</TD><TD>x</TD><TD>x</TD><TD>3</TD></TR></TBODY></TABLE><P> </P><P>That way I can then merge the new column with the plate and I would be able to know for certain which data belongs to which specific trip</P><P> </P><P>That's the general idea I have but I don't know what the syntax would have to be or even if it is possible at all</P>Tue, 14 Jun 2022 22:49:34 GMTNotMe2022-06-14T22:49:34ZCreating an IF statement using the previous cell as reference to keep track of different departures
https://community.powerbi.com/t5/Desktop/Creating-an-IF-statement-using-the-previous-cell-as-reference-to/m-p/2572241#M908888
<P>I'm currently working with a dataset of bus stops. The data doesn't indicate which specific trip each one belongs to, but it does indicate the license plate of the bus in question, which is my only bet for sorting them out (which I do need to do as part of the job). The problem is these being buses they of course show up multiple times during each day because they aren't doing a single trip, they do a few.</P><P> </P><P>Their data is clustered by trips and as far as I could tell the same bus doesn't have its trips stored next to one another, so I thought of doing it this way: I make a new column with an If statement that checks the cell just before it. If it's the same plate then copy the last value used. If it's different then add 1 to the same value</P><P> </P><P>Something like this</P><P> </P><TABLE border="1"><TBODY><TR><TD>Route</TD><TD>Description</TD><TD><STRONG>PLATE</STRONG></TD><TD>Direction</TD><TD>Stop</TD><TD>Timestamp</TD><TD>People in</TD><TD>People out</TD><TD><STRONG>NEW COLUMN</STRONG></TD></TR><TR><TD><P>123</P></TD><TD>xxx</TD><TD>888</TD><TD>1-2</TD><TD>1</TD><TD>07:10</TD><TD>x</TD><TD>x</TD><TD>1</TD></TR><TR><TD>123</TD><TD>xxx</TD><TD>888</TD><TD>1-2</TD><TD>2</TD><TD>07:20</TD><TD>x</TD><TD>x</TD><TD>1</TD></TR><TR><TD>123</TD><TD>xxx</TD><TD>888</TD><TD>1-2</TD><TD>3</TD><TD>07:30</TD><TD>x</TD><TD>x</TD><TD>1</TD></TR><TR><TD>123</TD><TD>xxx</TD><TD>777</TD><TD>1-2</TD><TD>1</TD><TD>07:10</TD><TD>x</TD><TD>x</TD><TD>2</TD></TR><TR><TD>123</TD><TD>xxx</TD><TD>777</TD><TD>1-2</TD><TD>2</TD><TD>07:20</TD><TD>x</TD><TD>x</TD><TD>2</TD></TR><TR><TD>123</TD><TD>xxx</TD><TD>888</TD><TD>2-1</TD><TD>1</TD><TD>14:10</TD><TD>x</TD><TD>x</TD><TD>3</TD></TR><TR><TD>123</TD><TD>xxx</TD><TD>888</TD><TD>2-1</TD><TD>2</TD><TD>14:15</TD><TD>x</TD><TD>x</TD><TD>3</TD></TR><TR><TD>123</TD><TD>xxx</TD><TD>888</TD><TD>2-1</TD><TD>3</TD><TD>14:20</TD><TD>x</TD><TD>x</TD><TD>3</TD></TR><TR><TD>123</TD><TD>xxx</TD><TD>888</TD><TD>2-1</TD><TD><P>4</P></TD><TD>14:30</TD><TD>x</TD><TD>x</TD><TD>3</TD></TR></TBODY></TABLE><P> </P><P>That way I can then merge the new column with the plate and I would be able to know for certain which data belongs to which specific trip</P><P> </P><P>That's the general idea I have but I don't know what the syntax would have to be or even if it is possible at all</P>Tue, 14 Jun 2022 22:49:34 GMThttps://community.powerbi.com/t5/Desktop/Creating-an-IF-statement-using-the-previous-cell-as-reference-to/m-p/2572241#M908888NotMe2022-06-14T22:49:34ZRe: Creating an IF statement using the previous cell as reference to keep track of different departu
https://community.powerbi.com/t5/Desktop/Creating-an-IF-statement-using-the-previous-cell-as-reference-to/m-p/2576683#M910032
<P>Hi <LI-USER uid="399934"></LI-USER> ,</P>
<P> </P>
<P>Don't seem to understand your requirements, can you explain the relationship between the PLATE, DIRECTION and ROUTE fields and the desired screenshot of the results?</P>
<P><BR />Looking forward to your reply.</P>
<P><BR />Best Regards,<BR />Henry</P>
<P> </P>Tue, 14 Jun 2022 05:38:16 GMThttps://community.powerbi.com/t5/Desktop/Creating-an-IF-statement-using-the-previous-cell-as-reference-to/m-p/2576683#M910032v-henryk-mstf2022-06-14T05:38:16ZRe: Creating an IF statement using the previous cell as reference to keep track of different departu
https://community.powerbi.com/t5/Desktop/Creating-an-IF-statement-using-the-previous-cell-as-reference-to/m-p/2578721#M910577
<P>Plate is the license plate for the bus.</P><P> </P><P>Direction is which way the bus is going. Either from point a A to point B or from point B to point A. There are busses that do multiple trips of each direction which is why I can't just use that so sepparate them.</P><P> </P><P>Route you can ignore since I'm only working with the data from one specific route, meaning it's the same in every entry. Same with Description.</P><P> </P><P>Also another caveat is that the busses don't all make the same stops each trip. They change depending on where people need to board or get off. They are also string data with no clear indication as to the order they're in. There is a timestamp on each entry though that I just realized I accidentally left out in the table earlier.</P>Tue, 14 Jun 2022 22:46:51 GMThttps://community.powerbi.com/t5/Desktop/Creating-an-IF-statement-using-the-previous-cell-as-reference-to/m-p/2578721#M910577NotMe2022-06-14T22:46:51Z