Forum Discussion
Linking TimeStamp to Value between Dates in Seperate Table
this would work in M
let Source = MASTER3_TIMETRACKING, #"Merged Queries" = Table.NestedJoin(Source, {"mrTIMEUSER"}, tblEmployeeHistory, {"EmployeeID"}, "Custom", JoinKind.LeftOuter), #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Custom", {"Role", "Start Date", "End Date"}, {"Role", "Start Date", "End Date"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Changed Type", each [Start Date] <= [mrTIMEDATE] and [End Date] >= [mrTIMEDATE]), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Start Date", "End Date"}) in #"Removed Columns"idea is following:
1) you merge the queries based on EmployeeID (only)
2) you expand the [Role], [Start Date] and [End Date] (at this point you will have more rows than in the beginning)3) you filter the dates based on the [mrTIMEDATE] (you should have the same number of rows as in 1) now)
4) you remove unnecessary columns
Hey Stachu ,
Apologies for my naivete and inexperience, with regard to Excel Power Query and DAX programming, but I have a quick question regarding the code you shared.
Any time I try and generate a custom column which includes a variable declaration, I get the following error...
Token Eof expected.
It isn't the first solution I've ran into this with, but I haven't been able to figure out how it should function. Any guidance in this regard would be most helpful!!
For reference:
- I go into Power Query table editor
- Go to Add Column Tab
- Click Custom Column option
- Name the column
- Paste the following code in
VAR __MrTIMEUSER = 'MASTER3_TIMETRACKING'[mrTIMEUSER] VAR __mrTIMEDATE = 'MASTER3_TIMETRACKING'[mrTIMEDATE] VAR __EmployeeHistory = FILTER ( 'tblEmployeeHistory', 'tblEmployeeHistory'[EmployeeID] = __MrTIMEUSER && 'tblEmployeeHistory'[Start Date] <= __mrTIMEDATE && 'tblEmployeeHistory'[End Date] >= __mrTIMEDATE ) RETURN CALCULATE ( FIRSTNONBLANK ( 'tblEmployeeHistory'[Role], TRUE () ), __EmployeeHistory )Receive Eof Error
Sincerely,
Kristopher
Community ChampionI think there is a bit of confusion here
in Power Query you use M, DAX is used in the model itself (in Excel it was called Power Pivot to differentiate)
you asked for DAX solution in your post, the one I provided will work in the model
for M based solution it's a bit different approach, I can followup later this week
Thank you for the great information, Stachu! Apologies for my inexperience and lack of knowledge. Yes, I was confusing the portion relating to PowerQuery with that of Power Pivot, and trying to write DAX in the custom column for M-Code.
I suppose that means though that the solution would work if I apply it to the data model in Power Pivot. I'll have to give that a shot later to find out.
Any additional info you would like to provide would be most appreciated, as I am still learning all about Power BI, and need to identify best practices to follow.
- Stachu
this would work in M
let Source = MASTER3_TIMETRACKING, #"Merged Queries" = Table.NestedJoin(Source, {"mrTIMEUSER"}, tblEmployeeHistory, {"EmployeeID"}, "Custom", JoinKind.LeftOuter), #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Custom", {"Role", "Start Date", "End Date"}, {"Role", "Start Date", "End Date"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Changed Type", each [Start Date] <= [mrTIMEDATE] and [End Date] >= [mrTIMEDATE]), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Start Date", "End Date"}) in #"Removed Columns"idea is following:
1) you merge the queries based on EmployeeID (only)
2) you expand the [Role], [Start Date] and [End Date] (at this point you will have more rows than in the beginning)3) you filter the dates based on the [mrTIMEDATE] (you should have the same number of rows as in 1) now)
4) you remove unnecessary columns - Stachu
Anonymous
If the problem is solved, can you mark the relevant post as a solution? That way other users can find answers more easily