Increment the cell value by 1 based on the IF condition criteria

[rajrajsha]

Hi, All

Im new to this chat.

Im looking for one of the solution in power query. can you team suggest me the solution.

I tried the way in PQ and i got stuck in incrementing the cell based on criteria.

below image is the solution in need. (colulmn E is the solution i need in PQ)

Below is the excel formal in E column, that im trying to implement in PQ.

ID	Allot #	In order	Out order	0
26	46419	10/18/2017 11:01	10/18/2017 14:23	1
35	62010	11/7/2016 16:18	12/8/2016 9:35	2
35	52291	11/8/2016 8:57	12/5/2016 14:32	2
35	3609	11/14/2016 9:07	11/14/2016 10:00	2
35	11500	12/22/2016 12:53	1/3/2017 12:55	3
35	24070	12/27/2016 7:54	12/27/2016 8:30	3
35	70007	12/27/2016 10:36	1/11/2017 14:44	4
35	23158	1/11/2017 7:53	1/11/2017 9:15	4
35	97907	1/23/2017 7:35	2/3/2017 12:46	5
35	97941	1/23/2017 10:20	2/20/2017 14:43	5
35	18860	1/30/2017 10:23	1/31/2017 6:49	5
35	78148	2/1/2017 11:36	2/1/2017 12:10	6
35	2937	2/2/2017 16:21	2/17/2017 8:27	7
35	66930	5/1/2017 7:53	5/23/2017 21:10	8
35	86329	5/4/2017 11:11	5/4/2017 12:48	8
35	98855	6/27/2017 8:39	8/1/2017 10:17	9
35	43489	7/6/2017 10:23	7/6/2017 10:59	9
35	43443	7/6/2017 10:23	7/6/2017 11:04	9
35	25018	7/6/2017 11:06	8/8/2017 11:44	10
35	43636	8/24/2017 9:11	10/24/2017 8:48	11
35	5863	9/6/2017 8:56	9/6/2017 11:21	11
35	38865	9/28/2017 7:47	9/28/2017 9:20	12
35	57490	9/28/2017 7:59	9/28/2017 8:40	12
39	96871	10/6/2016 15:41	1/3/2017 10:15	13
39	71507	10/12/2016 10:24	10/12/2016 12:19	13
39	81386	10/31/2016 11:17	1/5/2017 12:07	14
39	69521	11/22/2016 9:12	12/19/2016 13:10	14
39	2961	12/8/2016 9:04	12/8/2016 11:03	14
43	474989	3/3/2017 13:11	3/21/2017 12:40	15
43	712549	3/9/2017 7:51	4/12/2017 15:44	15
43	955454	3/14/2017 9:38	3/14/2017 11:21	15

 I have added a sample of date for your convinence. Thanks for you reply.

Please ask any question if not able to understand my explination.

Thanks

[v-juanli-msft · Accepted solution]

Hi @rajrajsha 

Check my queries and apply on your side.

If you have any problem, feel free to let me know.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZRdkuwgCIW3MtXPUxX5Fd1K1+x/GxfBaMx9mslpPsEj8P1+UD+/H1aG5n+hXGAXFqg/AL3AS+KONKTP3+/3Q+L/qv9QhgRXHUH6A9rBhoKXpdJ6hOKmBLFBUjPGutSEZB7DnfCkSEtLCPg+udRTgdJLOTEACcnPRpxR2CUuctG8mQuRYmPIpd7YvFrtwqdincqJ1VJKPYO8JtLI5oXeRvI4iR/pCMSOoHrXeAutg5xQqy2TXUg3lKc9LsYjtxwQwwF5gRim+ecukE4MzLSkZ2Vj08VZoXZuJ1UN2OJoWG0VZmwBe3SQPrxoVLOcGaIdIZmainUcEfXRiNriKeQ67ZN1S4RMZBsyJYyCL17VARyC+2cn1MyiV3Q+8KiGxim2rlQ6jPLahpjYRky99DTvKUj7D+J3zAvyIeUTQikxf0eMRnl7sqP73I1HKqUMQl7dNsf/ViytgMf4izs40t+5fIz1+e2pEF4MeSOFjxfa/VRcD6FlP8JzZVRu5UWlX0vw+p5U/KhW5y10DqP02f27+WOsgDZWfWXUufvuleHW80vyzm0v0oBMMyyHQqOnckhl9VSezpvTJtOotaT8ATAXCbR5EmUHP0FsCq9tW/gQxvvTpqKj2N2MfqRlA+V7++cezPRTNlcBhZNr6xUGxtOVGgbzC2siHKuT5qIeT0x2CKtVnPv7Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"Allot #" = _t, #"In order" = _t, #"Out order" = _t, #"0" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Allot #", Int64.Type}, {"In order", type datetime}, {"Out order", type datetime}, {"0", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 2, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"ID", "Out order"}, {"Added Index1.ID", "Added Index1.Out order"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Added Index1",{{"Added Index1.ID", "previous.ID"}, {"Added Index1.Out order", "previous.Out order"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Index.1"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if [ID]=[previous.ID] and  [In order]<=[previous.Out order] then 0 else 1),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Index", Order.Ascending}}),
    #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Custom.1", each List.Sum(List.FirstN(#"Sorted Rows"[Custom],[Index])))
in
    #"Added Custom1"

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

[BYENER]

Hi @v-juanli-msft ,

I used this solution too. It works good, but my report is very slow now. After this step I tried to do a groupby, but it's still loading. Did I something wrong?

Kind regards,

B. Yener

[rajrajsha]

v-juanli-msft, thank you soo much, the given code worked fine. really i struggled a lot but you solved it. Thanks a lot.

I just want to know about .pbix file, which you have attahced. please let me know.

Is there any thing that i need to do from my end.

[Mariusz]

Hi @rajrajsha 

Can you paste your sample from excel into the body of the message? 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[rajrajsha]

Hi, I have copied sample data for your reference. Last column is the result that i expect.

Thanks for your reply.

Im sorry for the delay reply.