Forum Discussion
Table.FirstN function doesn't seem to work properly for my case...
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
DebugJobEndedFirstStepI 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,
13 Replies
HI Anonymous,
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 DebugJobEndedFirstStepRegards,
Xiaoxin Sheng
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,
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.
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 ?
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]}})
Hi Anonymous,
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
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 ?
