cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
julioagh
Frequent Visitor

Comparing datetimezones to group table by condition

Hi I´m trying to compare two datetimezones from different columns on same table in order to determine how many row does that set have per ID. I have this data

 

IDDateTimeZone 1DateTimeZone 2
12021-05-28T09:00:56.6630000-05:002021-06-03T08:52:39.6570000-05:00
12021-05-28T09:00:56.6630000-05:002021-05-27T08:52:39.6570000-05:00
22021-02-01T15:53:56.0860000-05:002021-02-02T15:53:56.0860000-05:00
22021-02-01T15:53:56.0860000-05:002021-02-03T15:53:56.0860000-05:00
22021-02-01T15:53:56.0860000-05:002021-02-04T15:53:56.0860000-05:00
32021-02-01T15:53:56.0860000-05:002021-01-29T15:53:56.0860000-05:00
42020-10-15T19:07:57.8600000-05:002020-10-16T19:07:57.8600000-05:00
42020-10-15T19:07:57.8600000-05:002020-10-14T19:07:57.8600000-05:00
42020-10-15T19:07:57.8600000-05:002020-10-17T19:07:57.8600000-05:00

 

So 

Set 1 (DateTimeZone 1 < DateTimeZone 2)

IDDateTimeZone 1DateTimeZone 2
12021-05-28T09:00:56.6630000-05:002021-06-03T08:52:39.6570000-05:00
22021-02-01T15:53:56.0860000-05:002021-02-02T15:53:56.0860000-05:00
22021-02-01T15:53:56.0860000-05:002021-02-03T15:53:56.0860000-05:00
22021-02-01T15:53:56.0860000-05:002021-02-04T15:53:56.0860000-05:00
42020-10-15T19:07:57.8600000-05:002020-10-16T19:07:57.8600000-05:00
42020-10-15T19:07:57.8600000-05:002020-10-17T19:07:57.8600000-05:00

 

Set 2 (DateTimeZone 2< DateTimeZone 1 )

IDDateTimeZone 1DateTimeZone 2
12021-05-28T09:00:56.6630000-05:002021-05-27T08:52:39.6570000-05:00
32021-02-01T15:53:56.0860000-05:002021-01-29T15:53:56.0860000-05:00
42020-10-15T19:07:57.8600000-05:002020-10-14T19:07:57.8600000-05:00

 

So what I want to get is

Set 2

IDDateTimeZone 2< DateTimeZone 1DateTimeZone 2 > DateTimeZone 1
111
203
310
412

 

Thanks, 

 

Any help or suggestion would be really appreciated

 

Julio

 

 

1 ACCEPTED SOLUTION
AlB
Super User III
Super User III

@julioagh 

So you want to do it in PQ? Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdFJCsMwDEDRu2QdGQ2WPJzDu5D7XyNKDaGFuqEmRtqIz9t42xZa1tcyMgEqcG5YKmJVC2aC/vzsh6sxQGmYq3KVEkzTW7OvM6I36U7kq2ZAaqRV5RQx2xfRGx4106I8LsZf4qnJXyIBlzsx9hqBfLSR/0yqmkKPP8Te2KiZFuPjYhqK+wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID1 = _t, ID2 = _t, #"DateTimeZone 1" = _t, #"DateTimeZone 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID1", Int64.Type}, {"ID2", Int64.Type}, {"DateTimeZone 1", type datetimezone}, {"DateTimeZone 2", type datetimezone}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID1", "ID2"}, {{"DateTimeZone 2< DateTimeZone 1", each Table.RowCount(Table.SelectRows(_, each [DateTimeZone 2]<[DateTimeZone 1]))}, {"DateTimeZone 1< DateTimeZone 2", each Table.RowCount(Table.SelectRows(_, each [DateTimeZone 1]<[DateTimeZone 2]))}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"DateTimeZone 2< DateTimeZone 1", Int64.Type}, {"DateTimeZone 1< DateTimeZone 2", Int64.Type}})
in
    #"Changed Type1"

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.

 

