cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DarkAlex Frequent Visitor
Frequent Visitor

Building sequence of points

I have table with data:

ClientsPointsDateTime
Client 1P111.02.201811:15
Client 1P211.02.201811:17
Client 1P311.02.201811:20
Client 1P111.02.201811:40
Client 1P111.02.201811:35
Client 2P111.02.201812:07
Client 2P111.02.201812:20
Client 2P211.02.201812:27
Client 2P311.02.201812:30
Client 3P111.02.201814:10
Client 3P311.02.201814:22
Client 4P111.02.201815:20
Client 4P211.02.201815:27
Client 4P311.02.201815: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 1P1 - P2 - P3 - P1
Client 2P1- P2 - P3
Client 3P1 - P3
Client 4P1- P2 - P3

 

Top:

P1- P2 - P32
P1 - P2 - P3 - P11
P1 - P31

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Building sequence of points

@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...

image.png

 

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))

image.png

 

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

 

Test146Count = COUNT(Test146Out[Points])

image.png

 

 



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





2 REPLIES 2
Super User
Super User

Re: Building sequence of points

@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...

image.png

 

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))

image.png

 

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

 

Test146Count = COUNT(Test146Out[Points])

image.png

 

 



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





DarkAlex Frequent Visitor
Frequent Visitor

Re: Building sequence of points

@PattemManohar

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

Table:

ClientsDateTimePoints
119.11.201814:50:32TK14
119.11.201814:50:48TK14
119.11.201814:51:39TK14
119.11.201814:50:22TK17
119.11.201814:52:31TK16
1020.11.20189:33:38TK14
1020.11.20189:27:16TK14
1020.11.20189:27:24TK11
1020.11.20189:27:05TK17
1020.11.20189:27:08TK17
1020.11.20189:31:51TK11
1020.11.20189:33:45TK16
10019.11.201812:32:49TK14
10019.11.201812:31:52TK14
10019.11.201812:32:44TK14
10019.11.201812:31:41TK17
10019.11.201812:32:13TK14
10019.11.201812:31:57TK14
10019.11.201812:32:07TK14
10019.11.201812:32:55TK14
10019.11.201812:32:20TK14
10120.11.201810:56:05TK14
10220.11.201810:08:27TK17
10220.11.201810:08:47TK15
10220.11.201810:09:36TK15
10220.11.201810:08:32TK17
10220.11.201810:08:36TK14
10220.11.201810:10:39TK15
10220.11.201810:09:42TK15
10220.11.201810:11:08TK10
10320.11.20188:14:07TK15
10320.11.20188:24:21TK12
10320.11.20188:22:41TK14
10320.11.20188:16:01TK14
10320.11.20188:18:48TK15
10320.11.20188:15:44TK14
10320.11.20188:14:49TK14
10320.11.20188:24:18TK12
10320.11.20188:16:20TK14
10320.11.20188:20:36TK14
10320.11.20188:19:45TK16
10320.11.20188:24:04TK12
10320.11.20188:36:28TK12
10320.11.20188:22:56TK14
10320.11.20188:23:27TK14
10320.11.20188:19:53TK14
10320.11.20188:12:55TK14
10320.11.20188:16:52TK14
10320.11.20188:16:35TK14
10320.11.20188:18:35TK14
10320.11.20188:16:11TK14
10320.11.20188:16:29TK14
10320.11.20188:24:10TK12
10320.11.20188:18:15TK14
10320.11.20188:21:45TK14
10320.11.20188:15:17TK14
10320.11.20188:14:41TK15
10320.11.20188:19:34TK15
10320.11.20188:16:57TK14
10320.11.20188:23:55TK12
10320.11.20188:36:35TK12
10320.11.20188:15:07TK14
10320.11.20188:12:50TK17
10320.11.20188:16:44TK14
10320.11.20188:15:01TK14
10320.11.20188:20:21TK14
10320.11.20188:15:36TK14

 

And the result is:

Data.png

 

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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 30 members 722 guests
Please welcome our newest community members: