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

ahmetkeles

New Member
Joined
May 9, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
Hello;
I have a table looks like the sample below.

.
Sample Data.png

I aplly these Power Query steps to my data:

Power Query:
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.

Target.png

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

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
598
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 22, 2020
Messages
598
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
With the code below, at my end it loaded records by approx 20K-25K a time. And took under 2 minutes to finish.
Power Query:
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

New Member
Joined
May 9, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
Hi GraH;

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

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
598
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
That's very good to read.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,741
Messages
5,524,560
Members
409,584
Latest member
Devil_717

This Week's Hot Topics

Top