Forum Discussion
Number client rows in table
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 |
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.
LinkedInHi 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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
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.
LinkedInHi 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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.