List query showing a false 'TRUE' output

[SamB1]

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 use List.Transform and List.AllTrue. However, for some reason when doing this, Power Query seems to think 44650.555555555555 is equal to 44650.5 when both numbers are text. If it is 44650.50 it recognises it as not equal but not 44650.5.

I've copied my example query below and appreciate I may have miswritten something or there may be an easier way so any feedback would be very much appreciated because this is causing much frustration and was supposed to be a simple workaround to an otherwise quite complex data issue!

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]),
    #"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))))),

    // I added in this step to double check #"Add AllTrue Check" output was correct. The overall output is correct but this is where I see the error for ID 987 as all values should be FALSE but the first value gives a TRUE
    #"Add Match Check Col" = Table.AddColumn(#"Add AllTrue Check", "StartMatchList", each List.Transform([AllStart], (substring) => Text.Contains([AvStart], (substring)))),

    #"Expanded AllStart" = Table.ExpandListColumn(#"Add Match Check Col", "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"

[ppm1]

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]

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!