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 issu...
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"
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"