Count Number of Line Breaks in a Cell

[jalissa13]

Hello,

I am trying to count the number of line breaks in a cell. 

The Process:

1. Data is a spreadsheet downloaded from Sharepoint List uploaded by a PowerApp
2. Multiple "Network Numbers" can be selected per a job name
3. The "Activity Hours" entered are the total, not the network

Output:

1. The query should create a new column
2. New column should have formula that references the "Network Number" column
3. Count the number of line breaks (aka the number of network numbers in the cell) - New Column = "# Networks"
4. New column will be created and will divide the "Activity Hours" by this new "#Networks" - (New Column = "Hrs Per Network") (I know this part)
5. Action will Split all line breaks into seperate rows, we now are able to see the number of hours per network (I know this part)

Question:

I do not know how to create a formula or program to count the line breaks.

In excel the formula would be

=LEN(['Network Number'])-LEN(SUBSTITUTE(['Network Number'],CHAR(10),""))+1

I know that =Len(['Network Number']) can be written as

Text.Length([Network Number])

but I do not know where to go from here.

Pre Query Sample Data of one Row:

Network Number	Activity Hours
71127849 70000005	2

Ideal Query Output:

Network Number	Hrs Per Network
71127849	1
70000005	1

[Vera_33 · Accepted solution]

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"

[jalissa13]

Hi @Vera_33 ,
This project seems to be ever growing. 

It has slightly modified so I now have two columns I would like to split that are related to each other.

Example of Raw Data:

71597106 71597106 71597107 71597108 71597109

N/A N/A N/A N/A N/A

I copied the code from last time and edited it to match the new column "Designations". 

The problem is doubling the data. Example: (note it is not always N/A sometimes it is A, B, C)

71597106	N/A N/A N/A N/A N/A
71597107	N/A N/A N/A N/A N/A

Once it splits into seperate rows again, I now have doubled (if not more) the data. 

Is there a way to split them at the same time? 

I think if I continue this way, the filter down part will also double the time that is summed at the end.

[jalissa13]

Nevermind I did it! 
I created both columns into List and then created a table using both columns.

Then I used the same formula to split the Time by the list count.

Last I Expanded the table.

[jalissa13]

@Vera_33 

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.