Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculating return clients in M/Power Query

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.

 

ClientIDStartDateEndDateHealthServiceCode
445101-01-201431-12-201448371
445301-01-201431-12-201435YP01
445301-01-201531-12-201523TP51
223415-06-201931-12-201923TP50
223401-01-202031-12-202023TP50
302101-01-201931-12-201941DE30
302101-07-202030-06-202141DE30
445101-02-202030-06-2020ET50
598201-01-202031-12-202041DE30
635901-01-201931-12-201935YP01
635901-01-202030-06-202035YP01
635901-01-202030-06-202041DE30

 

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:

 

ClientIDStartDateEndDateHealthServiceCodeReturnClientContinuedService
445101-01-201431-12-20144837100
445301-01-201431-12-201435YP0100
445301-01-201531-12-201523TP5110
223415-06-201931-12-201923TP5000
223401-01-202031-12-202023TP5011
302101-01-201931-12-201941DE3000
302101-07-202030-06-202141DE3010
445101-02-202030-06-2020ET5010
598201-01-202031-12-202041DE3000
635901-01-201931-12-201935YP0100
635901-01-202030-06-202035YP0111
635901-01-202030-06-202041DE3010

 

 

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.

 

 

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

2 REPLIES 2
ImkeF
Super User
Super User

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

Thanks Imke!

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors