Forum Discussion
Inefficient function?
Hi Aaron,
if that's lightning-fast, let's make match just one row. Therefore you have to create "TeamLookup"-Table like this:
let fnFillDateIntervalls = (Table as table, DateColumn as text, FillUpUntil as date)=> let //DebugParameters: //Table = #"Staff Movements", //DateColumn = "Joined", Source = Table, #"Sorted Rows" = Table.Sort(Source,{{DateColumn, Order.Descending}}), Index = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1), Change = Table.TransformColumnTypes(Index,{{DateColumn, Int64.Type}}), #"Added Custom" = Table.AddColumn(Change, "Custom", each [Index]+1), #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Index"},#"Added Custom",{"Custom"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {DateColumn}, {"NewColumn.Date"}), #"Added Custom1" = Table.AddColumn(#"Expanded NewColumn", "Custom.1", each try {Record.Field(_,DateColumn)..[NewColumn.Date]} otherwise {Record.Field(_,DateColumn)..Number.From(FillUpUntil)}), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{DateColumn, "Index", "Custom", "NewColumn.Date"}), #"Expanded Custom.1" = Table.ExpandListColumn(#"Removed Columns", "Custom.1"), #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom.1",{{"Custom.1", DateColumn}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{DateColumn, type date}}) in #"Changed Type", Source = #"Staff Movements", #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"All", each fnFillDateIntervalls(_, "Joined", Date.From(DateTime.LocalNow())), type table}}), #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Joined", "Team"}, {"Joined", "Team"}) in #"Expanded All"This query transforms your "Staff Movements"-table into a table with one row for each day. You should disable load to the datamodel. So although this lengthens your table considerably, the result is still very fast, as it used the simple query you mentioned in your last post.
Edit: Don't use Internet-Explorer or Edge to copy this code but preferrably Firefox. Otherwise the code will break (this time will greet you with a request for a comma where there is already one) !!!
I wonder if I'm on the right track here: I've attempted a nested join, which seems to be lightning-fast but leaves me stumped on how to filter the result by date.
let
Source = Timesheets,
TeamLookup = #"Staff Movements",
AssignTeam = Table.NestedJoin(Source, {"Name"}, TeamLookup, {"Name"},"Team",JoinKind.LeftOuter)
in
AssignTeam
Reminder of the tables I have
Timesheets
Name Project Date
Joe Bloggs Project X 01/01/2014
Joe Bloggs Project Y 10/04/2015
Fred Jones Project A 01/02/2016
Staff Movements
Name Team Date Joined
Joe Bloggs Project Office 01/01/2014
Joe Bloggs HR 10/04/2015
Fred Jones Finance 30/12/2015
The code above works beautifully if a staff member has only one entry in the Staff Movements table, ie. if I expand the new "Team" column, I'd get a single row for Timesheet entries for Fred Jones as he's only one team. However, for people like Joe Bloggs, he nested join understandably returns two rows.
What I'm struggling with here is how to introduce a filter to the new "Team" column to reduce the number of rows it returns to be only one, ie. where the Timesheets.Date value >= Staff Movements.Date Joined.
Any ideas?
Thanks!
Aaron
Hi Aaron,
if that's lightning-fast, let's make match just one row. Therefore you have to create "TeamLookup"-Table like this:
let
fnFillDateIntervalls = (Table as table, DateColumn as text, FillUpUntil as date)=>
let
//DebugParameters:
//Table = #"Staff Movements",
//DateColumn = "Joined",
Source = Table,
#"Sorted Rows" = Table.Sort(Source,{{DateColumn, Order.Descending}}),
Index = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
Change = Table.TransformColumnTypes(Index,{{DateColumn, Int64.Type}}),
#"Added Custom" = Table.AddColumn(Change, "Custom", each [Index]+1),
#"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Index"},#"Added Custom",{"Custom"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {DateColumn}, {"NewColumn.Date"}),
#"Added Custom1" = Table.AddColumn(#"Expanded NewColumn", "Custom.1", each try {Record.Field(_,DateColumn)..[NewColumn.Date]} otherwise {Record.Field(_,DateColumn)..Number.From(FillUpUntil)}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{DateColumn, "Index", "Custom", "NewColumn.Date"}),
#"Expanded Custom.1" = Table.ExpandListColumn(#"Removed Columns", "Custom.1"),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Custom.1",{{"Custom.1", DateColumn}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{DateColumn, type date}})
in
#"Changed Type",
Source = #"Staff Movements",
#"Grouped Rows" = Table.Group(Source, {"Name"}, {{"All", each fnFillDateIntervalls(_, "Joined", Date.From(DateTime.LocalNow())), type table}}),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Joined", "Team"}, {"Joined", "Team"})
in
#"Expanded All"
This query transforms your "Staff Movements"-table into a table with one row for each day. You should disable load to the datamodel. So although this lengthens your table considerably, the result is still very fast, as it used the simple query you mentioned in your last post.
Edit: Don't use Internet-Explorer or Edge to copy this code but preferrably Firefox. Otherwise the code will break (this time will greet you with a request for a comma where there is already one) !!!
That's perfect, ImkeF!
Thank you very much for your help.