Forum Discussion

fiveone's avatar
fiveone
Icon for Helper II rankHelper 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"
fiveone's avatar
fiveone
Icon for Helper II rankHelper 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
Icon for Community Champion rankCommunity 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.

  • fiveone's avatar
    fiveone
    Icon for Helper II rankHelper II
    9 years ago

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

      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,