Forum Discussion

fiveone's avatar
fiveone
Helper II
9 years ago
Solved

Creating a table from selected rows from another table

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!

Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    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"

7 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    9 years ago

    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's avatar
      fiveone
      Helper II
      9 years ago

      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's avatar
        MarcelBeug
        Community Champion
        9 years ago

        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.