Forum Discussion
Power Query extremely slow / load not working
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?
- Anonymous6 years agoNot applicable
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 Accountinglet
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_TableQuery 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 Linelet
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 InvoiceThis 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"- Anonymous6 years agoNot applicable
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.