Forum Discussion
Keep only lines starting with a date
Hello,
New to power BI, I am trying to pull just a section of data from a ticketing system. All the data below will pull into one cell. I am attempting to pull just the data starting with a date. Unfortunately that does not always start or end on the same number of lines. Any easy way to do this?
********************************************************
Circuit ID - XXXXXXXXX - Ticket No - XXXXX
Customer Contact number - XXXXXXXXXXXXXXXXXXXXX
01/28 - xxxxxxxxxxx
01/28 - xxxxxxxxxxxxxxxxxxxxxxxx
01/29 - xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
1/31 - xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
01/31 - xxxxxxxxxxxxxxxxxxxxxxxxxxx
02/01 - Ixxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
02/01 - xxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
************************************************************************
Hi, mahlman
You need to split the data row by row, and then split the data column by "-" again.
You can then filter the rows of data by determining if the row data contains date values.
M code:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVTbbptAEP2VIz8bw3KPn+pL3FrxTQb1oW4e1rCpUTAbwSIrf9/h4hBFlUwidYXQsnPmMHN2Zg6Hwao8iwyz5RxjzAPGTNOybBsYAo0pfAjJ5Diu63m+D1pBogRmMlM8UmRaJHmhsOL0chxHsyxLY7SIYZHS4bcoTUSmRtv998p5GIhIZjHPXz9SFB0HuWsmrcHj8DBYC6VE2oboOJvpfMpMy/e8KsjW2ATpey6zbav2ql/otqbuQ0MgzwKZVKLAJVEnpJy2CuVLXG1a4F0/oGHqzLiF1IBCVF/giJPihavohCeZY12J8ArisHEU6iJIamaODQO7NXgWw272oy6FqxBJHpWJAumhwXUrPZpLc+j7qkcSPdNvN5KObJ9ZzGc1xXyLzTbEbLUN7hH+WAagZ4Jwu0M4WT3c77u/GUw3e0p2hfbF9hSY6Ra7iRzhSluDe6JN3fhfaLrRT2ANu0eGiHiaNqbJ79IwTDcMIbO2hqjPxBEeGbkCM/gZETlXMbTFcEnIu6ZIMiWhToKKYDZ6y83rE+9aCJVkfwhyQRGdRBmnIq4rmTqro7qdzEKmKXFQR9TmqCwUeVQ0d/9s3D01gzxTKdPQWSKmmYELz1QH+PnrXUuMZyRPM8JULstjKqCe1dikw2ooVUPFwW65oWkx2cw/jrKpPGIviwK+72st/F1H0NX2KMgvYa3bwtHlifyJRwKBMHSmG2P2CfcvQfsk8PgX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type", "Text Before Delimiter", each Text.BeforeDelimiter([Column1], "-"), type text), #"Added Custom Column" = Table.AddColumn(#"Inserted Text Before Delimiter", "Custom", each try Date.From([Text Before Delimiter]) otherwise null), #"Filtered Rows" = Table.SelectRows(#"Added Custom Column", each ([Custom] <> null)) in #"Filtered Rows"result：
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- amitchandak
Super User
mahlman ,this is not helping
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. 3 examples below, I just need the dated lines in red none of the other data.
Lumen CID : DS11223344 , Lumen TKT : 55667788 Site Contact : First Last 555-333-1111 , [email protected] ,Secondary Contact : Firsts Last 555-111-2222
Mettel CID : 55NBDB123877 , Mettel TKT : 87614432/8 - Some notes with latest updates
2/9 - Some notes with latest updates
02/10/- Some notes with latest updates- set up a dispatch for Monday 2/14 between 12:00 PM and 4:00 PM.Mettel Circuit ID - 66NBDB2233445 - Mettel Ticket No - 4813181
DO NOT CLOSE THIS IS A TOP TALKER
01/28 - Some notes with latest updates
01/28 - Some notes with latest updates
01/29 - Some notes with latest updates
1/31 - Some notes with latest updates.
01/31 - Some notes with latest updates.
02/01 - Some notes with latest updates.
02/01 - Some notes with latest updates.
2/1 - Some notes with latest updates.
2/04 - Some notes with latest updates. call with A&TT on Monday, Feb 7th at 10am cst - Mettel will call into the NOC.
02/07 - Some notes with latest updates. Meeting now scheudled for 2/8
02/07- Some notes with latest updates. Follow up with customer 2/9Random Note I dont want
VZ Circuit ID:C00223344 trouble tkt:2022111122225 PIN : AND Site Contact : Bob Ross 888-1112222
02/11 - Some notes with latest updates.
02/11 - Some notes with latest updates.
02/13- Some notes with latest updates. interface Se0/1/0:1
02/13- Some notes with latest updates.
02/13- Some notes with latest updates.
02/13 - Some notes with latest updates.- v-easonf-msft
Community Support
Hi, mahlman
You need to split the data row by row, and then split the data column by "-" again.
You can then filter the rows of data by determining if the row data contains date values.
M code:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVTbbptAEP2VIz8bw3KPn+pL3FrxTQb1oW4e1rCpUTAbwSIrf9/h4hBFlUwidYXQsnPmMHN2Zg6Hwao8iwyz5RxjzAPGTNOybBsYAo0pfAjJ5Diu63m+D1pBogRmMlM8UmRaJHmhsOL0chxHsyxLY7SIYZHS4bcoTUSmRtv998p5GIhIZjHPXz9SFB0HuWsmrcHj8DBYC6VE2oboOJvpfMpMy/e8KsjW2ATpey6zbav2ql/otqbuQ0MgzwKZVKLAJVEnpJy2CuVLXG1a4F0/oGHqzLiF1IBCVF/giJPihavohCeZY12J8ArisHEU6iJIamaODQO7NXgWw272oy6FqxBJHpWJAumhwXUrPZpLc+j7qkcSPdNvN5KObJ9ZzGc1xXyLzTbEbLUN7hH+WAagZ4Jwu0M4WT3c77u/GUw3e0p2hfbF9hSY6Ra7iRzhSluDe6JN3fhfaLrRT2ANu0eGiHiaNqbJ79IwTDcMIbO2hqjPxBEeGbkCM/gZETlXMbTFcEnIu6ZIMiWhToKKYDZ6y83rE+9aCJVkfwhyQRGdRBmnIq4rmTqro7qdzEKmKXFQR9TmqCwUeVQ0d/9s3D01gzxTKdPQWSKmmYELz1QH+PnrXUuMZyRPM8JULstjKqCe1dikw2ooVUPFwW65oWkx2cw/jrKpPGIviwK+72st/F1H0NX2KMgvYa3bwtHlifyJRwKBMHSmG2P2CfcvQfsk8PgX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type", "Text Before Delimiter", each Text.BeforeDelimiter([Column1], "-"), type text), #"Added Custom Column" = Table.AddColumn(#"Inserted Text Before Delimiter", "Custom", each try Date.From([Text Before Delimiter]) otherwise null), #"Filtered Rows" = Table.SelectRows(#"Added Custom Column", each ([Custom] <> null)) in #"Filtered Rows"result：
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
