Forum Discussion

jalissa13's avatar
jalissa13
Regular Visitor
4 years ago
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...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
jalissa13
Regular Visitor
4 years ago

Hi Anonymous ,
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)

71597106N/A
N/A
N/A
N/A
N/A
71597107N/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's avatar
    jalissa13
    Regular Visitor
    4 years ago

    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.