Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
MrTWolff__
New Member

Convert 2010-2020 into Years

Hello Everybody!

This is my first post on the community. So, here I go.
I'm currently working on a dataset that I got from Kaggle, which is Netflix's popular movies and series, This dataset comes from the IMDB website data collected by using web scraping. 

I can't figure out a way to transform the dates of the series that range from a StartYear and EndYear in the cell into a date, they're in format text right now.  Generally, series dates vary from a Start and End Year; in some cases, some series only have (2019-), which means it's still on the air.
Is there a way that I can make them become actual dates instead of only text? 
Sorry if my question sounds stupid I'm quite new to powerBI, I'm still figuring out what the heck I'm doing,

Cheers Guys!!!!

 

MrTWolff___0-1665008724174.png

@https://www.kaggle.com/datasets/narayan63/netflix-popular-movies-dataset

2 ACCEPTED SOLUTIONS
Shaurya
Memorable Member
Memorable Member

Hi @MrTWolff__,

 

The first step to this would be to split your column into Start and End Year from the range format like 2002-2006.

 

Screenshot 2022-10-06 052512.jpg

 

You can choose '-' as the delimiter. This will give you two columns, one with start year and another with end year. Note that you'll get a null value in the second column for shows that have not yet ended like Stranger Things (Awaiting Finale🙂).

 

Now you have two columns with year but those would be strings. In order to convert them into dates, just select Date in the list of data types.

 

Screenshot 2022-10-06 052723.jpg

 

Result should look like this:

 

Screenshot 2022-10-06 053705.jpg

 

Works for you? Mark this post as a solution if it does!

View solution in original post

v-yueyunzh-msft
Community Support
Community Support

Hi , @MrTWolff__ 

Here are the steps you can refer to :

(1)This is my test data :

vyueyunzhmsft_1-1665049237680.png

(2)We can select "Insert Step After" to add a step .

vyueyunzhmsft_2-1665049331397.png

(3)We can enter this M language in the input box :   The firest parameter is the next step name.

= Table.SplitColumn(test, "year" , (x)=> Text.Split(x,"-") ,{"start_year","end_year"},null  )

vyueyunzhmsft_3-1665049427934.png

(4)Then we can change the type of the [end_year] type to whole number if we need.

vyueyunzhmsft_4-1665049470781.png

(5)Then we can meet your need , the result is as follows:

vyueyunzhmsft_5-1665049499629.png

 

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

 

 

 

View solution in original post

3 REPLIES 3
MrTWolff__
New Member

@v-yueyunzh-msft @Shaurya Thank you so so so much Guys. You helped me a lot... Following both of you guys, I took the best approach e figured out the best solution to my problem. 

Cheers guys!!!!!

v-yueyunzh-msft
Community Support
Community Support

Hi , @MrTWolff__ 

Here are the steps you can refer to :

(1)This is my test data :

vyueyunzhmsft_1-1665049237680.png

(2)We can select "Insert Step After" to add a step .

vyueyunzhmsft_2-1665049331397.png

(3)We can enter this M language in the input box :   The firest parameter is the next step name.

= Table.SplitColumn(test, "year" , (x)=> Text.Split(x,"-") ,{"start_year","end_year"},null  )

vyueyunzhmsft_3-1665049427934.png

(4)Then we can change the type of the [end_year] type to whole number if we need.

vyueyunzhmsft_4-1665049470781.png

(5)Then we can meet your need , the result is as follows:

vyueyunzhmsft_5-1665049499629.png

 

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

 

 

 

Shaurya
Memorable Member
Memorable Member

Hi @MrTWolff__,

 

The first step to this would be to split your column into Start and End Year from the range format like 2002-2006.

 

Screenshot 2022-10-06 052512.jpg

 

You can choose '-' as the delimiter. This will give you two columns, one with start year and another with end year. Note that you'll get a null value in the second column for shows that have not yet ended like Stranger Things (Awaiting Finale🙂).

 

Now you have two columns with year but those would be strings. In order to convert them into dates, just select Date in the list of data types.

 

Screenshot 2022-10-06 052723.jpg

 

Result should look like this:

 

Screenshot 2022-10-06 053705.jpg

 

Works for you? Mark this post as a solution if it does!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors