Forum Discussion
Table.FirstN function doesn't seem to work properly for my case...
I put here the code that I ended with to do what I needed, including the workarounds (firstn/lastn not working properly and numbers interprated as sums or counts in the report so I've used SelectRows and changed all the datatypes to text at the end). Thanks for your help even if there are still some behaviours I don't understand.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndQ9bsMwDAXg3acQvDYWSOrP1lWCDBocJEVbF4085PaVOrSMHaVyJi3+8J4Jiftm32K7a+M5ntPxOp0+0kFA1IHpSAlED8ZrkMoM4gUIIH/9NY/tYXdrL+GdUxDoPAxeG9kr+qXH8HZZ288QFxa9SVZLcmZtm6Qp6znOi2T7o61XyqOWtlf3kpnlybZDJzD9be9pkI7wfrLKOsSwTkaB9Di5bAut/0bN6BOlddZTnBbBQ03pgu0rSjPKS7v/Sxdjq+Zcts9Wrpyzyfoar9uvZdY26zGOy6foKp6iLdgUXbBsXiVcswOY3bwDmN20A5rDNw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Ref = _t, User = _t, Creationdate = _t, Isuserpartofteama = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Ref", type text}, {"User", type text}, {"Creationdate", type datetimezone}, {"Isuserpartofteama", type logical}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (k) => Table.SelectRows(#"Changed Type", each [Id] = k[Id])),
#"Removed Duplicates" = Table.Distinct(#"Added Custom", {"Id"}),
#"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Ref", "User", "Creationdate", "Isuserpartofteama"}),
Custom1 = Table.SelectRows(#"Removed Columns", each Table.Contains([Custom], [Isuserpartofteama = true])),
#"Added JobStarted" = Table.AddColumn(Custom1, "JobStarted", each Table.Last(Table.SelectRows([Custom], each [Isuserpartofteama] = true))[Creationdate], type datetimezone),
#"Added JobEnded" = Table.AddColumn(#"Added JobStarted", "JobEnded", each if Table.First([Custom])[Isuserpartofteama] = true then "not finished yet" else Text.From(Table.First(Table.SelectRows([Custom], each [Isuserpartofteama] = false))[Creationdate]), type text),
#"Added TimeTaken" = Table.AddColumn(#"Added JobEnded", "TimeTakenInHours", each Text.From(Number.Round(if Table.First([Custom])[Isuserpartofteama] = true then Duration.TotalHours(DateTimeZone.LocalNow() - [JobStarted]) else Duration.TotalHours(DateTimeZone.From([JobEnded]) - [JobStarted]), 0)), type text),
#"Added SLA" = Table.AddColumn(#"Added TimeTaken", "SLA", each if Number.From([TimeTakenInHours]) > 24 then "NOT OK" else "OK"),
#"Changed Type1" = Table.TransformColumnTypes(#"Added SLA",{{"JobStarted", type text}, {"Id", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Custom"})
in
#"Removed Columns1"
This part
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (k)=> Table.SelectRows(#"Changed Type",each [Id]=k[Id])), #"Removed Duplicates" = Table.Distinct(#"Added Custom", {"Id"}),is rather questionable and leads to data destruction. I think what you want instead is
= Table.Group(#"Changed Type", {"Id"}, {{"Rows", each _, type table [Id=nullable number, Ref=nullable text, User=nullable text, Creationdate=nullable datetimezone, Isuserpartofteama=nullable logical]}})Hello,
I have run your version with the Grouping and I end up with the same amount of lines/data that with my AddColumn/Distinct/RemoveColumns code. Si I reckon your solution is more concise but I don't understand the data destruction, can you elaborate please ?
Thank you
Running
Table.Distinct(#"Added Custom", {"Id"})will work in a (potentially) unexpected way. Instead of returning the distinct values of "Id" it will grab any one row from the table for each of the IDs. You should not assume that it will always grab the first row. In fact, Power Query has no exposed concept of row numbers or row order. It is your responsibility as a developer to enforce that sort order, and even then Power Query can choose to ignore your directive in downstream transforms.
Long story short - Table.Distinct can produce non-deterministic results.
