Performance?

[Misha]

Hi, I am e pretty new Power BU-user and the lowspeed of the application drives me crazy.... As i think this can t be normal I want to post a concrete case: I have a connection with an acces-table containing 4k rows en added a column where i counted the fiscal year (if date between x and y then...) Now it takes me about 120 seconds to adapt this information in the query....closing and applying the query takes me another 200 seconds....Not normal I guess?

[v-jiascu-msft]

Hi @Misha,

If you can share the pbix file, that would be great. You can mask the confidential parts first.

Best Regards,

Dale

[Anonymous]

Hi Misha, welcome to the forum!

Please post a screenshot showing your data model (the relationships view), and then copy and paste the text of the query that you're referring to.  Under the View tab on the Query Editor screen, click "Advanced Editor".

That should help us troubleshoot your issue.

[Misha]

Tx fr your reply,

Hereby a specific case: I wanted to add the additional column [TotMarket] in the function of the additional column [TotKost]. This took me exactly 5 minutes to adapt the query and another 1.45min to close and aply the query. Knowing that I m just started Power BI and work with an uthermost simple model this looks not right to me...

As you asked infra the info you asked for

Tx in advance. 

let
    Bron = Sql.Databases("***\sql2008r2 of ***\sql2008r2,55208"),
    +++_live = Bron{[Name="+++_live"]}[Data],
    dbo_vw_vto_rpt = +++_live{[Schema="dbo",Item="vw_vto_rpt"]}[Data],
    #"Begindatum gefilterd op >300612" = Table.SelectRows(dbo_vw_vto_rpt, each [begindatum] > #datetime(2012, 6, 30, 0, 0, 0)),
    #"Kolommen verwijderd" = Table.RemoveColumns(#"Begindatum gefilterd op >300612",{"journaal", "progdeeln", "prognlicenties"}),
    #"Kolom 'TotOmzet' toegevoegd" = Table.AddColumn(#"Kolommen verwijderd", "TotOmzet", each [omzet]+[expomzet]),
    #"Kolom 'TotMarket' toegevoegd" = Table.AddColumn(#"Kolom 'TotOmzet' toegevoegd", "TotMarket", each [folders]+[expfolders]+[verzending]+[expverzending]+[portkosten]+[expportkosten]),
    #"Kolom 'TotKost' toegevoegd" = Table.AddColumn(#"Kolom 'TotMarket' toegevoegd", "TotKost", each [kosten]+[expkosten]-[TotMarket]),
    #"Volgorde van kolommen gewijzigd" = Table.ReorderColumns(#"Kolom 'TotKost' toegevoegd",{"jaar", "nummer", "cursusverantwoordelijke", "project", "omschrijving", "begindatum", "status", "aantdeeln", "TotOmzet", "omzet", "expomzet", "kosten", "expkosten", "catering", "expcatering", "progncatering", "docenten", "expdocenten", "intdocent", "expintdocent", "boeken", "expboeken", "prognboeken", "didac", "expdidac", "licenties", "explicenties", "klanal", "vto_jaar", "vto_journaal", "vto_nummer", "andere", "folders", "verzending", "expandere", "expfolders", "expverzending", "produkt", "verkoopprijs", "portkosten", "expportkosten", "hoofdproduct", "hoofdproject", "TotKost"}),
    #"2017OP0718 verwijderd uit projecten" = Table.SelectRows(#"Volgorde van kolommen gewijzigd", each ([project] <> "2017OP0718")),
    #"Type gewijzigd" = Table.TransformColumnTypes(#"2017OP0718 verwijderd uit projecten",{{"begindatum", type date}}),
    #"Notie ""Boekjaar"" toegevoegd" = Table.AddColumn(#"Type gewijzigd", "Boekjaar", each if [begindatum] >= #date(2022, 7, 1) then "22-23" else if [begindatum] >= #date(2021, 7, 1) then "21-22" else if [begindatum] >= #date(2020, 7, 1) then "20-21" else if [begindatum] >= #date(2019, 7, 1) then "19-20" else if [begindatum] >= #date(2018, 7, 1) then "18-19" else if [begindatum] >= #date(2017, 7, 1) then "17-18" else if [begindatum] >= #date(2016, 7, 1) then "16-17" else if [begindatum] >= #date(2015, 7, 1) then "15-16" else if [begindatum] >= #date(2014, 7, 1) then "14-15" else if [begindatum] >= #date(2013, 7, 1) then "13-14" else if [begindatum] >= #date(2012, 7, 1) then "12-13" else 99),
    #"Type gewijzigd1" = Table.TransformColumnTypes(#"Notie ""Boekjaar"" toegevoegd",{{"TotOmzet", Int64.Type}, {"omzet", Int64.Type}, {"expomzet", Int64.Type}, {"kosten", Int64.Type}, {"expkosten", Int64.Type}, {"catering", Int64.Type}, {"expcatering", Int64.Type}, {"progncatering", Int64.Type}, {"docenten", Int64.Type}, {"expdocenten", Int64.Type}, {"intdocent", Int64.Type}, {"expintdocent", Int64.Type}, {"boeken", Int64.Type}, {"expboeken", Int64.Type}, {"prognboeken", Int64.Type}, {"didac", Int64.Type}, {"expdidac", Int64.Type}, {"licenties", Int64.Type}, {"explicenties", Int64.Type}, {"andere", Int64.Type}, {"folders", Int64.Type}, {"verzending", Int64.Type}, {"expandere", Int64.Type}, {"expfolders", Int64.Type}, {"expverzending", Int64.Type}, {"verkoopprijs", Int64.Type}, {"portkosten", Int64.Type}, {"expportkosten", Int64.Type}, {"TotKost", Int64.Type}, {"TotMarket", Int64.Type}}),
    #"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Type gewijzigd1", "Winst", each [TotOmzet]-[TotKost]+[TotMarket]),
    #"Type gewijzigd2" = Table.TransformColumnTypes(#"Aangepaste kolom toegevoegd",{{"Winst", Int64.Type}})
in
    #"Type gewijzigd2"