Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Modelling/Transforming rows with overlapping Start Dates and End Dates to determine client status

Hi all,   I have written my problem below. I hope with enough clarity. I would prefer a solution in Power Query/M, but I'll gladly settle for a solution in DAX.   The use case: My organization m...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi Anonymous ,

     

    Please add these custom columns in Power Query Editor.

     

    = List.Dates([Start Date], Duration.Days([End Date]-[Start Date])+1 ,#duration(1, 0, 0, 0))
    = let CID = [Client ID], LDate = [List_Dates],
mylist = Table.SelectRows(#"Added Custom", each [Client ID] = CID and
List.ContainsAny([List_Dates], LDate))[#" Serial#"]
in Text.Combine(List.Transform(mylist, Text.From), ",")

     

    Then calculate the maximum and minimum dates by grouping in [Client ID] and [Custom] columns.

     

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

v-kkf-msft's avatar
v-kkf-msft
Community Support
4 years ago

Hi Anonymous ,

 

Please add these custom columns in Power Query Editor.

 

= List.Dates([Start Date], Duration.Days([End Date]-[Start Date])+1 ,#duration(1, 0, 0, 0))
= let CID = [Client ID], LDate = [List_Dates],
mylist = Table.SelectRows(#"Added Custom", each [Client ID] = CID and
List.ContainsAny([List_Dates], LDate))[#" Serial#"]
in Text.Combine(List.Transform(mylist, Text.From), ",")

 

Then calculate the maximum and minimum dates by grouping in [Client ID] and [Custom] columns.

 

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks v-kkf-msft 

 

It solves the problem, so I accept your solution. I also learned a lot from it, so that's even better. It does wreak havoc on the data load/memory with my dataset, eventually loading 9GB of data on a 4mb source file, with calculation lasting almost a day. 

 

But that's a secondary concern. Primary concern has been solved. Thanks!