Dreadful performance

[trevb]

I'm having dreadful problems with Performance on my Surface Pro 3 i7.  Queries were slow so I offloaded a lot of work into DAX but that now means everybodies experience is slow as I'm not doing so much in the back end.

To give some idea I have one table that I download and trim out as many columns as possible as early as possible.  Against that data I run the query below to create a Pivot

//Query pivots the data so that we can spread costs across financial years it then sets nulls to zero and renames the column for humans to understand more easily
let
Source = Projects,
JustProjectCode = Table.SelectColumns(Source,{"ProjectCode","ProjectStartDate","ProjectFinishDate"}),
AddStartFY =
Table.ExpandListColumn(
Table.AddColumn(JustProjectCode ,
"SFY",
each List.Generate(
()=>GetEarliestFY(Projects[ProjectStartDate]),
each _<= GetLatestFY(Projects[ProjectFinishDate]),
each Date.AddYears(_,1)
)

),
"SFY"
),
AddEndFY = Table.AddColumn(AddStartFY, "EFY", each Date.AddDays(Date.AddYears([SFY],1),-1)),
AddFYTitle = Table.AddColumn(AddEndFY , "Financial Year", each "Work " & Number.ToText(Date.Year([SFY])) & "/" & Number.ToText(Number.Mod(Date.Year([EFY]),1000))),
AddCoreStart = Table.AddColumn(AddFYTitle, "Start", each List.Max({[SFY],[ProjectStartDate]})),
AddCoreEnd = Table.AddColumn(AddCoreStart, "End", each List.Min({[EFY],[ProjectFinishDate]})),
AddDaysEffort = Table.AddColumn(AddCoreEnd, "DaysEffort", each if [Start] < [End] then Duration.Days([End]-[Start])+1 else 0, type number),
#"Removed Columns" = Table.RemoveColumns(AddDaysEffort,{"ProjectStartDate", "ProjectFinishDate", "SFY", "EFY", "Start", "End"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Financial Year"]), "Financial Year", "DaysEffort", List.Sum)
in
#"Pivoted Column"

I set this off at 11:40 this morning, got bored by 12:00, had lunch and went for a walk and returned 13:00 to find the query still running!  This can't be right but I cannot work out if this is the machine or the query.  Does anyone have any ideas?

[trevb · Accepted solution]

And now I answer my own question.

Nestled in the below is this "GetEarliestFY(Projects[ProjectStartDate])".  This is calling a powerbi function that works out the earliest financial year from the provided column.  Apart from the fact that this being an function potentially has an overhead I think it is being called for every itteration of the loop along with it's compatriot.

by changing this to #dates on both of those the query finished in a little over 15 seconds.  🙂  Some work to be done on this I think.