Calculating return clients in M/Power Query

[Anonymous]

Hello all,

I have a problem that I hope you can help me with, or at least point me in the right direction. I can solve it using DAX and copious numbers of calculated columns for each step in the process. However, as a business policy we are transitioning away from using calculated columns in our datamodels and I need to calculate the following in the Query Editor/M. 

I hope you can give me some points how to translate the following to M. Alternative solutions are also welcome. I need to be able to report on COUNTS (so the number of return clients, the number of continued service clients, etc.)

I have a table that resembles the following example. Every row contains a ClientID, a StartDate, EndDate and a HealthServiceCode (the "product" the client receives). A row represents the period a client receives a specific service. A client can have more than one HealthServiceCode at a time, but this is then covered in multiple multiple rows.

ClientID	StartDate	EndDate	HealthServiceCode
4451	01-01-2014	31-12-2014	48371
4453	01-01-2014	31-12-2014	35YP01
4453	01-01-2015	31-12-2015	23TP51
2234	15-06-2019	31-12-2019	23TP50
2234	01-01-2020	31-12-2020	23TP50
3021	01-01-2019	31-12-2019	41DE30
3021	01-07-2020	30-06-2021	41DE30
4451	01-02-2020	30-06-2020	ET50
5982	01-01-2020	31-12-2020	41DE30
6359	01-01-2019	31-12-2019	35YP01
6359	01-01-2020	30-06-2020	35YP01
6359	01-01-2020	30-06-2020	41DE30

I need to calculate a couple of things:

• Whether a client is a return client (definition: Any ClientID who has had an earlier HealthServiceCode)
• Whether a client gets continued service (definition: Any ClientID who is return client with the same HealthServiceCode and where StartDate is within 1 day of the previous' EndDate)

The first time a client appears in the table, he/she should not be tagged as a ReturnClient (because it's the first time they are registered).

The outcomes are binary. Either someone is a return client or they are not, someone receives continued service or they do not not.

So the outcome should resemble something like this:

ClientID	StartDate	EndDate	HealthServiceCode	ReturnClient	ContinuedService
4451	01-01-2014	31-12-2014	48371	0	0
4453	01-01-2014	31-12-2014	35YP01	0	0
4453	01-01-2015	31-12-2015	23TP51	1	0
2234	15-06-2019	31-12-2019	23TP50	0	0
2234	01-01-2020	31-12-2020	23TP50	1	1
3021	01-01-2019	31-12-2019	41DE30	0	0
3021	01-07-2020	30-06-2021	41DE30	1	0
4451	01-02-2020	30-06-2020	ET50	1	0
5982	01-01-2020	31-12-2020	41DE30	0	0
6359	01-01-2019	31-12-2019	35YP01	0	0
6359	01-01-2020	30-06-2020	35YP01	1	1
6359	01-01-2020	30-06-2020	41DE30	1	0

Thanks in advance! Any online resources that can point me to the solution are also welcome, as I need to get better at M anyway.

[ImkeF · Accepted solution]

Hi @Anonymous  

please post this code into the advanced editor and follow the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldAxCsMwDAXQu3gOWPqSknhv9gxZSsj9r9HYqYtaB0PBy8fP4lv7HlSNwxCI43lArGcQjowadJaJwzEUKl0q9lzp3pq3OUC21S4LSH7NFmnM18nbVC15W+eCnC3BWSF8fa2Zq/xYpLHTZy5dhcqNs25laGwOy/ZuYGlGt62bOoqlblu33R/bNvjH1g7HCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ClientID = _t, StartDate = _t, EndDate = _t, HealthServiceCode = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClientID", Int64.Type}, {"StartDate", type date}, {"EndDate", type date}, {"HealthServiceCode", type text}}),
    #"Sorted Rows" = Table.Buffer( Table.Sort(#"Changed Type",{{"ClientID", Order.Ascending}, {"StartDate", Order.Ascending}}) ),
    #"Grouped Rows" = Table.Group
                        (#"Sorted Rows", 
                        {"ClientID"}, 
                        {{"Count", each Table.RowCount(_), type number}, 
                        {"Partition", each let 
                            AddedIndex = Table.AddIndexColumn(_, "Index",0,1), 
                            Result = Table.AddColumn( 
                                        AddedIndex,  
                                        "ContinuedService", 
                                            each if [Index] > 0
                                            then [StartDate] - AddedIndex{[Index]-1}[EndDate]  = #duration(1,0,0,0)
                                                    and [HealthServiceCode] = AddedIndex{[Index]-1}[HealthServiceCode]
                                            else false )
                                    in
                                        Result
                                }},
                        GroupKind.Local),
    #"Expanded Partition" = Table.ExpandTableColumn(#"Grouped Rows", "Partition", {"StartDate", "EndDate", "HealthServiceCode", "Index", "ContinuedService"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Partition", "ReturnClient", each if [Index] = 0 then 0 else 1)
in
    #"Added Custom"

some reading recommendations for the applied methods:

https://www.thebiccountant.com/2017/05/29/performance-tip-partition-tables-crossjoins-possible-powerquery-powerbi/

https://blog.crossjoin.co.uk/2014/01/03/aggregating-by-local-groups-in-power-query/

https://community.powerbi.com/t5/Community-Blog/Bug-warning-for-Table-Sort-and-removing-duplicates-in-Power/ba-p/810390

https://www.youtube.com/watch?v=-3KFZaYImEY

.. attaching the file as well

[Anonymous]

Thanks Imke!

This is definitely fit for purpose. Also many thanks for the resources! Accepted as solution.