The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.
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 |
Solved! Go to 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"
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.
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.
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"