Power Query extremely slow / load not working

[Anonymous]

Hi all

We are going to switch from QlikView to Power BI, and I'm working on setting up the data model in Power Query. We will get a lot of data from a database, Excel sheets and an online service. There will also be a lot of joins and transformations.

But to keep it simple in the beginning, I have just written a few queries to extract the six most important tables from the database, and leave everything else out for now. Then I transform the data, and add the joins (four tables should join to one). The preview of the data in Power Query is fine.

But when I click I want to use the query changes, Power BI opens up a dialog box where I can see it is working on three of the queries (the ones with enabled load). But it works and works, and is never completed. First it just says "evaluating" for ten minutes, then it finally starts to import some data. But after about a half million rows, it just stays there, and doesn't do anything more I can see. I have  waited more than an hour, but then gave up. The cancel button didn't respond, and the whole dialog box became black. I had to use task manager to kill Power BI and restart it. Same ting happened next time I tried.

In task manager I have between 60% and 100% processor use, 80% memory use, and 0% disc and network use.

In QlikView it takes about 15 minutes to load and transform a model containing the same data, plus a lot more.

What I am doing wrong?

[Greg_Deckler]

What version of Power BI are you using? Are you using 64-bit?

Can you post the queries that you are using? What are the actual sources?

Have you looked at the new query diagnostic feature released in October?

[Anonymous]

I'm using the 64-bit version from august, so I don't have access to query diagnostics yet. I'll hear if the IT department can upgrade me to the newest version.

These are the queries. The only source I have is a database, connected to with ODBC:

Query VatHeader

let
Kilde = Odbc.Query("*******", "***********************************"),
#"Kolonner med nye navn" = Table.RenameColumns(Kilde,{{"****", "FakturaId.MvaHode"}, {"*****", "MvaHode"}})
in
#"Kolonner med nye navn"

Query Invoice Header

let
Kilde = Odbc.DataSource("****", [HierarchicalNavigation=true]),
Database = Kilde{[Name="*****",Kind="Database"]}[Data],
dbo_Schema = Database{[Name="dbo",Kind="Schema"]}[Data],

FakturaHode0= dbo_Schema{[Name="******",Kind="Table"]}[Data],

