Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

 

 

 

Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

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

     

     

2 Replies

  • PattemManohar's avatar
    PattemManohar
    Icon for Community Champion rankCommunity Champion
    7 years ago

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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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:

       

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