Forum Discussion
Solved
Count Number of Line Breaks in a Cell
Hello, I am trying to count the number of line breaks in a cell. The Process: Data is a spreadsheet downloaded from Sharepoint List uploaded by a PowerApp Multiple "Network Numbers" can be s...
Hi jalissa13
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjc0NDK3MLGMyTM3AANTJR0lI6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Network Number" = _t, #"Activity Hours" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Network Number", type text}, {"Activity Hours", Int64.Type}}), Custom1 = Table.TransformColumns(#"Changed Type",{{"Network Number", each Text.Split(_,"#(lf)")}}), #"Added Custom" = Table.AddColumn(Custom1, "Hrs Per Network", each [Activity Hours]/List.Count([Network Number])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Activity Hours"}), #"Expanded Network Number" = Table.ExpandListColumn(#"Removed Columns", "Network Number") in #"Expanded Network Number"
Hi jalissa13
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjc0NDK3MLGMyTM3AANTJR0lI6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Network Number" = _t, #"Activity Hours" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Network Number", type text}, {"Activity Hours", Int64.Type}}),
Custom1 = Table.TransformColumns(#"Changed Type",{{"Network Number", each Text.Split(_,"#(lf)")}}),
#"Added Custom" = Table.AddColumn(Custom1, "Hrs Per Network", each [Activity Hours]/List.Count([Network Number])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Activity Hours"}),
#"Expanded Network Number" = Table.ExpandListColumn(#"Removed Columns", "Network Number")
in
#"Expanded Network Number"
Anonymous
Thank you so much! it worked perfectly. I am more of a button pusher on Power Query rather than a program from scratch person, so I was slightly confused on how the Custom1 was created, but it did work perfectly.
I really appreciate your quick response.