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

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

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