FakturaHode1 = Table.TransformColumnTypes(FakturaHode0,{{"****", type text}}),
FakturaHode2 = Table.RenameColumns(FakturaHode1,{{"****, "FakturaId"},{"*****","BeløpHode"}}),
FakturaHode3 = Table.RemoveColumns(FakturaHode2,{"****","*****","*****","*****"}),
FakturaHode5 = Table.Join(FakturaHode3,{"FakturaId"}, VatHeader,{"FakturaId.MvaHode"},JoinKind.LeftOuter),

FakturaHode6 = Table.AddColumn(FakturaHode5, "Fakturadato", each Text.End([*****],2)&"."& Text.Middle( [*****],4,2)&"."& Text.Start([****],4) ),
FakturaHode7 = Table.RemoveColumns(FakturaHode6,{"FakturaId.MvaHode"}),
FakturaHode8= Table.AddColumn(FakturaHode6, "EFaktura", each if **************** then "Ja" else "Nei")

in
FakturaHode8

Query Accounting

let
Kilde = Odbc.DataSource("***********", [HierarchicalNavigation=true]),
Database = Kilde{[Name="*********",Kind="Database"]}[Data],
dbo_Schema_Kontering = Database{[Name="dbo",Kind="Schema"]}[Data],
Kontering1 = dbo_Schema_Kontering{[Name="******",Kind="Table"]}[Data],
Kontering2 = Table.RenameColumns(Kontering1,{{"*****", "Kostnadsstad"}, {"*****", "Prosjekt"}, {"*****", "Id"}}),
Kontering3 = Table.AddColumn(Kontering2, "Kostnadsart", each if Text.StartsWith([******],"2")=false and Text.StartsWith([*****],"00")=true then Text.End([*****],5) else Text.Start([*****],5)),
Kontering_Table= Table.RemoveColumns(Kontering3,{"****", "****", "****", "****", "****", "****","****","****","****"})
in
Kontering_Table

Query Invoice Accounting

let
Kilde = Odbc.DataSource("*****", [HierarchicalNavigation=true]),
Database = Kilde{[Name="******",Kind="Database"]}[Data],
dbo_Schema = Database{[Name="dbo",Kind="Schema"]}[Data],

FakturaKontering1= dbo_Schema{[Name="******",Kind="Table"]}[Data],
FakturaKontering2= Table.RenameColumns(FakturaKontering1,{{"*******","BeløpLinjeInklMva" },{"********", "FakturaId.Kontering"}, {"*****", "Linjenummer"}}),
FakturaKontering3 = Table.AddColumn(FakturaKontering2, "LinjeId.Kontering", each Number.ToText([FakturaId.Kontering])&" - "&Number.ToText([Linjenummer])),
FakturaKontering4 = Table.TransformColumnTypes(FakturaKontering3,{{"LinjeId.Kontering", type text}}),
FakturaKontering6= Table.Join(FakturaKontering4,"*****",Accounting,"Id",JoinKind.LeftOuter),
FakturaKontering_Table= Table.RemoveColumns(FakturaKontering6,{"*****", "*****","*****","*****","*****","*****","*****","*****","*****","*****","*****","*****","*****","*****","*****","*****","*****"})

in
FakturaKontering_Table

Query Invoice Line

let
Kilde = Odbc.DataSource("****", [HierarchicalNavigation=true]),
Database = Kilde{[Name="********",Kind="Database"]}[Data],
dbo_Schema = Database{[Name="dbo",Kind="Schema"]}[Data],
FakturaLinje1 = dbo_Schema{[Name="**********",Kind="Table"]}[Data],

FakturaLinje2 = Table.RenameColumns(FakturaLinje1,{{"******", "Ordrenummer"}, {"********", "MvaSats"}, {"*****", "FakturaId.Linje"}, {"****", "Linjenummer.Linje"},{"******", "Rabatt"},{"*********", "Kvantum"}}),
FakturaLinje3 = Table.TransformColumnTypes(FakturaLinje2,{{"Ordrenummer", type text}}),
FakturaLinje5 = Table.AddColumn(FakturaLinje3, "LinjeId.Linje", each Number.ToText([FakturaId.Linje])&" - "&Number.ToText([Linjenummer.Linje])),
FakturaLinje6 = Table.RemoveColumns(FakturaLinje5,{"********","********","********","********","********","********","********","********","********"}),
FakturaLinje7 = Table.AddKey(FakturaLinje6,{"LinjeId.Linje"},true),
FakturaLinje_Table = Table.AddColumn(FakturaLinje7, "EHandel", each if *********************** then "Ja" else "Nei")

in
FakturaLinje_Table

Query Invoice

This is the only query with load enabled

let

Fakturahode2=Table.AddKey(InvoiceHeader,{"FakturaId"},true)
, TabellMedKontering = Table.Join(Fakturahode2, {"FakturaId"}, InvoiceAccounting, {"FakturaId.Kontering"},JoinKind.LeftOuter),

FullTabell1 = Table.Join(TabellMedKontering , {"LinjeId.Kontering"}, InvoiceLine, {"LinjeId.Linje"},JoinKind.LeftOuter),

FullTabell2 = Table.AddColumn(FullTabell1, "Beløp", each if isnull([*****]) then [BeløpLinjeInklMva] else [*****]/(1+[MvaSats]/1000) ),

#"Fjernede kolonner2" = Table.RemoveColumns(FullTabell2,{"*****","LinjeId.Linje", "Linjenummer", "FakturaId.Kontering", "LinjeId.Kontering","FakturaId.Linje","Linjenummer.Linje"}),
#"Kolonner med nye navn" = Table.RenameColumns(#"Fjernede kolonner2",{{ "*****", "Fakturanummer"}, {"******", "Parkert"},{"********","Leverandørnummer"}}),
#"Endret type" = Table.TransformColumnTypes(#"Kolonner med nye navn",{{"Beløp", type number}})

in
#"Endret type"

[Anonymous]

It looks like Power BI is not powerful enough to do a alot of joins. I used direct quieries instead, so I could do the joins inside the database, and it works. To me this looks like a bug in Power Query.