Power Query count rows

[rs1aj]

I created an index column after sorting a column descending. Now, I want to create a new custom column taking their index number divided by the number of total rows or Max index number:

New Column = [Index]/Countrows(Table) or New Column = [Index]/Max([Index])

Example:

[alansidman]

See if this link helps

https://community.powerbi.com/t5/Des...mn/td-p/218038

[rs1aj]

The link looks like a DAX solution but I'm using Power Query so I need a M language solution. Or can you use DAX formulas in power query?

[romperstomper]

You can create another query that just returns the max index number, then in yoyur main query add a custom column using a formula like:

#"Name of max query"{0}

which will return a record for each row, and you just expand that.

[rs1aj]

I have no idea how to do the steps you suggested. I'm surprised that in the M language, you can't just write:

New Column = [Index] / Table.RowCount(Table) but this doesn't work as I've found out.

[romperstomper]

Actually, you can do that - you just need to use the name of the previous step as the table name. So if you'd just added an Index column, it would be something like:

=[Index]/Table.RowCount(#"Added Index")

[rs1aj]

Perfect. This worked. Now, one more. Can I use a similar formula to pull the MAX number from my recently added column?

Like:
New Column = [Column] / Max(#"Added Index1")

[romperstomper]

I think you'd be back to a separate query for that - although if you're referring to the column you just created, isn't the max value just 1?

[rs1aj]

Actually, I was typing out a response of what I'm doing and realized a way to do what I need without a "Max" function.

Thanks for your help!