Forum Discussion

SamB1's avatar
SamB1
Frequent Visitor
3 years ago

List query showing a false 'TRUE' output

I have a table of datetimes I need to check if all datetime values for each ID are equal to the average of the datetimes.   I transformed the values from datetime to number and then text so I could...
Need Help
ppm1's avatar
ppm1
Icon for Solution Sage rankSolution Sage
3 years ago

Is this what you mean?

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsrQwV9JRMjbQNzDWNzIwMlIwNLIyMAAipVgdbLImRMsamqBKGhkPpGQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Start = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Start", type datetime}}, "en-GB"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type with Locale", "Start", "StartAsDecimal"),
    #"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column",{{"StartAsDecimal", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"AvgTime", each List.Average([StartAsDecimal]), type nullable number}, {"AllRows", each _, type table [ID=nullable text, Start=nullable datetime, StartAsDecimal=nullable number]}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Start", "StartAsDecimal"}, {"Start", "StartAsDecimal"}),
    #"Added Custom" = Table.AddColumn(#"Expanded AllRows", "IsAvg", each [AvgTime] = [StartAsDecimal]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"ID", "Start", "IsAvg"})
in
    #"Removed Other Columns"

 

Pat

SamB1's avatar
SamB1
Frequent Visitor
3 years ago

Hi Pat,

Thank you for having a look at this!

 

I did look at this option previously but that would only be comparing each value to the Average Time whereas I need to compare all values for each ID to the average to determine if all values for that ID are equal to the average.

 

Here's another example which would show TRUE for only one value using the query you posted but should be FALSE overall:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjNW0lEyNtA3MNY3MjAyUjA0sjIwACKlWB0sksb4JE1gkrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Start = _t]),
    #"Change to datetimes" = Table.TransformColumnTypes(Source,{{"Start", type datetime}}),
    #"Change to numbers" = Table.TransformColumnTypes(#"Change to datetimes",{{"Start", type number}}),
    #"Duplicated Start" = Table.DuplicateColumn(#"Change to numbers", "Start", "Start - Copy"),
    #"Change to text for List" = Table.TransformColumnTypes(#"Duplicated Start",{{"Start - Copy", type text}}),
    #"Grouped for AvStart" = Table.Group(#"Change to text for List", {"ID"}, {{"AvStart", each List.Average([Start]), type nullable number}, {"IDCount", each Table.RowCount(_), Int64.Type}, {"AllStart", each [#"Start - Copy"], type list}}),
    #"Change AvStart to text for List compare" = Table.TransformColumnTypes(#"Grouped for AvStart",{{"AvStart", type text}}),
    
    #"Add AllTrue Check" = Table.AddColumn(#"Change AvStart to text for List compare", "AllTrue", each List.AllTrue(
List.Transform([AllStart], (substring) => Text.Contains([AvStart], (substring))))),

    #"Expanded AllStart" = Table.ExpandListColumn(#"Add AllTrue Check", "AllStart"),
    #"Back to Number" = Table.TransformColumnTypes(#"Expanded AllStart",{{"AllStart", type number}, {"AvStart", type number}}),
    #"Back to Datetime" = Table.TransformColumnTypes(#"Back to Number",{{"AllStart", type datetime}, {"AvStart", type datetime}})
in
    #"Back to Datetime"

 

However that one annoying value of 44650.555555555555 and 44650.5 means I don't have confidence in this method getting it right everytime unless I can figure out why there's a 'false' TRUE.

 

Hope that makes sense!