Forum Discussion
Creating a table from selected rows from another table
This wil create the result you are looking for.
let Source = List.Dates(#date(2016,8,5),3,#duration(7,0,0,0)), Tabled = Table.FromList(Source, each {_}, {"Week Ending"}), #"Added Custom" = Table.AddColumn(Tabled, "Custom", (x) => Table.SelectRows(StaffingTable, each [#"Open Date"] <= x[#"Week Ending"] and ([#"Closed Date"] = null or [#"Closed Date"] > x[#"Week Ending"]))), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Request ID"}, {"Request ID"}) in #"Expanded Custom"
Thanks Marcel for the reply.
That is indeed the kind of solution I ended up with (kinda... I was trying to creatd a new table using Table.Group instead of using adding a column to the Week Ending table).
#"Requests by week" = Table.Group(#"Added Week Ending", {"Week Ending"}, {"Requests", each Table.SelectRows(#"Added Week Ending",each [Date Issued] <= [Week Ending] and [Date Closed] > [Week Ending]), type table}),
#"Result" = Table.ExpandTableColumn(#"Requests by week", "Requests", {"Request ID"})
I'm not sure in understand the meaning/use of the 2 "x" in your code but I left them
#"Requests by week" = Table.AddColumn(#"Week Endings only", "Requests", (x) => Table.SelectRows(#"All unique SRs", each [#"Date Issued"] <= x[#"Week Ending"] and ([#"Date Closed"] = null or [#"Date Closed"] > x[#"Week Ending"]))),
#"Result" = Table.ExpandTableColumn(#"Requests by week", "Requests", {"Request ID"})
But in both cases the ExpandTableColumn step returns the following error : "We cannot apply operator < to types Text and Date."
which is weird because the columns being compared are definitely dates, as shown by the icon in the table header:
In addition I still owe you an explanation of the x's in the line:
#"Added Custom" = Table.AddColumn(Tabled, "Custom", (x) => Table.SelectRows(StaffingTable, each [#"Open Date"] <= x[#"Week Ending"] and ([#"Closed Date"] = null or [#"Closed Date"] > x[#"Week Ending"]))),
Well, within the function Table.SelectRows you want to refer to columns in StaffingTable (Open and Closed Date), but also to a column in the Tabled table (Week Ending).
If I would refer to the column Week Ending within the Table.SelectRows part, it would refer to such a (nonexisting) column in the StaffingTable.
The (x) => creates a small function with x as parameter (being the current record from the Tabled table) and this can be used to refer to the column "Week Ending" in the Tabled table within the Table.SelectRows for the StaffingTable table.
Wow, that's an awesome trick. I have only been playing with powerquery for a few days but that way of refering to another table looks pretty intense to me.
Anyway it works like a charm thanks for the hint I would never had figured it out by myself