Solved: Number client rows in table

Anonymous · ‎03-11-2020

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 · ‎03-11-2020

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

View solution in original post

dax · ‎03-12-2020

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.