Creating a table from selected rows from another table

[fiveone]

The data am I dealing with is about Staffing.  I need to show the number of open staffing requests over time, week by week.

We get staffing requests (identified by their Request ID) that get issued on Issued Date and closed on Closed Date.

We look at data at the week level. A week starting on Saturday and ending on Friday.

We consider that a staffing request was open on a given week if it was already issued by then (Issued Date <= Week Ending) and not closed yet (Closed Date > Week Ending).

I have a Staffing table that contains Request ID, Issued Date, and Closed Date:

Request ID	Open Date	Closed Date
1	2016-08-01	2016-08-02
2	2016-08-03	2016-08-07
3	2016-08-09	2017-01-02
4	2016-08-10	2017-01-03
5	2016-08-12	2016-08-14
6	2016-08-13	2016-08-28
7	2016-08-14	2016-08-17
8	2016-08-14	2017-01-03

What I need in the end is this:

Week Ending	Request ID
2016-08-05	2
2016-08-12	3
2016-08-12	4
2016-08-12	5
2016-08-19	3
2016-08-19	4
2016-08-19	6
2016-08-19	8

 Where for each week ending I list all Request IDs that were issued during that week but not yet closed.

I am able to get the list of Week Endings for the dataset with the following code (not sure if it is optimal but it works 😉 )

#"Added Week Ending"= Table.AddColumn(source, "Week Ending",
                 each
//If Friday then we are on the week ending day
                if Date.DayOfWeek([Date Issued])= 5
                then Date.ToText([Date Issued],"d")
                else
//If Saturday then next Friday is in 6 days         
                if Date.DayOfWeek([Date Issued])= 6
                then Date.ToText(Date.AddDays([Date Issued],6),"d")
                else
//If Sunday then next Friday is in 5 days         
                if Date.DayOfWeek([Date Issued])= 0
                then Date.ToText(Date.AddDays([Date Issued],5),"d")
                etc…
        ),

What I struggle with is looping through the data to get for each week the rows that meet the condition ([Date Issued] <= [Week Ending]) and ([Date Closed] > [Week Ending]))

I think I need to create a table with the list of week Endings (this I was able to).

Then another table where I add (using Table.FromRows or Table.FromList), week by week the rows from the source data that meet the criteria (using Table.SelectRows) but I don’t understand how to loop and how to reference other tables.

Maybe it’s not even the proper way to do it 😉

Please help!

[MarcelBeug · Accepted solution]

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"

[fiveone]

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:

[MarcelBeug]

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.

[fiveone]

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

[MarcelBeug]

Your Week Endings are text (because this column is left aligned and not in italics, as compared with the other date columns).

Possibly you supplied data type Date with your Table.AddColumn function.

As an example, the following will create a column that looks like dates, but are still text:

= Table.AddColumn(#"Changed Type", "Custom", each Text.From([Week Ending]), type date)

So if you correct the data type for your Week Endings, then it should work fine.

[fiveone]

You are right - good catch and thanks for the italics+right alignment thing - I really thought we could trust the calendar icon.

this is exactly the issue I am having with the following

//Get the list of ending dates
//Working
#"Added Week Ending"= Table.AddColumn(#"All unique SRs", "Week Ending",
each
if Date.DayOfWeek([Date Issued])= 5
then Date.ToText([Date Issued],"d")
else
if Date.DayOfWeek([Date Issued])= 6
then Date.ToText(Date.AddDays([Date Issued],6),"d")
else
if Date.DayOfWeek([Date Issued])= 0
then Date.ToText(Date.AddDays([Date Issued],5),"d")
else
if Date.DayOfWeek([Date Issued])= 1
then Date.ToText(Date.AddDays([Date Issued],4),"d")
else
if Date.DayOfWeek([Date Issued])= 2
then Date.ToText(Date.AddDays([Date Issued],3),"d")
else
if Date.DayOfWeek([Date Issued])= 3
then Date.ToText(Date.AddDays([Date Issued],2),"d")
else
if Date.DayOfWeek([Date Issued])= 4
then Date.ToText(Date.AddDays([Date Issued],1),"d")
else
"", type date
),
#"Changed Type" = Table.TransformColumnTypes(#"Added Week Ending",{{"Week Ending", type date}}),
//should work but does not
/* #"Added Week Ending"= Table.AddColumn(#"All unique SRs", "Week Ending",
each
if Date.DayOfWeek([Date Issued])= 5
then [Date Issued]
else
if Date.DayOfWeek([Date Issued])= 6
then Date.AddDays([Date Issued],6)
else
if Date.DayOfWeek([Date Issued])= 0
then Date.AddDays([Date Issued],5)
else
if Date.DayOfWeek([Date Issued])= 1
then Date.AddDays([Date Issued],4)
else
if Date.DayOfWeek([Date Issued])= 2
then Date.AddDays([Date Issued],3)
else
if Date.DayOfWeek([Date Issued])= 3
then Date.AddDays([Date Issued],2)
else
if Date.DayOfWeek([Date Issued])= 4
then Date.AddDays([Date Issued],1)
else
[Date Issued], type date
),
*/

[MarcelBeug]

You can't even trust the data type you supplied as the 4th argument with Table.AddColumn (type date): the Date.ToText function returns text as you would expect, but you can't turn it into a date with that 4th parameter.

Nevertheless, your data will still display in the Query Editor and the data type of the column suggests it would be dates, but it (probably) won't load to your table as it is really text and not date.

I raised an issue for this phenomenon back in September 2016.

My suggestion would be not to use the data type parameter of Table,AddColumn, but rather add an additional step to your query in which you adjust the data type of your date column (edit: as you already did). Or adjust your code so you get dates, not texts in the first place,