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 Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Kudoed Authors