cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
frano72
Helper III
Helper III

looking up a start and end date between another tables start and end date

Hi, i can do the following in DAX but i think it might be better to do it in PQ.

 

I have one fact table that is storing the "state" of a piece of equipment (dozer).  Is it "pushing", is it "idling" etc.  There is a start time, an end time, and the state name for this table.

 

Another fact table stores cycle data.  Similar format, a start time an end time and some other metrics.

 

So typically there are many cycles for each state.

 

So in the cycle fact table i want to add a column that brings over from the states table what state was it in for that cycle.

 

So a really simple example could be, you might have two records in state table - 12 am to 1 pm "pushing", 1 pm to midnight" "idling".

startfinishstate
12:00 am1 pmpushing
1 pmmidnightidling

 

Then in cycles table, there can be 100's of rows...so it needs to lookup the start and end time over in the states table to return which state it is in.

 

start timeend timemetres NEW COLUMN (STATE)
12:0012:0350function returns "Pushing"
12:0312:0755function returns "Pushing"

 

hopefully this makes sense.  ignoring the business meaning of the data, its a case of finding where a time interval is between another (larger) time interval.  one can assume that you never get a time interval crossing boundaries.

 

 

 

 

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User II
Super User II

@frano72 

wdx223_Daniel_0-1604295419366.png

let
    StateTable = Excel.CurrentWorkbook(){[Name="StateTable"]}[Content],
    StatesTable = Excel.CurrentWorkbook(){[Name="StatesTable"]}[Content],
    Custom1 = Table.AddColumn(StatesTable,"State",each Table.SelectRows(StateTable,(x)=>x[start]<=[start time] and x[finish]>=[end time]){0}?[state]?)
in
    Custom1

View solution in original post

5 REPLIES 5
wdx223_Daniel
Super User II
Super User II

@frano72 

wdx223_Daniel_0-1604295419366.png

let
    StateTable = Excel.CurrentWorkbook(){[Name="StateTable"]}[Content],
    StatesTable = Excel.CurrentWorkbook(){[Name="StatesTable"]}[Content],
    Custom1 = Table.AddColumn(StatesTable,"State",each Table.SelectRows(StateTable,(x)=>x[start]<=[start time] and x[finish]>=[end time]){0}?[state]?)
in
    Custom1

View solution in original post

Hi @wdx223_Daniel , that works a treat.  Now for scope creep 😉  I left out another part of the issue to simplify the problem.  within both fact tables there is a column that is the uniqueID for the Dozer.  So if you can imagine in your two tables a column added to each with a unique dozer name.  

 

So somewhere in this statement need to also ensure statestable.machineid = statetable.machineid

SelectRows(StateTable,(x)=>x[start]<=[start time] and x[finish]>=[end time]

 

make sense ?

 

thanks !

 

actually...i think i got it to work.  just added another and condition following the same format and it looks to be working.

 

Thanks @wdx223_Daniel 

CNENFRNL
Super User III
Super User III

Hi, @frano72 , to my understanding, what's needed to do is to compare the end time with the division point, i.e. 1pm in the mockup.

#"New Column" = Table.AddColumn(#"cycles table", "Status", each if [end time]>#time(13,0,0) then "pushing" else "idling")

 

Hi @CNENFRNL  - that certainly would work for the specific case listed, but those fact tables are continually updated with new data, so need a way for it to be generalised so that can look up what the state is rather than hard coding it in the if statement.

 

I thought there would be a way of parameterising the problem with a custom power query function that does the date check

 

Does that make sense ?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors