cancel
Showing results for 
Search instead for 
Did you mean: 
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
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.