Divide by number of rows per item

[RobRoos]

Hi,

I've got this table and want to divide Sum of DiskSizeInGB by number of same Srv-computers.

(In the source data I get, total Sum of DiskSize per server, is listed per app)

Srv-computer	App	Sum of DiskSizeInGB	SizePerApp
TST03310	1	610	305
TST03310	2	610	305
TFSTS131551	A	124751	41583.7
TFSTS131551	B	124751	41583.7
TFSTS131551	C	124751	41583.7
TFXTV120014	J	320	320

I got this far, but get a error:

let
Source = Excel.Workbook(File.Contents(".......\Storage analysis 20220518.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Added Custom" =

Table.AddColumn(Table1_Table, "SizePerApp", 

each [Sum of DiskSizeInGB]/
List.Count(List.Select(Table1_Table[#"Srv-computer"], each _ = [#"Srv-computer"])))

in
#"Added Custom"

Expression.Error: We cannot apply field access to the type Text.
Details:
Value=TST03310
Key=Srv-computer

Is my calculation of number of rows per Srv-computer correct?

Who can help me out?

Thanks in advance!

[Vijay_A_Verma · Accepted solution]

Replace each _ = [#"Srv-computer"] with (x)=>x= [#"Srv-computer"]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCgkOMTA2NjRQ0lEyBGIzICtWB0XYCFnYLTgk2NDY0NQUpNgRpMnIxBzIwZR0wifpjC4ZERJmaGRgYGgClPACYmMjoIWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Srv-computer" = _t, App = _t, #"Sum of DiskSizeInGB" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Srv-computer", type text}, {"App", type text}, {"Sum of DiskSizeInGB", Int64.Type}}),
    BuffList = List.Buffer(#"Changed Type"[#"Srv-computer"]),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "SizePerApp", each Number.Round([Sum of DiskSizeInGB]/List.Count(List.Select(BuffList, (x)=> x=[#"Srv-computer"])),1))
in
    #"Added Custom"

[RobRoos]

Great! Works like a charm 🙂

Thanks!