Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
matthewkaess
Helper I
Helper I

Grouping consecutive dates into a single row

I’m working on an object hire report that needs to group objects with consecutive borrowed on dates into a single row in a table visual. The available columns are;

  • Object No
    Borrowed Date
    Returned Date

Using SWITCH to calculate the Start Date and End Date dynamically based on a date slicer. What I need to do now is combine those two into a single line when the borrowed date(s) are consecutive days.

In this screen shot, the last two lines need to be combined into a single row with the start date being 18/01/2021 and the end date being 19/01/2021. The dates in the slicer are 01/01/2021 to 30/01/2021 which is why the first lines start and end dates are blank.

 

The table returned looks like this

 

Object No      Borrowed Date Returned Date      Start Date         End Date

2011-Z26718/12/202018/12/202018/12/202018/12/2020
2011-Z2678/01/202116/01/20218/01/202116/01/2021
2011-Z26718/01/202118/01/202118/01/202118/01/2021
2011-Z26719/01/202119/01/202119/01/202119/01/2021

 
The result I am looking for is this where the last two rows above are combined into a single row as show below. They only need to be combined if the Borrowed Date(s) are consecutive days. If not they remain in a single row for each entry.

Object No     Borrowed Date  Returned Date      Start Date        End Date

2011-Z26718/12/202018/12/202018/12/202018/12/2020
2011-Z2678/01/202116/01/20218/01/202116/01/2021
2011-Z267  18/01/202119/01/2021


I will upload a redacted version of the model. I've tried various techniques to get this working correctly but so far have not been successful. 

1 ACCEPTED SOLUTION
AlB
Super User
Super User

@matthewkaess 

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

 

SU18_powerbi_badge

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

 

View solution in original post

12 REPLIES 12
AlB
Super User
Super User

@matthewkaess 

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

 

SU18_powerbi_badge

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

 

It may have been something I did when redacting the data into an excel table. The actual datasource is PostgreSQL table(s).

 

As you can see in the screen shot from my live copy with your changes, it looks correct. Very much appreciated! Now onto the DAX problem with those dates and a date slicer. 😮 I may need to hit you up for some help with that too. 

Cheers!

 

matthewkaess_0-1611872731791.png

 

AlB
Super User
Super User

@matthewkaess 

It's working on the data I posted earlier. Share the data you are running it on on an excel file or pbix

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 

 

SU18_powerbi_badge

PBIX and Excel Data Files 

Try this link and let me know how you go. Thanks for all your help! Much appreciated.

@matthewkaess 

Where is the query? Where is the error?

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 

 

SU18_powerbi_badge

That's odd - the error is gone now but the results are incomplete. May be an issue in the actual report not the redacted version.

 

The query is 'Object Billing Export'

matthewkaess_0-1611795515974.png

 

This is all that is being returned now. 

matthewkaess_1-1611795638993.png

 

 

AlB
Super User
Super User

@matthewkaess 

Try 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 

 

SU18_powerbi_badge

Error - Unable to convert type null to type logical 😞

AlB
Super User
Super User

@matthewkaess 

Ok, we'll have to tweak it a bit, yes.

There's something i don't understand, though. Why are you not merging the two last rows in the expected result you show above? the last two rows should be one rowwith dates:

2021-01-16      2021-01-19

shouldn't it? Otherwise it's inconsistent with what you are doing with the other 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 

 

SU18_powerbi_badge

   

That is exactly the result I'm looking for but something in the M code logic isn't quite right. The logic section in the query below. I'm just not sure how to change it to cover this scenario. 

 

#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let inputT_ = [Grouped],
colStart_ = inputT_[Borrowed Date],
colEnd_ = inputT_[Returned Date],
S1_ = Table.AddIndexColumn(inputT_,"Index",0),
S2_ = Table.AddColumn(S1_, "Date1", each if try colEnd_{[Index]-1}=Date.AddDays([Borrowed Date],-1) otherwise false then null else [Borrowed Date], type date),
S3_ = Table.AddColumn(S2_, "Date2", each if try colStart_{[Index]+1}=Date.AddDays([Returned Date], 1) otherwise false then null else [Returned Date], type date),
#"Filled Up" = Table.FillUp(S3_,{"Date2"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Up", each [Date1] <> null)

AlB
Super User
Super User

Hi @matthewkaess 

Something very similar was soved on this thread. Check it out:

https://community.powerbi.com/t5/Power-Query/How-to-merge-two-rows-when-date-range-is-in-continuatio...

 

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 

SU18_powerbi_badge

That link is very close to what I am trying to achieve. If the data wasn't inconsistent, this would work. Problem is when the borrowed date and returned date overlap but are still 'continuous'. The first image is the raw data. The second is the result of the query. Notice rows 5-7 below. The problem is row 6 where. Just not sure yet how to change the query to fix this.Screenshot 2021-01-27 133442.jpgScreenshot 2021-01-27 133419.jpg

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors