Building sequence of points

[DarkAlex]

I have table with data:

Clients	Points	Date	Time
Client 1	P1	11.02.2018	11:15
Client 1	P2	11.02.2018	11:17
Client 1	P3	11.02.2018	11:20
Client 1	P1	11.02.2018	11:40
Client 1	P1	11.02.2018	11:35
Client 2	P1	11.02.2018	12:07
Client 2	P1	11.02.2018	12:20
Client 2	P2	11.02.2018	12:27
Client 2	P3	11.02.2018	12:30
Client 3	P1	11.02.2018	14:10
Client 3	P3	11.02.2018	14:22
Client 4	P1	11.02.2018	15:20
Client 4	P2	11.02.2018	15:27
Client 4	P3	11.02.2018	15:30

And I need to build a sequence of points (route) for each client and make a top10 of them. As a result I want to see:

Client 1	P1 - P2 - P3 - P1
Client 2	P1- P2 - P3
Client 3	P1 - P3
Client 4	P1- P2 - P3

Top:

P1- P2 - P3	2
P1 - P2 - P3 - P1	1
P1 - P3	1

[PattemManohar · Accepted solution]

@DarkAlex Please add an Index field in Power Query Editor and then add an supporting column in Data pane as below:

ExcludeFlag = 
VAR _CurrVal = Test146MultiRowConcat[Points]
VAR _PrevVal = LOOKUPVALUE(Test146MultiRowConcat[Points],Test146MultiRowConcat[Clients],Test146MultiRowConcat[Clients],Test146MultiRowConcat[Date],Test146MultiRowConcat[Date],Test146MultiRowConcat[Index],Test146MultiRowConcat[Index]-1)
RETURN IF(_CurrVal=_PrevVal,"Y","N")

Now, your table will look like this...

Then, create a "New Table" as below (which will be the expected output)

Test146Out = SUMMARIZE(FILTER(Test146MultiRowConcat,Test146MultiRowConcat[ExcludeFlag]="N"),Test146MultiRowConcat[Clients],"Points",CONCATENATEX(Test146MultiRowConcat,Test146MultiRowConcat[Points],"-",Test146MultiRowConcat[Date],ASC,Test146MultiRowConcat[Time],ASC))

You can then create a measure to see the count for each Points as below

Test146Count = COUNT(Test146Out[Points])

[DarkAlex]

@PattemManohar

On this sample everything is ok, but I tried this on my data and have problem with repeating of points.

Table:

Clients	Date	Time	Points
1	19.11.2018	14:50:32	TK14
1	19.11.2018	14:50:48	TK14
1	19.11.2018	14:51:39	TK14
1	19.11.2018	14:50:22	TK17
1	19.11.2018	14:52:31	TK16
10	20.11.2018	9:33:38	TK14
10	20.11.2018	9:27:16	TK14
10	20.11.2018	9:27:24	TK11
10	20.11.2018	9:27:05	TK17
10	20.11.2018	9:27:08	TK17
10	20.11.2018	9:31:51	TK11
10	20.11.2018	9:33:45	TK16
100	19.11.2018	12:32:49	TK14
100	19.11.2018	12:31:52	TK14
100	19.11.2018	12:32:44	TK14
100	19.11.2018	12:31:41	TK17
100	19.11.2018	12:32:13	TK14
100	19.11.2018	12:31:57	TK14
100	19.11.2018	12:32:07	TK14
100	19.11.2018	12:32:55	TK14
100	19.11.2018	12:32:20	TK14
101	20.11.2018	10:56:05	TK14
102	20.11.2018	10:08:27	TK17
102	20.11.2018	10:08:47	TK15
102	20.11.2018	10:09:36	TK15
102	20.11.2018	10:08:32	TK17
102	20.11.2018	10:08:36	TK14
102	20.11.2018	10:10:39	TK15
102	20.11.2018	10:09:42	TK15
102	20.11.2018	10:11:08	TK10
103	20.11.2018	8:14:07	TK15
103	20.11.2018	8:24:21	TK12
103	20.11.2018	8:22:41	TK14
103	20.11.2018	8:16:01	TK14
103	20.11.2018	8:18:48	TK15
103	20.11.2018	8:15:44	TK14
103	20.11.2018	8:14:49	TK14
103	20.11.2018	8:24:18	TK12
103	20.11.2018	8:16:20	TK14
103	20.11.2018	8:20:36	TK14
103	20.11.2018	8:19:45	TK16
103	20.11.2018	8:24:04	TK12
103	20.11.2018	8:36:28	TK12
103	20.11.2018	8:22:56	TK14
103	20.11.2018	8:23:27	TK14
103	20.11.2018	8:19:53	TK14
103	20.11.2018	8:12:55	TK14
103	20.11.2018	8:16:52	TK14
103	20.11.2018	8:16:35	TK14
103	20.11.2018	8:18:35	TK14
103	20.11.2018	8:16:11	TK14
103	20.11.2018	8:16:29	TK14
103	20.11.2018	8:24:10	TK12
103	20.11.2018	8:18:15	TK14
103	20.11.2018	8:21:45	TK14
103	20.11.2018	8:15:17	TK14
103	20.11.2018	8:14:41	TK15
103	20.11.2018	8:19:34	TK15
103	20.11.2018	8:16:57	TK14
103	20.11.2018	8:23:55	TK12
103	20.11.2018	8:36:35	TK12
103	20.11.2018	8:15:07	TK14
103	20.11.2018	8:12:50	TK17
103	20.11.2018	8:16:44	TK14
103	20.11.2018	8:15:01	TK14
103	20.11.2018	8:20:21	TK14
103	20.11.2018	8:15:36	TK14

And the result is:

I don't realize, what I'm doing wrong. Can you help me with it?