Table.FirstN function doesn't seem to work properly for my case...

[jmclej]

Hello,

Here is my code for which I don't understand the output :

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.LastN([Custom], each [Isuserpartofteama] = true))[Creationdate], type datetimezone),
    #"Added JobEnded" = Table.AddColumn(#"Added JobStarted", "JobEnded", each Table.Last(Table.FirstN([Custom], each [Isuserpartofteama] = false))[Creationdate], type datetimezone),
    #"Added TimeTaken" = Table.AddColumn(#"Added JobEnded", "TimeTaken", each [JobEnded] - [JobStarted], type duration),
    DebugJobEndedFirstStep = Table.AddColumn(#"Added TimeTaken", "DebugJobEndedFirstStep", each Table.FirstN([Custom], each [Isuserpartofteama] = false), type datetimezone)
in
    DebugJobEndedFirstStep

I have added the column DebugJobEndedFirstStep which is one step away from what JobEnded is doing, in order to check where the issue lies and there is already something wrong here for me. Indeed, the 2 tables highlighted in yellow shouldn't be empty.

(To be noted that I have a similar issue with the column Jobstarted, but I should be able to fix it when I fix JobEnded because it is based on a similar logic).

Indeed, on the 1st line, there are 2 records in the table Custom that match First N rows where Isuserpartofteama is false :

And on the 4th line, there is 1 record in the table Custom that match First N rows where Isuserpartofteama is false :

How come then "each Table.FirstN([Custom], each [Isuserpartofteama] = false)" returns empty table for these 2 lines ? And if I made a mistake, how can I retrieve what is expected please ?

Thanks for your help,

[v-shex-msft]

Hi @jmclej,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[jmclej]

Hello,

I've received information telling me that FirstN/LastN/Distinct functions (and probably others) can have unexpected results, which is the case in my code with firstN/LastN and that it depends on the "context", the "nested each" and that I could get some information about that in here : Power Query M Primer (Part 21): Identifier Scope & Sections | Ben Gribaudo . I am not saying it is not te right place but to be honest I didn't see how it helps me (I don't understand it enough maybe) and explain the wrong results encountered in my code. Do you have maybe other links/information explaining how we can be sure of a result when we use the mentioned functions ? Have you run my code to see what I am talking about, and do you agree it should be working as it is ?

[jmclej]

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"

[lbendlin]

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]}})

[jmclej]

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

[lbendlin]

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.

[jmclej]

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.

[lbendlin]

I already included the link to the Power Query primer.  Please take the time to go through that - it is time (very) well spent.

[jmclej]

Hi,

Thanks for the workaround, it is indeed working with SelectRows. The logic has to be like this though in order to be like what was expected with FirstN/LastN :

#"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 Table.First(Table.SelectRows([Custom], each [Isuserpartofteama] = false))[Creationdate], type datetimezone),

But it actually doesn't answer why it is not working with FirstN/LastN ? Indeed, why for instance "each Table.FirstN([Custom], each [Isuserpartofteama] = false)" returns empty tables for the 1st and 4th line ? I keep reading the documentation and it should work, what is wrong with it please ?

Regards,

[lbendlin]

Welcome to the wonderful world of contexts.  Yes, they exist in Power Query too.  You are nesting one "each"  inside another "each"  and Power Query gets confused as to which context to use.  

Mitigate by using inline functions or variables ("let ... in ..." ) much like you would do in DAX.

[jmclej]

But I have as many "each" involved/nested in my original code mixing Last/FirstN that I have in the Last/SelectRows version, so why is the context "clearer" in the second case ? Do parenthesis would help regarding this context issue ? Is this a known issue that Microsoft is going to address soon ? This seems crazy.. I mean my code is not yet very complicated and I can't already be sure that it is behaving as expected ? How can I check/debug what context is being use ?

[lbendlin]

Break your query into smaller steps, and use inline function to check/debug.  You can also use the Query diagnostics to validate the results of each step.

If you like to learn M I can highly recommend the primer here 

Power Query M Primer (Part 21): Identifier Scope & Sections | Ben Gribaudo

[v-shex-msft]

HI @jmclej,

If you want to filter rows from a table based on some conditions, please use selectrows instead of FirstN/LastN functions. After I replace the function I mentioned above on your sample code, these steps work well.

Full query:

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.First(Table.SelectRows([Custom], each [Isuserpartofteama] = true))[Creationdate], type datetimezone),
    #"Added JobEnded" = Table.AddColumn(#"Added JobStarted", "JobEnded", each Table.Last(Table.SelectRows([Custom], each [Isuserpartofteama] = false))[Creationdate], type datetimezone),
    #"Added TimeTaken" = Table.AddColumn(#"Added JobEnded", "TimeTaken", each [JobEnded] - [JobStarted], type duration),
    DebugJobEndedFirstStep = Table.AddColumn(#"Added TimeTaken", "DebugJobEndedFirstStep", each Table.First(Table.SelectRows([Custom], each [Isuserpartofteama] = false))[Creationdate], type datetimezone)
in
    DebugJobEndedFirstStep

Regards,

Xiaoxin Sheng