Forum Discussion
Keep only lines starting with a date
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 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
2/8 - Some notes with latest updates |
Mettel Circuit ID - 66NBDB2233445 - Mettel Ticket No - 4813181
Random 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. |
- 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.