Forum Discussion
Helper IGrouping consecutive dates into a single row
See it all at work in the attached file.
I'm getting a bit lost. The example that you show (2504-Z004) has only one row in the data you've shared, with dates 18/01/2021 - 18/01/2021 , so the result you show is the correct one
Most of the Object No in your data have only one row. I had a look at the one with the larger number or rows (you can see them in the query Object Billing Export (TESTS) that I created to help in the debugging and they seem to work fine.
I uncommented the S2_ and S3_ ( to use the latest versions I'd suggested, you had the previous version active) and included sorting by Returned Date on this step, to get the dates completely sorted out before the crucial step:
Sorted by Object No = Table.Sort(#"Reordered Columns",{{"Object No", Order.Ascending}, {"Borrowed Date", Order.Ascending}, {"Returned Date", Order.Ascending}})plus it looks like the error you got (Error - Unable to convert type null to type logical) was caused by cases like
3433-Z062 that had a null not as the very latest date in Borrowed Date. So I temporarily replaced those nulls with a date far in the future (31/12/9999) so that the row gets placed at the end when sorting ascending (the null woud be placed at the beginning since it is treated as as 0). Then at the end convert that 31/12/9999 back to null
Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Community ChampionTry this variation of the solution posted in the other thread S2_ and S3_ are the only steps that have been updated
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZPPDoMgDIdfxXg2gf5gKs9iPGyJWXbQLTttbz9g1D9BDXBpjX5f2xS7riQoVVblx0YbyB4bpudUvK7fx3QvxmG8DW/3SpCApMalECo89JVX6EhxgLdrvGX8ktOBmVNIFtQZAkvFgmZXMJMQmMnQ/cK2icODvCmkATZJnYf6tK7PCi1zhsdaAVbQX+HWCPhvdhXyZIkakeNsgYwbxlVaC8sKsV2h1ruC4xUae5gNF9AFpfwPkXN/dR3hm9KnczeJpaPO+x8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"customer key" = _t, #"Cust source key" = _t, #"member number" = _t, Status = _t, #"Start Date" = _t, #"End Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"customer key", Int64.Type}, {"Cust source key", type text}, {"member number", Int64.Type}, {"Status", type text}, {"Start Date", type date}, {"End Date", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"customer key", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Cust source key", "member number", "Status"}, {{"Count", each _, type table [customer key=nullable number, Cust source key=nullable text, member number=nullable number, Status=nullable text, Start Date=nullable date, End Date=nullable text]}}),
inputT_ = #"Grouped Rows"{[#"Cust source key"="x123",#"member number"=1111,Status="non paying member"]}[Count],
colStart_ = inputT_[Start Date],
colEnd_ = inputT_[End Date],
S1_ = Table.AddIndexColumn(inputT_,"Index",0),
S2_ = Table.AddColumn(S1_, "Date1", each if try Number.From([Start Date]-Date.From(colEnd_{[Index]-1}))<=1 otherwise false then null else [Start Date], type date),
S3_ = Table.AddColumn(S2_, "Date2", each if try Number.From(Date.From(colStart_{[Index]+1})-[End Date])<=1 otherwise false then null else [End Date], type date),
#"Filled Up" = Table.FillUp(S3_,{"Date2"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Up", each [Date1] <> null)
in
#"Filtered Rows"Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
matthewkaessError - Unable to convert type null to type logical 😞