so incredibly helpful! honestly, it wasn't even so much the step by step that helped, because of course there are multiple way to come up with your own conclusions, but what helped me was even when you brought up the missing values in a column, or the "0 and less than or equal to 0". It really helped me think through how i could handle and clean my data in the future, and why i would look for such answers in the first place! very insightful, so thank you for that :)
Thanks for the kind words. Besure and Click , Subscribe, Like and Bell icons, and of course share the channel with friends who are interested in data analytics.
I appreciate this a ton, for the trip durations with negatives, i figured out the issue was midnight, so i'm going to try and take the number + 60 and it should give the proper trip times.
I continue to go through this data, handing it in different ways such as SQL, Excel and R. While job hunting my R ability is limited at best, but hopefully it will make sense eventually. Thanks so much for doing so much work to show multiple ways to reach the end goal.
From what I have seen on UA-cam and other sources when starting out as an "entry-level" Data Anlytist you start working with SQL, Power BI (or Tabluea ), and Excel.
Sir Thank you so much for sharing this video🙏 this is my first time learning, I have never built any portfolio before and I feel clueless about how to start. Until I found your video! Really helpful and I can learn how to clean real data. From this video, I know many tips and I can improve my skills as well. The next things I want to improve are my SQL, python, and R programming.
this video has been extremely useful to me and liked how you talked about the missing values and duration less than 0, someone else here has said its because of midnight, it made me think how to think about cleanly data
Wow, this is immensely helpful. Thank you for an advance walkthrough of how to handle these data sets. I have completed all the coursework for this class, but feel lost doing the capstone at the moment.
Hello, I use a non licenced Excell 2010 and I don't have the "get data from folder" choice .Because there is a limit of 1048576 rows I can't download all 12 months csv in the same spreadsheet, even if I've merged them into one file. Can you help me with this? Do I have to pay for Excell to get more options? Thank you.
Well the best answer is buy a copy of Excel 2016 or later. Apparently but can install Power Query as a add-on on 2010. Also look at my videos, where I had a databases.
Hi sir, So I can’t get power query on my MacBook Pro because it’s an old version of mac. Can process all this data on google sheets instead? Thank you!
Hi! Thank you for the amazing help! I'm using Mac, I'm trying to to close&load to a pivot table but do not have the option and just clicking close&load seems to be unable to load. Do you know any way around this?
Thanks for sharing this - I didn't know you could do so much within powerquery! I have a Q about the nulls or 0 values in the data, let's just say hypothetically the mgr doesn't know why these 0s exist or some values are null, is it good practice to delete these anomalies from the data?
I'm just starting my capstone project and have attempted to import all the .csv files just as you did in this video, but my "get data" menu does not have a "from file" < "from folder" option available. There is only a "get data (power query)" option, along with a few others: "from HTML", "from text (legacy)", "from sql server", and "from database". When I click on "get data(power query)", it takes me to two more options: "import data from a MS Excel workbook" or " import data from a text or csv file." When I choose "import data from a text or csv file" it doesn't allow me to choose the correct folder. Instead, it opens the folder and only allows me to choose 1 of the 12 csv files. How do I import all these files into one excel sheet in the newer version of excel?? Sorry for the wordy question. I just wanted to be thorough in my explanation. I'll appreciate any help you can give me.
@@Fadein1980 You don't !! you use Power Query to import, combine, clean, and store the data to store the data. Note: PQ doesn't store the data in the spreadsheet.
Hello thanks for your time and effort put into making this informative video. This explanation helped me a lot, I was wondering if there is a part 2 to this video? And if so can you provide me with the name/link to it please. Thanx again I really appreciate it.
@@CaribouDataScience Now I get, i tried finding the options on my device but couldn't spot one, just went through the comment and saw the video link you dropped. Thanks so much Sir, I really do appreciate your work here
I am struggling to add days of the week. It keeps giving me days of the week from 0-6 instead of 1-7. Using power query. Any suggestions as to how I can do this?
Here is a video that shows how to what you are looking for . the Power Query show work the same for both PBI and excel: ua-cam.com/video/QFADxSyXwwY/v-deo.html
Very helpful! I'm just getting started. Can this be done with Google Sheets? So far it is taking forever to load files. Struggling to get the first two months uploaded. Thanks
Be sure and click the "Bell" icon so that will be notified whenever I go live or upload new content. I don't think sheets has a way to import and combine 12 csv files. Remember you can use what ever you want to use for the project i.e. Power BI, R, Python or Tableau. Also if you look at the Capstone Playlist you well see other methods beside just Excel.
@@CaribouDataScience Thanks! I'm aware. I was trying with R but it's a bigger learning curve, although it's more efficient. I'm also trying with Numbers on Mac and surprsingly the files are uploading but it is taking time. I will research a bit more! I appreciate your help!!
yah, I'm really curious about this as well! when working with the pivot tables, when i place the "start_date" into the rows section, I get completely different output@@CaribouDataScience
Hello again, I'm having trouble to restore the rows I want to keep after deleting the ones with 0 in trip_duration. Can you please help me? I'de also like to ask " do we work different when using Excell and Excell with Power Query? Thank you in advance.
@@naveenvasanth9306 Be sure and click both the "Like" and "Bell" icons, that way you will be notified whern ever I go like or upload new content. www.kaggle.com/datasets/evangower/cyclistic-bike-share.
@@CaribouDataScience Having errors after the abs function. I've gone back to watch the video over and over, can't seem to figure out where the error is coming from.
Rather than import all 4M rows, use the group_by function under Transform, create summary tables, and then use them for your connections. BTW, I will be doing a live stream this evening at 8:00 PM/EDT. Click on the "Bell" Icon and you will notified when I go live.
The most practical steps I saw for this case study in UA-cam! Thank you, sir.
Thanks, I will be live streaming at 9:00 PM EDT this evening.
so incredibly helpful! honestly, it wasn't even so much the step by step that helped, because of course there are multiple way to come up with your own conclusions, but what helped me was even when you brought up the missing values in a column, or the "0 and less than or equal to 0". It really helped me think through how i could handle and clean my data in the future, and why i would look for such answers in the first place! very insightful, so thank you for that :)
Thanks for the kind words. Besure and Click , Subscribe, Like and Bell icons, and of course share the channel with friends who are interested in data analytics.
Thank you so much for your help and taking the time to create videos like this! We appreciate you!
You welcome. Stop by my stream tonight at 9:00 PM. EDT
This made a lot of sense to me. Thank you sir
I appreciate this a ton, for the trip durations with negatives, i figured out the issue was midnight, so i'm going to try and take the number + 60 and it should give the proper trip times.
You welcome. Be sure and click the "bell" icon so that you will notified when ever I got live.
I continue to go through this data, handing it in different ways such as SQL, Excel and R. While job hunting my R ability is limited at best, but hopefully it will make sense eventually. Thanks so much for doing so much work to show multiple ways to reach the end goal.
From what I have seen on UA-cam and other sources when starting out as an "entry-level" Data Anlytist you start working with SQL, Power BI (or Tabluea ), and Excel.
There are about 100,000 rows missing start station names and numbers.
Sir Thank you so much for sharing this video🙏 this is my first time learning, I have never built any portfolio before and I feel clueless about how to start. Until I found your video! Really helpful and I can learn how to clean real data.
From this video, I know many tips and I can improve my skills as well. The next things I want to improve are my SQL, python, and R programming.
Thanks, for the kind words. I am glad the video was helpful. ChatGPT is what I use to write Python, and site SQL problems.
Thank you sir, it is really insightful.
this video has been extremely useful to me and liked how you talked about the missing values and duration less than 0, someone else here has said its because of midnight, it made me think how to think about cleanly data
I am glad it was a help.
Thank you very much sir. We do appreciate you!
Wow, this is immensely helpful. Thank you for an advance walkthrough of how to handle these data sets. I have completed all the coursework for this class, but feel lost doing the capstone at the moment.
Thanks for the kind words. If you have any suggestions for content drop them in the comments. Also Besure and click, subscribe, like she Bell icons.
As a fresher who wants to enter Data Analytics field, your videos are very much helpful sir! Thank you so much SIr!!!
You are welcome.
thanks a lot for this video, this really helps me a lot
When I click sort ascending, power query returns thousands and thousands of null rows, what should I do if this is happening?
Thank you so much for this go-through! I was lost
I am glad it helped! Besure and click the 🔔 icon either you will be notified whenever I upload new content.
wow this is super helpful sir. thank you
You are welcome.
Thank you sooooo much, this was soooo helpful
You are welcome. BTW I will be doing a live stream on UA-cam @ 8:00 PM/EDS:
ua-cam.com/video/qJWNxnnfSfM/v-deo.html
Hello, I use a non licenced Excell 2010 and I don't have the "get data from folder" choice .Because there is a limit of 1048576 rows I can't download all 12 months csv in the same spreadsheet, even if I've merged them into one file. Can you help me with this? Do I have to pay for Excell to get more options? Thank you.
Well the best answer is buy a copy of Excel 2016 or later. Apparently but can install Power Query as a add-on on 2010. Also look at my videos, where I had a databases.
Hi sir,
So I can’t get power query on my MacBook Pro because it’s an old version of mac. Can process all this data on google sheets instead? Thank you!
ua-cam.com/video/NtLd6zoUSic/v-deo.html
Hi! Thank you for the amazing help! I'm using Mac, I'm trying to to close&load to a pivot table but do not have the option and just clicking close&load seems to be unable to load. Do you know any way around this?
Do you mean in Power Query?
Thanks for sharing this - I didn't know you could do so much within powerquery!
I have a Q about the nulls or 0 values in the data, let's just say hypothetically the mgr doesn't know why these 0s exist or some values are null, is it good practice to delete these anomalies from the data?
It could depend on how many rows there are.
I'm just starting my capstone project and have attempted to import all the .csv files just as you did in this video, but my "get data" menu does not have a "from file" < "from folder" option available. There is only a "get data (power query)" option, along with a few others: "from HTML", "from text (legacy)", "from sql server", and "from database". When I click on "get data(power query)", it takes me to two more options: "import data from a MS Excel workbook" or " import data from a text or csv file." When I choose "import data from a text or csv file" it doesn't allow me to choose the correct folder. Instead, it opens the folder and only allows me to choose 1 of the 12 csv files. How do I import all these files into one excel sheet in the newer version of excel?? Sorry for the wordy question. I just wanted to be thorough in my explanation. I'll appreciate any help you can give me.
What version of Excel?
@@CaribouDataScience 16.67
also how did you fit all these rows into one sheet? The total rows exceeds limit that excel allows.
@@Fadein1980 You don't !! you use Power Query to import, combine, clean, and store the data to store the data. Note: PQ doesn't store the data in the spreadsheet.
@@CaribouDataScience so there’s no way for me to accomplish this the way you do in this video?
Thankyou Sir
Hello thanks for your time and effort put into making this informative video. This explanation helped me a lot, I was wondering if there is a part 2 to this video? And if so can you provide me with the name/link to it please. Thanx again I really appreciate it.
I didn't make a part 2, but there should be some more Excel videos in the playlist.
Hello!, Just a doubt Did you remove the missing Station Name values?
It is you project, you can do what you want. You could also create a new station_name for the missing values.
Excel/Power Query: Cyclistic Bikeshare
Awesome work Sir.
Did you really merge the 12 documents together?
Tried doing it severally but didn't see the options on my excel
Of course I did. It depends on the version of Excel you are using.
@@CaribouDataScience
Now I get, i tried finding the options on my device but couldn't spot one, just went through the comment and saw the video link you dropped.
Thanks so much Sir, I really do appreciate your work here
I am struggling to add days of the week. It keeps giving me days of the week from 0-6 instead of 1-7. Using power query. Any suggestions as to how I can do this?
Here is a video that shows how to what you are looking for . the Power Query show work the same for both PBI and excel:
ua-cam.com/video/QFADxSyXwwY/v-deo.html
Very helpful! I'm just getting started.
Can this be done with Google Sheets?
So far it is taking forever to load files. Struggling to get the first two months uploaded.
Thanks
Be sure and click the "Bell" icon so that will be notified whenever I go live or upload new content.
I don't think sheets has a way to import and combine 12 csv files. Remember you can use what ever you want to use for the project i.e. Power BI, R, Python or Tableau. Also if you look at the Capstone Playlist you well see other methods beside just Excel.
@@CaribouDataScience Thanks! I'm aware. I was trying with R but it's a bigger learning curve, although it's more efficient.
I'm also trying with Numbers on Mac and surprsingly the files are uploading but it is taking time.
I will research a bit more!
I appreciate your help!!
Take a look at the videos where I use SQLite and Excel.
Thank you!
BTW I leave stream Mondy - Thursday @ 8:00 PM/EST , click the "bell" icon and you will be notified when ever i go live.
I'm just starting on this project. I know there are a lot of data for this project and you mentioned 12. Which 12 did you use or combine?
Did I respond to your question?
yah, I'm really curious about this as well! when working with the pivot tables, when i place the "start_date" into the rows section, I get completely different output@@CaribouDataScience
@@Alex.In_Wonderland, Do you mean that the dates are different? What are you putting in the columns?
Can't seem to understand how you got rid of the values that are equal to zero. I need help.
Actually you want to keep the rows. In the drop down list select "greater than" and enter 0.
Hello, do you have a video where you can combine the 12 excel files into one csv file using excel? Thank you!
I don't think there is a way. You could do it using R or Python/Pandas is you best bet.
BTW, I will be a live stream this evening at 8:00PM /EST (New York time) here is the link for that:
ua-cam.com/video/sTcF4qAi-x8/v-deo.html
@@CaribouDataScience do you have a video on how to do this in R?
@@DarkMerchant2222 Yes, it is part of the Capstone playlist:
ua-cam.com/play/PLnBliEe9L853Rrts3QKXzf-RL49uuTa57.html
Hello again,
I'm having trouble to restore the rows I want to keep after deleting the ones with 0 in trip_duration.
Can you please help me? I'de also like to ask " do we work different when using Excell and Excell with Power Query?
Thank you in advance.
Are using Power Query?
@@CaribouDataScience YES
How can I load, I'm using a mac and it doesn't gives me the option of from folder?
Do you have Power Query ? If you do try importing them one at a time and then do a append query.
@@CaribouDataScience So,I'll import one by one and then how do I combine all of it?
@@chalotheekhai8481 , Stop by my live stream I will be glad to help you.
ua-cam.com/video/sTcF4qAi-x8/v-deo.html
@@CaribouDataScience Roger that, Thank You
@@chalotheekhai8481 Here is link that should work: ua-cam.com/video/ui-tjBcANRU/v-deo.html
Excel/Power Query: Cyclistic Bikeshare (v2A) google capstone
sir i have excel 2016. will this work.
I think so. Just follow the instructions.
why did you remove the ID column? why we did not just ignore it?
Hey, it your project. You can do want you want.
can anyone say where I can download the bike data ?
Kaggle
Hi sir! When i click the download link they provide it shows bunch of files. What i have to do? Should i download all files ?
Are you getting the data from Kaggle?
@@CaribouDataScience no sir, they provide some links. May i know where you get those datas and how should i get it too?
@@naveenvasanth9306 Be sure and click both the "Like" and "Bell" icons, that way you will be notified whern ever I go like or upload new content. www.kaggle.com/datasets/evangower/cyclistic-bike-share.
Hi sir, where I can download the data to practice?
Kaggle
how can i get and compine the data ?
You can find the data on Kaggle. The video explains his how combine the files .
hello sir i tried the abs function but it was showing errors
Try runnung it wituout abs .
Go back and check all the steps.
@@CaribouDataScience Having errors after the abs function. I've gone back to watch the video over and over, can't seem to figure out where the error is coming from.
I followed this process but I couldn’t save my work
Could you help pls thanks
What happens when you try and save it?
Stop by my stream this evening and will see if I can help.
Using excel other than365 anyone have completed please Guide me to complete this case study
What version of Excel are you using?
Better Call Saul brought me here
Sorry, did you have a question?
hello sir, can u tell me first step like files naming and file sticking
I am not sure what you are asking? But start by creating a directory and putting all the data files in it. Then follow the steps in the video.
@@CaribouDataScience which video is first for capstone project sir
@@ducker3342 of you are using Excel this is the one.
@@CaribouDataScience what is next sir?
@@ducker3342 ua-cam.com/play/PLnBliEe9L8523SJqJE_E6PkDFpD701ZO0.html
google data analytics capstone complete a case study
Yep, Besure and check out the rest of the videos in the playlist.
@@CaribouDataScience thanks sir .. perfect work
When I try to load it as a connection only. The data source on the workbook continues to load and never finds the source. How do I fix this?
Rather than import all 4M rows, use the group_by function under Transform, create summary tables, and then use them for your connections.
BTW, I will be doing a live stream this evening at 8:00 PM/EDT. Click on the "Bell" Icon and you will notified when I go live.