View solution in original post

4 REPLIES 4
AlB
Super User III
Super User III

@julioagh 

So you want to do it in PQ? Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdFJCsMwDEDRu2QdGQ2WPJzDu5D7XyNKDaGFuqEmRtqIz9t42xZa1tcyMgEqcG5YKmJVC2aC/vzsh6sxQGmYq3KVEkzTW7OvM6I36U7kq2ZAaqRV5RQx2xfRGx4106I8LsZf4qnJXyIBlzsx9hqBfLSR/0yqmkKPP8Te2KiZFuPjYhqK+wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID1 = _t, ID2 = _t, #"DateTimeZone 1" = _t, #"DateTimeZone 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID1", Int64.Type}, {"ID2", Int64.Type}, {"DateTimeZone 1", type datetimezone}, {"DateTimeZone 2", type datetimezone}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID1", "ID2"}, {{"DateTimeZone 2< DateTimeZone 1", each Table.RowCount(Table.SelectRows(_, each [DateTimeZone 2]<[DateTimeZone 1]))}, {"DateTimeZone 1< DateTimeZone 2", each Table.RowCount(Table.SelectRows(_, each [DateTimeZone 1]<[DateTimeZone 2]))}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"DateTimeZone 2< DateTimeZone 1", Int64.Type}, {"DateTimeZone 1< DateTimeZone 2", Int64.Type}})
in
    #"Changed Type1"

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.

 

View solution in original post

julioagh
Frequent Visitor

Thank you so much!

julioagh
Frequent Visitor

Thanks for the response @AlB . And sorry for the misleading first example of data I tried your solution and create a visual table but is not grouping the results. Its making the count but is assigned to every row of the visual table. And I want to the count its made grouping by ID. Perhaps another option could be making it on code with the Table. Group command but I don´t know how to do it exactly. 

 

I get to something like this.

 

ID1ID2DateTimeZone 1DateTimeZone 2
112021-05-28T09:00:56.6630000-05:002021-06-03T08:52:39.6570000-05:00
112021-05-28T09:00:56.6630000-05:002021-05-27T08:52:39.6570000-05:00
122021-02-01T15:53:56.0860000-05:002021-02-02T15:53:56.0860000-05:00
122021-02-01T15:53:56.0860000-05:002021-02-03T15:53:56.0860000-05:00
122021-02-01T15:53:56.0860000-05:002021-02-04T15:53:56.0860000-05:00
232021-02-01T15:53:56.0860000-05:002021-01-29T15:53:56.0860000-05:00
242020-10-15T19:07:57.8600000-05:002020-10-16T19:07:57.8600000-05:00
242020-10-15T19:07:57.8600000-05:002020-10-14T19:07:57.8600000-05:00
242020-10-15T19:07:57.8600000-05:002020-10-17T19:07:57.8600000-05:00

 

So what I want to achieve is this

 

ID1ID2DateTimeZone 2< DateTimeZone 1DateTimeZone 2 > DateTimeZone 1
1111
1203
2310
2412

 

 

But I dont know hot make the grouping by two columns and accesing correctly to datimezone data

 

 

 

Table.Group(Table1, {"ID1","ID2"}, {"DT2>DT1", each Table.RowCount(Table.SelectRows(Table1,[DateTimeZone2]>[DateTimeZone1]))})

 

 

 

 

Thanks for your help. Maybe I need another approach

Julio

AlB
Super User III
Super User III

Hi @julioagh 

1. Place Table1[Id] in a table visual. Make sure it is set to "Don't summarize"

2. Add these measures to the visual

DTZ1<DTZ2 = 
COUNTROWS(FILTER(Table1, Table1[DateTimeZone 2] < Table1[DateTimeZone 1])) + 0
DTZ2<DTZ1 = 
COUNTROWS ( FILTER ( Table1, Table1[DateTimeZone 1] < Table1[DateTimeZone 2] ) ) + 0

 

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.

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors