cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sai_Vadde
Regular Visitor

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.
 
 
IDColumn
1PowerBI-Dax100-Microsoft-2018 (3 delimiters)
2PowerBI-Dax100-Microsoft-2018-2021 (4 delimiters)
3Power Automate-END100-2016 (2 delimiters)
4PowerBI-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.
1 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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

6 REPLIES 6
Icey
Community Support
Community Support

Hi @Sai_Vadde ,

 

Based on my test, @mahoneypat 's method should work in your scenario.

 

Icey_1-1635152570693.png

 

In addition, you can also try this:

 

Icey_0-1635152093629.png

 

 

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
Super User
Super User

please show your expected results.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Hey hi, 
Thanks for following up

The result should look like
 
NumbersExamsYears
1DAX-100-PowerBI-2018-20212018-2021
220-DAX-200-PowerBI-20212021
3201-DAX-205-powerBI-2019-20202019-2020

 

Thanks 

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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. 

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors