Forum Discussion
Table.FirstN function doesn't seem to work properly for my case...
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.
Again, really ?? But that's crazy, after FirstN/LastN that give wrong results, now Distinct is not safe to use... Do you know where to find a list of those functions that produce "non-deterministic results" please ?
But for my need, it can take actually any row having the given id, because for all those lines, what I will need is in the table in the row called "Custom", and they have all the same content for each given Id.
I already included the link to the Power Query primer. Please take the time to go through that - it is time (very) well spent.