Power Query [Index]/List.Max([Index])

[ahmetkeles]

Hello;
I have a table looks like the sample below.

.

I aplly these Power Query steps to my data:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Point", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Test", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Dizin", 1, 1),
    #"Added Column" = Table.AddColumn(#"Added Index", "Special", each [Index]/List.Max(#"Added Index"[Index])),
    #"Added Column1" = Table.AddColumn(#"Added Column", "New Point", each [Point]*[Special]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Column1",{"Index", "Special"})
in
    #"Removed Columns"

I get this result.

Since my table has 1200000 rows, it works very slow. Is there a way to make it faster?

 

[GraH]

Maybe calculate List.Max only once and not 1.2 Mio times already helps.

Add a step "max" after your index. Same as your step but without table.addcolumn part.

Then insert a step that refers again to the addindex and add your calculation referring tot max.

 

[GraH]

With the code below, at my end it loaded records by approx 20K-25K a time. And took under 2 minutes to finish.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Point", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Test", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
    Last = Table.RowCount(#"Added Index"),
    #"Added Column" = Table.AddColumn(#"Added Index", "Special", each [Index]/Last),
    #"Added Column1" = Table.AddColumn(#"Added Column", "New Point", each [Point]*[Special]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Column1",{"Index", "Special"})
in
    #"Removed Columns"

 

[ahmetkeles]

Hi GraH;

Thanks for your help. It really works. I appreciate it.

 

[GraH]

That's very good to read.