Forum Discussion
Finding a way to split the column
Hello everyone,
Morning!
I hope you guys are doing well.
Appreciate all your replies
I had a column in Power BI, where the data was not in the same format, and I am looking for splitting that column where it starts with year.
| ID | Column |
| 1 | PowerBI-Dax100-Microsoft-2018 (3 delimiters) |
| 2 | PowerBI-Dax100-Microsoft-2018-2021 (4 delimiters) |
| 3 | Power Automate-END100-2016 (2 delimiters) |
| 4 | PowerBI-Automate-Dax100-End100- 2021-2021 (5 delimiter's) |
I want to split that column where it starts with years.
Thank you everyone.
Looking forward for your responses.
You just need to add a custom column to your existing query and put this formula in the pop-up box. Replace Exams with your actual column name.
Text.Combine(List.Select(List.LastN(Text.Split([Exams], "-"),2), each Text.Contains(_, "2")), "-")Pat
6 Replies
- vanessafvg
Community Champion
please show your expected results.
- mahoneypat
Microsoft Employee
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJxjNA1NDDQDcgvTy1y8tQ1MjC0ABJGIDkEO1YnWskILKIL0mCEogGqFqrMGKIRqs5UtwBhsCVIsQFEHsqOjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Numbers = _t, Exams = _t, Years = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Numbers", Int64.Type}, {"Exams", type text}, {"Years", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.Select(List.LastN(Text.Split([Exams], "-"),2), each Text.Contains(_, "2")), "-"), type text) in #"Added Custom"Pat
- Icey
Community Support
Hi Anonymous ,
Based on my test, mahoneypat 's method should work in your scenario.
In addition, you can also try this:
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
