Finding a way to split the column

[Anonymous]

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.

[Icey]

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.

[vanessafvg]

please show your expected results.

[Anonymous]

Hey hi, 

Thanks for following up

The result should look like

Numbers	Exams	Years
1	DAX-100-PowerBI-2018-2021	2018-2021
2	20-DAX-200-PowerBI-2021	2021
3	201-DAX-205-powerBI-2019-2020	2019-2020

Thanks 

[mahoneypat]

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

[Anonymous]

Hi Pat, 

Morning! 

Thanks pat, it seems to be working - But, how can I change that code to my scenario 

could you please ellobrate it. (While I am trying to perform in the same way. 

Added custom column, 

(Paste the code you provided)

from "Table.AddColumn"  then it was creating a new table and while expanding I selected only the new column I required. 

But, few columns were working fine others are wrongly misplaced. 

Thanks pat, 

looking forward to hear from you. 

[mahoneypat · Accepted solution]

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