Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculation time increases for each added Calculated Column - Power Query

Hi, I am pulling a csv file data into power query to do cleaning and transformations to the data. There are around 300K `Attrb` rows, of which i am only filtering Attrb1,Attrb3,Attrb4,Attr5,Attr6. T...
Need Help
Show and Tell
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Jimmy801 ,

 

I copied your code to the Advanced editor, but i am getting the following error :

 

Formula.Firewall: Query 'tblOriginal' (step 'FilteredInnoRows') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

 

Any idea why this is happening? 

 

My original code was :

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"INNO", type text}, {"INNO_SORT", Int64.Type}, {"Attrb6", Int64.Type}, {"Attrb5", Int64.Type}, {"Attrb4", Int64.Type}, {"Attrb3", Int64.Type}, {"Attrb1", Int64.Type}}),
    InsertedTotal = Table.AddColumn(ChangedType, "Total", each [Attrb6] + [Attrb4], Int64.Type),
    InsertedAttrb6AvgPercent = Table.AddColumn(InsertedTotal, "Attrb6 Avg%", each [Attrb6]/List.Average(InsertedTotal[Attrb6]),Percentage.Type),
    InsertedAttrb4AvgPercent = Table.AddColumn(InsertedAttrb6AvgPercent, "Attrb4 Avg%", each [Attrb4]/List.Average(InsertedAttrb6AvgPercent[Attrb4]),Percentage.Type),
    InsertedTotalAvgPercent = Table.AddColumn(InsertedAttrb4AvgPercent, "Total%", each [Total]/List.Average(InsertedAttrb4AvgPercent[Total]),Percentage.Type),
    FilteredInnoRows = if drpInno =null then InsertedTotalAvgPercent else Table.SelectRows(InsertedTotalAvgPercent, each Text.Contains([INNO], drpInno,Comparer.OrdinalIgnoreCase))
in
    FilteredInnoRows

...which is refering previous steps. But the only difference between your code and mine is that you are calculating the averages upfront (which i had tried earlier but still same loading time) but they are also still referencing previous steps.

 

What would be the difference to change the loading time? Is it because the data is referencing a 300K rows CSV file that gets filtered on 5-6 Attrb codes and then these are pivoted as columns?

Jimmy801's avatar
Jimmy801
Community Champion
5 years ago

Hello Anonymous 

 

this is because of the safety settings. Or you are ignoring them in the settings, or you are using this code instead

let
    Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"INNO", type text}, {"INNO_SORT", Int64.Type}, {"Attrb6", Int64.Type}, {"Attrb5", Int64.Type}, {"Attrb4", Int64.Type}, {"Attrb3", Int64.Type}, {"Attrb1", Int64.Type}}),
    InsertedTotal = Table.AddColumn(ChangedType, "Total", each [Attrb6] + [Attrb4], Int64.Type),
    AverageAttrb6 = List.Average(InsertedTotal[Attrb6]),
    InsertedAttrb6AvgPercent = Table.AddColumn(InsertedTotal, "Attrb6 Avg%", each [Attrb6]/AverageAttrb6,Percentage.Type),
    AverageAttrb4 = List.Average(InsertedAttrb6AvgPercent[Attrb4]),
    InsertedAttrb4AvgPercent = Table.AddColumn(InsertedAttrb6AvgPercent, "Attrb4 Avg%", each [Attrb4]/AverageAttrb4,Percentage.Type),
    AverageTotal = List.Average(InsertedAttrb4AvgPercent[Total]),
    InsertedTotalAvgPercent = Table.AddColumn(InsertedAttrb4AvgPercent, "Total%", each [Total]/AverageTotal,Percentage.Type),
    GetFilter = 
    let
        Source = Excel.CurrentWorkbook(){[Name="drpInno"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"INNO", type any}})[INNO]{0}
    in
        #"Changed Type",
    FilteredInnoRows = if drpInno =null then InsertedTotalAvgPercent else Table.SelectRows(InsertedTotalAvgPercent, each Text.Contains([INNO], GetFilter,Comparer.OrdinalIgnoreCase))
in
    FilteredInnoRows

 

no, it's the referecing that makes the difference. in a new column, you are refercing a list and it has to be evaluated each time. 

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    The error was because of a typo `AverarageAttrb6`  in this line:

     

        InsertedAttrb6AvgPercent = Table.AddColumn(InsertedTotal, "Attrb6 Avg%", each [Attrb6]/AverarageAttrb6,Percentage.Type),

     

     After changing that, it is working.

     

    Which list are you referring that is causing the loading issue? How do i resolve it correctly?

     

    Any Idea on how to calculate PERCENTILE in Power Query for the last 3 columns?