none
Power Query bug and performances impact RRS feed

  • Question

  • Hi,

    Performance problem investigation

    In the actual French Excel Power Query 64 Bits, reinstalled On 2017/03/16. There is a performance Bug.

    In need to deal with 40 dimensions, that represent 2000 elements, that can be considered as very small.

    My initial Excel file, that manipulate all the dimensions, is entirely blocked, frozen,…. The modifications of one of the 40 requests become impossible with the query interface, even add a simple filter.

    1°) One dimension

    I made a single Excel File dealing with One dimension. In this case result splash very quickly and you can easily modify the query. So the query have no internal user bug.

    2°) All dimension, 40 queries

    In the global Excel file, the same query is impossible to manipulate. A poor join block without any answer.

    So the actual bug impact the performance of power query that become unusable.

    3°) Excel Comportment

    After a run of the query, Excel give an answer: 2254 rows.

    And the PC is still running without stopping. I can see in the task management system process to 90%, memory to 99% and 3 microsoft mashup procedures corresponding to query treatment running.

    What are they doing ? Try to refresh the interface ?

    Please Help !

    Jerome 

     

    Here is the code of the treatment of one dimension.

    • It open a format sheet containing 1 row
    • It cross it with the dimension to fill all the dimension value (load from an array to make it more simple).
    • It make some formating and trivial settings.

     

    let

        Source = Excel.CurrentWorkbook(){[Name="Invoice"]}[Content],

        #"Requêtes fusionnées" = Table.NestedJoin(Source,{"Link"},#"FichiersCsvCoreTableau",{"Link"},"NewColumn",JoinKind.LeftOuter),

        #"NewColumn développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "NewColumn", {"Name", "Personnalisé.Column1"}, {"Name", "Personnalisé.Column1"}),

        #"Lignes filtrées2" = Table.SelectRows(#"NewColumn développé", each ([Name] = "COR_PDT_LINE.csv")),

        #"Colonnes supprimées4" = Table.RemoveColumns(#"Lignes filtrées2",{"Name"}),

        #"Lignes filtrées1" = Table.SelectRows(#"Colonnes supprimées4", each not Text.EndsWith([Personnalisé.Column1], "Code")),

        #"Colonnes supprimées" = Table.RemoveColumns(#"Lignes filtrées1",{"Product Line0 Code"}),

        #"Colonnes renommées3" = Table.RenameColumns(#"Colonnes supprimées",{{"Personnalisé.Column1", "Product Line0 Code"}}),

        #"Colonnes supprimées1" = Table.RemoveColumns(#"Colonnes renommées3",{"Link"}),

        #"Index ajouté" = Table.AddIndexColumn(#"Colonnes supprimées1", "Index", 1, 1),

        #"Colonnes supprimées2" = Table.RemoveColumns(#"Index ajouté",{"Quantity Sold (Net) - Detailled"}),

        #"Colonnes supprimées3" = Table.RemoveColumns(#"Colonnes supprimées2",{"Quantity Sold (Net) - Packaged", "Sales (Net)"}),

        #"Personnalisée ajoutée" = Table.AddColumn(#"Colonnes supprimées3", "Custom", each [Index]),

        #"Colonnes renommées" = Table.RenameColumns(#"Personnalisée ajoutée",{{"Custom", "Quantity Sold (Net) - Detailled"}}),

        #"Personnalisée ajoutée1" = Table.AddColumn(#"Colonnes renommées", "Custom", each 1),

        #"Colonnes renommées1" = Table.RenameColumns(#"Personnalisée ajoutée1",{{"Custom", "Quantity Sold (Net) - Packaged"}}),

        #"Colonnes renommées2" = Table.RenameColumns(#"Colonnes renommées1",{{"Index", "Sales (Net)"}})

    in

        #"Colonnes renommées2"

    Friday, March 17, 2017 9:28 AM

Answers

  • Super mega over Cool,

    I don't know yet if I still have problems but the option desctivation is the solution to manipulate little request when you have plenty.

    Thanks a lot !

    By the way I noticed very Strange comportment in the actual version. I'am going to check and post new persisting problems. 

    Jerome

    Friday, March 17, 2017 1:17 PM

All replies