Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
Solved! Go to 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"
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"
Great! Works like a charm 🙂
Thanks!