Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Power Query extremely slow / load not working

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?

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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.