Power Query engine slow

[Anonymous]

Hi,

My power Query engine is very slow when i refresh a calander and i hope somebody can help.

I use a simple calander script.

let
Source = List.Dates(StartDate, Length, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Datum"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Datum", type date}}),
StartDate = #date(2020,1,1),
Today = DateTime.Date(DateTime.LocalNow()),
Length = Duration.Days(Today - StartDate)+1,
Custom1 = #"Changed Type",
#"Inserted Year" = Table.AddColumn(Custom1, "Jaar", each Date.Year([Datum]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Maand naam", each Date.MonthName([Datum]), type text),
#"Inserted Kwartaal Name" = Table.AddColumn(#"Inserted Month Name", "Kwartaal naam", each Date.QuarterOfYear([Datum]), type text),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Kwartaal Name", "Dag naam", each Date.DayOfWeekName([Datum]), type text),
#"Inserted Month" = Table.AddColumn(#"Inserted Day Name", "Maand nummer", each Date.Month([Datum]), Int64.Type),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Month", "Dag vd week", each Date.DayOfWeek([Datum])+1, Int64.Type),
#"Inserted First Characters" = Table.AddColumn(#"Inserted Day of Week", "MMM", each Text.Start([Maand naam], 3), type text),
#"Inserted First Characters1" = Table.AddColumn(#"Inserted First Characters", "DDD", each Text.Start([Dag naam], 3), type text),
#"Reordered Columns" = Table.ReorderColumns(#"Inserted First Characters1",{"Datum", "Jaar", "Maand naam", "MMM", "Maand nummer", "Dag naam", "DDD", "Dag vd week"}),
#"Added Custom1" = Table.AddColumn(#"Reordered Columns", "MaandID", each (Date.Year([Datum]) - Date.Year(StartDate))*12 + Date.Month([Datum])),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"MaandID", Int64.Type}, {"Kwartaal naam", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type2",{{"Datum", Order.Descending}})
in
#"Sorted Rows"

When i start to refresh the script it takes more than 30 minutes.

In generel the power qeury engine is slow at this domain.

i thaugth maybe the model is to complex or the datatransformations.

But this script is just simple and it still takes some time.

I heard from the IT departement that PowerBI is installed on a virtual machine.

so everytime i open BI desktop a script is runned.

For example everytime i open BI, I have to change the language for the interface because it doesnt store the presets for differents sessions.

I dont understand it fully but can this be the cause?

Regard,

Charaf

[Anonymous]

@AnkitKukreja , thank you for your reply.

The script is not in the vm. The power BI application is.

Regards,

charaf

[AnkitKukreja]

Hi @Anonymous 

Yup, I understand that. I was talking about the point you said, whenever you open your Power BI a script is run in a VM. So maybe some issues with that, but this is a very simple m code and I don't think it would be causing any delays in your model.

[AnkitKukreja]

Hi @Anonymous 

I think the issue is with the VM, because I just tried your code and it take a second or even less to refresh and this M code generally gets stored as a cache and shouldn't take much time to refresh because its not connecting to any database or does not have any live connection.

It is stored in the memory of your Power BI file.

I don't understand what's that script is doing in VM but your code is definitely not causing the issue here.

I hope it helps. Have a great weekend.