Calculating return clients in M/Power Query

Question

Hello all,&nbsp;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.&nbsp;&nbsp;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.)&nbsp;&nbsp;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.&nbsp;ClientIDStartDateEndDateHealthServiceCode445101-01-201431-12-201448371445301-01-201431-12-201435YP01445301-01-201531-12-201523TP51223415-06-201931-12-201923TP50223401-01-202031-12-202023TP50302101-01-201931-12-201941DE30302101-07-202030-06-202141DE30445101-02-202030-06-2020ET50598201-01-202031-12-202041DE30635901-01-201931-12-201935YP01635901-01-202030-06-202035YP01635901-01-202030-06-202041DE30&nbsp;I need to calculate a couple of things:&nbsp;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&nbsp;return client&nbsp;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.&nbsp;So the outcome should resemble something like this:&nbsp;ClientIDStartDateEndDateHealthServiceCodeReturnClientContinuedService445101-01-201431-12-20144837100445301-01-201431-12-201435YP0100445301-01-201531-12-201523TP5110223415-06-201931-12-201923TP5000223401-01-202031-12-202023TP5011302101-01-201931-12-201941DE3000302101-07-202030-06-202141DE3010445101-02-202030-06-2020ET5010598201-01-202031-12-202041DE3000635901-01-201931-12-201935YP0100635901-01-202030-06-202035YP0111635901-01-202030-06-202041DE3010&nbsp;&nbsp;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.&nbsp;&nbsp;

imkef · Accepted Answer

Hi Anonymous&nbsp;&nbsp;
please post this code into the advanced editor and follow the steps:
&nbsp;
&nbsp;
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] &gt; 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"
&nbsp;
&nbsp;
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
&nbsp;
.. attaching the file as well
&nbsp;

Forum Discussion

Calculating return clients in M/Power Query

2 Replies

Related content

Power BI Report Builder: Cannot access report parameters inside Power Query (M language)

M Power Query Column Calculation

Summing by Client

COUNTIFS in Power Query (M)

MoM calculation

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