Number client rows in table

[Anonymous]

Hello everyone,

I am trying to put an identifier per client. (like 1,2,3,4,5)

Over time a client can have multiple healthcare registrations

I am using the shanckey chart to visulize the patern(i am using the 1,2,3,4,5 from the clientcount column)

Now i am putting the numbers for every client manualy by hand

I want ot automate this process

Below 4 colums that is use.

Clientcount is the column i manually proces by hand in excel.

i am sorting the clientnumber and the begindate and then the proces starts to fill the clientcount column per client.

Is there a way to automatically fill the count number per client?

Clientnumber	Clientcount	Begin date	End date
16205	1	20-08-2018	09-12-2018
16205	2	10-12-2018	07-04-2019
16205	3	08-04-2019	06-10-2019
16205	4	07-10-2019	22-12-2019
16214	1	31-12-2018	29-12-2019
16214	2	03-02-2020	31-01-2021
16218	1	30-04-2018	27-01-2019
16218	2	16-12-2019	13-12-2020

[Mariusz · Accepted solution]

Hi @Anonymous 

Try this script or see the attached.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY7BDcMwDAN38TsCSDm141kC779GqTpVU7TPI46UzrOwOR5lKw7DYQ4eAgyjL5jbxyEyltMNe8D4cjTyjgXN1Pl1esY67NdoOtwVV95u+fjroBoidqwCGMB0olpx/fPa6cu57UTMlvOCukCjcz4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Clientnumber = _t, #"Begin date" = _t, #"End date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Clientnumber", Int64.Type}, {"Begin date", type date}, {"End date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Clientnumber"}, {{"tbl", each  
        let 
            tbl = _,
            #"Sorted Rows" = Table.Sort( tbl, {{ "End date", Order.Ascending } } ),
            #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Clientcount", 1, 1)
        in 
            #"Added Index", type table }}),
    #"Expanded tbl" = Table.ExpandTableColumn(#"Grouped Rows", "tbl", {"Begin date", "End date", "Clientcount"}, {"Begin date", "End date", "Clientcount"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded tbl",{{"Begin date", type date}, {"End date", type date}, {"Clientcount", Int64.Type}})
in
    #"Changed Type1"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[dax · Accepted solution]

Hi @Anonymous ， 

You could refer to @Mariusz 's suggestions, and you also could refer to my sample for details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY7BDcMwDAN38TsCSDm141kC779GqTpVU7TPI46UzrOwOR5lKw7DYQ4eAgyjL5jbxyEyltMNe8D4cjTyjgXN1Pl1esY67NdoOtwVV95u+fjroBoidqwCGMB0olpx/fPa6cu57UTMlvOCukCjcz4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Clientnumber = _t, #"Begin date" = _t, #"End date" = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"-",".",Replacer.ReplaceText,{"Begin date", "End date"}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Replaced Value", {{"Begin date", type date}, {"End date", type date}}, "sq-AL"),
    #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Clientnumber"}, {{"all", each _, type table [Clientnumber=text, Begin date=date, End date=date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "client count", each Table.AddIndexColumn([all], "client count", 1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"all"}),
    #"Expanded client count" = Table.ExpandTableColumn(#"Removed Columns", "client count", {"Begin date", "End date", "client count"}, {"Begin date", "End date", "client count.1"})
in
    #"Expanded client count"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mariusz · Accepted solution]

Hi @Anonymous 

Try this script or see the attached.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY7BDcMwDAN38TsCSDm141kC779GqTpVU7TPI46UzrOwOR5lKw7DYQ4eAgyjL5jbxyEyltMNe8D4cjTyjgXN1Pl1esY67NdoOtwVV95u+fjroBoidqwCGMB0olpx/fPa6cu57UTMlvOCukCjcz4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Clientnumber = _t, #"Begin date" = _t, #"End date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Clientnumber", Int64.Type}, {"Begin date", type date}, {"End date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Clientnumber"}, {{"tbl", each  
        let 
            tbl = _,
            #"Sorted Rows" = Table.Sort( tbl, {{ "End date", Order.Ascending } } ),
            #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Clientcount", 1, 1)
        in 
            #"Added Index", type table }}),
    #"Expanded tbl" = Table.ExpandTableColumn(#"Grouped Rows", "tbl", {"Begin date", "End date", "Clientcount"}, {"Begin date", "End date", "Clientcount"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded tbl",{{"Begin date", type date}, {"End date", type date}, {"Clientcount", Int64.Type}})
in
    #"Changed Type1"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn