cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fiveone Regular Visitor
Regular Visitor

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!

1 ACCEPTED SOLUTION

Accepted Solutions
MarcelBeug Super Contributor
Super Contributor

Re: Creating a table from selected rows from another table

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"
Specializing in Power Query Formula Language (M)

View solution in original post

7 REPLIES 7
MarcelBeug Super Contributor
Super Contributor

Re: Creating a table from selected rows from another table

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"
Specializing in Power Query Formula Language (M)

View solution in original post

fiveone Regular Visitor
Regular Visitor

Re: Creating a table from selected rows from another table

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:

 

2017-01-20 17_11_24-Clipboard.png

 

 

 

 

MarcelBeug Super Contributor
Super Contributor

Re: Creating a table from selected rows from another table

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.

Specializing in Power Query Formula Language (M)
fiveone Regular Visitor
Regular Visitor

Re: Creating a table from selected rows from another table

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 Super Contributor
Super Contributor

Re: Creating a table from selected rows from another table

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,

Specializing in Power Query Formula Language (M)
MarcelBeug Super Contributor
Super Contributor

Re: Creating a table from selected rows from another table

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.

Specializing in Power Query Formula Language (M)
fiveone Regular Visitor
Regular Visitor

Re: Creating a table from selected rows from another table

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 119 members 1,530 guests
Please welcome our newest community members: