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

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
Helper 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
    Community 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,