Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
ID | DateTimeZone 1 | DateTimeZone 2 |
1 | 2021-05-28T09:00:56.6630000-05:00 | 2021-06-03T08:52:39.6570000-05:00 |
1 | 2021-05-28T09:00:56.6630000-05:00 | 2021-05-27T08:52:39.6570000-05:00 |
2 | 2021-02-01T15:53:56.0860000-05:00 | 2021-02-02T15:53:56.0860000-05:00 |
2 | 2021-02-01T15:53:56.0860000-05:00 | 2021-02-03T15:53:56.0860000-05:00 |
2 | 2021-02-01T15:53:56.0860000-05:00 | 2021-02-04T15:53:56.0860000-05:00 |
3 | 2021-02-01T15:53:56.0860000-05:00 | 2021-01-29T15:53:56.0860000-05:00 |
4 | 2020-10-15T19:07:57.8600000-05:00 | 2020-10-16T19:07:57.8600000-05:00 |
4 | 2020-10-15T19:07:57.8600000-05:00 | 2020-10-14T19:07:57.8600000-05:00 |
4 | 2020-10-15T19:07:57.8600000-05:00 | 2020-10-17T19:07:57.8600000-05:00 |
So
Set 1 (DateTimeZone 1 < DateTimeZone 2)
ID | DateTimeZone 1 | DateTimeZone 2 |
1 | 2021-05-28T09:00:56.6630000-05:00 | 2021-06-03T08:52:39.6570000-05:00 |
2 | 2021-02-01T15:53:56.0860000-05:00 | 2021-02-02T15:53:56.0860000-05:00 |
2 | 2021-02-01T15:53:56.0860000-05:00 | 2021-02-03T15:53:56.0860000-05:00 |
2 | 2021-02-01T15:53:56.0860000-05:00 | 2021-02-04T15:53:56.0860000-05:00 |
4 | 2020-10-15T19:07:57.8600000-05:00 | 2020-10-16T19:07:57.8600000-05:00 |
4 | 2020-10-15T19:07:57.8600000-05:00 | 2020-10-17T19:07:57.8600000-05:00 |
Set 2 (DateTimeZone 2< DateTimeZone 1 )
ID | DateTimeZone 1 | DateTimeZone 2 |
1 | 2021-05-28T09:00:56.6630000-05:00 | 2021-05-27T08:52:39.6570000-05:00 |
3 | 2021-02-01T15:53:56.0860000-05:00 | 2021-01-29T15:53:56.0860000-05:00 |
4 | 2020-10-15T19:07:57.8600000-05:00 | 2020-10-14T19:07:57.8600000-05:00 |
So what I want to get is
Set 2
ID | DateTimeZone 2< DateTimeZone 1 | DateTimeZone 2 > DateTimeZone 1 |
1 | 1 | 1 |
2 | 0 | 3 |
3 | 1 | 0 |
4 | 1 | 2 |
Thanks,
Any help or suggestion would be really appreciated
Julio
Solved! Go to Solution.
@Anonymous
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"
|
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. |
@Anonymous
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"
|
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. |
Thank you so much!
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.
ID1 | ID2 | DateTimeZone 1 | DateTimeZone 2 |
1 | 1 | 2021-05-28T09:00:56.6630000-05:00 | 2021-06-03T08:52:39.6570000-05:00 |
1 | 1 | 2021-05-28T09:00:56.6630000-05:00 | 2021-05-27T08:52:39.6570000-05:00 |
1 | 2 | 2021-02-01T15:53:56.0860000-05:00 | 2021-02-02T15:53:56.0860000-05:00 |
1 | 2 | 2021-02-01T15:53:56.0860000-05:00 | 2021-02-03T15:53:56.0860000-05:00 |
1 | 2 | 2021-02-01T15:53:56.0860000-05:00 | 2021-02-04T15:53:56.0860000-05:00 |
2 | 3 | 2021-02-01T15:53:56.0860000-05:00 | 2021-01-29T15:53:56.0860000-05:00 |
2 | 4 | 2020-10-15T19:07:57.8600000-05:00 | 2020-10-16T19:07:57.8600000-05:00 |
2 | 4 | 2020-10-15T19:07:57.8600000-05:00 | 2020-10-14T19:07:57.8600000-05:00 |
2 | 4 | 2020-10-15T19:07:57.8600000-05:00 | 2020-10-17T19:07:57.8600000-05:00 |
So what I want to achieve is this
ID1 | ID2 | DateTimeZone 2< DateTimeZone 1 | DateTimeZone 2 > DateTimeZone 1 |
1 | 1 | 1 | 1 |
1 | 2 | 0 | 3 |
2 | 3 | 1 | 0 |
2 | 4 | 1 | 2 |
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
Hi @Anonymous
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
|
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. |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.