For the termdate column error, run the following 4 queries and it'll work: UPDATE hr SET termdate = IF(termdate IS NOT NULL AND termdate != '', date(str_to_date(termdate, '%Y-%m-%d %H:%i:%s UTC')), '0000-00-00') WHERE true; SELECT termdate from hr; SET sql_mode = 'ALLOW_INVALID_DATES'; ALTER TABLE hr MODIFY COLUMN termdate DATE;
@@herdataproject Wonderful news, I can't wait to follow them, I am currently doing an SQL course, as soon as I am done, I want to replicate this project to improve my skills.
You made it already!! Thank you so much!!! Extremely helpful!!! Keep up the videos plz your projects are unbelievably helpful for me right now as I’m work my on building a strong portfolio, I am sure you are helping many others also!
The dataset can be downloaded from Telegram or GitHub. On Telegram check the pinned message Telegram - t.me/+7ms4DzzAYVlhYmZk GitHub - github.com/Irene-arch/HR-Dashboard-MySQL-PowerBI Please leave any questions or requests in the comments
Thanks, Irene I was able to go through the tutorial and actually practice on it and I can say it is absolutely helpful to me as a beginner in the data industry. Thumbs up and keep up inspiring!! love from Bangladesh
To update age = '2060-01-01' AND birthdate < '2070-01-01'; The reason age is showing in negative is instead of 1960 it is mentioned as 2060 , so you can use DATE_SUB to subtract 100 years. Hope this helps thanks !
@gnardi6928 i think its due to the time youre doing the project unless you got an error during import. Since we’re using current date it is different from the one I used when recording the video
Q6. How does the gender distribution vary across departments and JOB TITLES? 43:18 I think you ignored the "job title' part of this question. This was what I did. SELECT * FROM hr; SELECT department, jobtitle, gender, count(*) count FROM hr WHERE age >= 18 AND termdate = '0000-00-00' GROUP BY department, jobtitle, gender ORDER BY department;
Heeeey, yes I did. I think it was coz of how varied they were so decided to just go with departments to have a general overview. Thanks for sharing your solution and keep up the amazing work!!
I want to say thank you for this video! It made it using Microsoft SQL server much more understandable because I understand that it is more advanced but using my SQL workbench requires a more hands-on approach to learning the basics. You showed how to manually change things in the best way possible! Also, I will support any future videos !! Keep up the good work!
@@herdataproject Thank you for much for always ..However I got stuck trying to answer the first question ...It keeps returning an error message 'Incorrect DATE value:'0000-00-00'.. Here is my line of code ; Select gender, count(*) AS gender_count FROM hr WHERE age >= 18 AND termdate = '0000-00-00' GROUP BY gender; ..Please where am I getting it wrong ?
It's awesome!! I've watched n number of RUclips videos but i found this video is very useful especially at data cleaning and data analysis part as its crystal clear in terms of better understanding... Thanks so much ❤👍🤝.
Thank you so much for this wonderful tutorial, you are so calm in teaching and it makes it very understanding. Please can you do more videos on SQL+Power Bi. I just started my learning journey as a Data Analyst. Please can you Mentor me
Kudos, Great video! I thoroughly enjoyed it and found the SQL Data Analysis walkthrough to be quite accessible, especially on date formatting and conversion 👏🏽. However, for enhanced practicality, consider leveraging Power BI's DAX to calculate most measures after establishing a data model. This approach enhances interactivity and reusability in any project. Looking forward to more content like this! 👏🏽👋🏽💯
Thanks for the insights. I'm glad you found the video helpful. I'll be sure to show how to use DAX in the next PowerBI project. Stay tuned and thanks for watching!!
PS - I was having issues with obtaining the data set, when I open the file that you've suggested excel doesn't read it as a table and there is an error report. I was able to find the same data set on kaggle (the data set did not include the negative age for one of the employees as shown in your video) and I was able to follow along thereafter.
You're most welcome. I'm glad you found the video helpful. Sorry for the hustle in getting the dataset. It's on Github and Telegram. So once you join Telegram, check the pinned messages and you'll be able to get the dataset
SELECT birthdate FROM hr; SET sql_safe_updates = 0; UPDATE hr SET birthdate = CASE WHEN birthdate LIKE '%/%' THEN date_format(Str_to_date(birthdate,'%m/%d/%y'),'%Y-%m-%d') WHEN birthdate LIKE '%-%' THEN date_format(Str_to_date(birthdate,'%m-%d-%y'),'%Y-%m-%d') ELSE NULL END; 03:00:08 UPDATE hr SET birthdate = CASE WHEN birthdate LIKE '%/%' THEN date_format(Str_to_date(birthdate,'%m/%d/%y'),'%Y-%m-%d') WHEN birthdate LIKE '%-%' THEN date_format(Str_to_date(birthdate,'%m-%d-%y'),'%Y-%m-%d') ELSE NULL END Error Code: 1292. Truncated incorrect date value: '06/04/1991' 0.000 sec please could you help me with this
First import the dataset again because the values have already become NULL and then run SET SQL_MODE = ' '; then run the update query. Hopefully it works
Hello, it's okay if they remain blank. Mine are updating to '0000-00-00' because strict mode in my version of SQL has been disabled. Yours is not that's why they're not updating. The only thing you'll change in your queries while answering the questions is just writing 'termdate IS NULL' instead of 'termdate = '0000-00-00' But if you'd also like to disable strict mode on your version of SQL you can run this query SET SQL_MODE = ' '; Then run your queries. Let me know if it works!
@@herdataproject thank you soooo much mam for relying I will try it .. Thank you for being sooo kind 🙏🏻🙏🏻🙏🏻🙏🏻🙏🏻.. thank you for giving your valuable time. 🙏🏻🙏🏻🙏🏻
You're welcome. I'm not yet an expert but to be good you need to learn and always practise what you've learnt so that you don't forget. That way you build knowledge on a lot of concepts.
Heloo Mam, why we need to analyse these data in sql instead of excel? we didn't connect that sql analysis in power bi then why we need to find that analyse in sql just simply use one tool like power bi and easy find that analyse in power bi .what the use in that analyse in sql? I don't know why please leave the answer mam🙏
It's just a preference. There are people who had requested to be shown how to clean and analyse data using SQL. You can use Excel or PowerBI from start to finish if that's what you prefer.
Thank you for this video. It was really easy to follow and understand for me. And when I got stuck with a sql queries related with termdate, I could find solution in the comment as well..
why did you past all the queries in diff excel files & then imported in power bi, instead of you can directly connect to power bi using import mode....
Hi, thank you for helping me learn with this video, but I had a doubt, some of the answers I get are slightly different from yours for example I got slightly different values when I calculated the different age groups, could you please tell me why is that?
Bro please tell me update command not working in my SQL workbench since last 24 hours😭😭😭 I tried again and again Pls tell me where is fault, chatGPT speaks I apologise too
Hello first and foremost thank you Irene for showing this project,as i was going through it using sql server microsoft i am stuck on changing the termdate data type anyone using ssms how did you go about it,i will appreciate for any help
This is what exactly I have been looking for. We'll made. Thanks ++. As I am just learning sql any suggestions on a good book to learn instead of only videos.
Hi there, I am getting an error on question 5. This is my code: SELECT AVG(DATEDIFF(termdate, hire_date))/365 AS average_length_of_employment FROM hr WHERE termdate =18; This is the error I am receiving: Error Code: 1525. Incorrect DATE value: '0000-00-00'. Any assistance would be appreciated. Thanx
17:19:17 UPDATE hr SET birthdate=CASE WHEN birthdate LIKE'%/%'THEN date_format(str_to_date(birthdate,'%m/%d/%Y'),'%Y-%m-%d') WHEN birthdate LIKE'%-%'THEN date_format(str_to_date(birthdate,'%m-%d-%Y'),'%Y-%m-%d') ELSE NULL END Error Code: 1411. Incorrect datetime value: '2012-01-01' for function str_to_date 0.012 sec Could you help me with this?
Hello, I skipped the data modelling section because the data was already cleaned in SQL and didn't require advanced transformations like creating measures or calculated columns. But you could do the modelling if you're looking to make you report interactive
Hello 👋 ! I was trying this in Power Query Editor for the data cleaning part for hire_date col. How can the null values be dealt with in this way, can you please help😅
Hello, try this. Hard to explain via text but hope it works Select the table or range that contains the column with null values. Go to the "Data" tab. Click on "From Table/Range" to open the Power Query Editor. In the Power Query Editor, find and select the column with null values in the "Query Settings" pane on the right. Go to the "Transform" tab in the Power Query Editor ribbon. Click on "Replace Values" from the "Any Column" group. In the "Replace Values" dialog box, enter "null" (without quotes) in the "Value to Find" box. Leave the "Replace With" box empty to remove null values from the column. Click "OK" to apply the changes. Once you've made all necessary transformations, click "Close & Apply"
HEllo, for the termdate column, I don't have the 0000-00-00 when the line is empty, I have nothing so I have an error when I want to convert the type text to a date.
There's another alternative you can use to convert the values from text to date UPDATE hr SET termdate = IF(termdate = '', NULL, str_to_date(termdate, "%Y-%m-%d %H:%i:%s UTC")) WHERE termdate IS NOT NULL; And in place of termdate = '0000-00-00 ' use IS NULL. Let me know if it works for you
This is great, just a question, is the dataset you provided via GitHub the exact same as the one you used in the video? I noticed the values I'm getting in my analysis as I follow along are slightly different from yours, I'm worried i might be making mistakes in my queries.. I'm using SQL server by the way, the differences are minor but they do affect the results of the analysis for example for you, the lowest turnover rate was the marketing department and mine was the business development department.
Hi, is this the correct way of utilizing databases for Power BI Visualizations? I mean we need to export the details as csv file then import it to Power BI? Or can we directly connect to database to get the data? Thanks
I couldnt update termdate using PLSQL developer. STR_TO_DATE is not working. I tried using TO_CHAR(TERMDATE,'DD/MM/YYYY') , trunc(TERMDATE).. nothing works out. Datatype is Varchar 26. Plz help.
I fixed it.. following syntax works out.. UPDATE tablename SET TERMDATE=cast(to_timestamp_tz(TERMDATE, 'yyyy-mm-dd hh24:mi:ss "UTC"') as date) WHERE TERMDATE IS NOT NULL;
i get trouble with this query, the output is error : SELECT GENDER, count(*) AS COUNT FROM HR WHERE AGE >= 18 AND TERMDATE = IS NOT NULL group by GENDER; because some part in termdate table is null? how can fix it?
Hi the link works. You can try it again here t.me/+7ms4DzzAYVlhYmZk If it doesn't work, try downloading from Github github.com/Irene-arch/HR-Dashboard-MySQL-PowerBI
The assumption is that termdate is the date the employment ended. So it wouldn't make sense to have it as a future date. That's why I made sure to use the termdate as less than more equal to the current date. Hope this helps!
when we are considering the term date 00000 to select the employees, what about the employees whose term date is in future?that means they are still in the company and we are not considering them
thanks for the video, very well explained, just one question if the original CSV file that you initially imported to SQL suffers any change lets say gets more rows added whitin the already existing categories would that get reflected on the MySQL values as well?
Hello , UPDATE hr SET termdate = date(str_to_date(termdate, '%Y-%m-%d %H:%i:%s UTC')) WHERE termdate IS NOT NULL AND termdate != ' '; - This part is throwing error on mysql. Below is the response error- Response- Error Code: 1411. Incorrect datetime value: '' for function str_to_date
So the error message suggests that you have an empty string ('') in your termdate column, which cannot be converted to a datetime value using the str_to_date() function. To fix this, you can add an additional condition in your WHERE clause to exclude empty strings: So add this condition as well to just take care of any kind of empty string in the column UPDATE hr SET termdate = date(str_to_date(termdate, '%Y-%m-%d %H:%i:%s UTC')) WHERE termdate IS NOT NULL AND termdate != ' ' AND termdate != '';
UPDATE hr SET termdate = IF(termdate IS NOT NULL AND termdate != '', date(str_to_date(termdate, '%Y-%m-%d %H:%i:%s UTC')), '0000-00-00') WHERE true; this will fix it
Please i have a question... When i query for "what is the gender breakdown of employee in the company" and SQL returns the result table.....is there a way to save the result so that when I connect powerBI to the SQL database I will have access to the result table from the query rather than exporting each result as a CSV because imagine you are working with a large company and the result table from your query is 3million rows I don't think I will be able to export that into excel easily? Do you get me?
Yes I get you. I think what you're looking for is something like a database view. Create a database view with the SQL query logic. Power BI can then directly connect to this view and retrieve data without running the query every time. I have never tried to use it before with PowerBI or even Tableau but I think it would work. You can try it out and let me know.
At @28.34 im getting this error select gender,count(*) as count from hr where age >= 18 and termdate = '0000-00-00' group by gender LIMIT 0, 5000 Error Code: 1525. Incorrect DATE value: '0000-00-00' 0.00037 sec
Hello, how to solve the below problem in Microsoft sql UPDATE HR SET termdate = CONVERT(date, termdate, 120) WHERE termdate IS NOT NULL AND termdate != ' '; Error - Conversion failed when converting date and/or time from character string.
you can modify the UPDATE statement to exclude the problematic rows that cannot be converted to a date. UPDATE HR SET termdate = CONVERT(date, termdate, 120) WHERE termdate IS NOT NULL AND termdate != ' ' AND ISDATE(termdate) = 1; The additional condition ISDATE(termdate) = 1 is added to ensure that only valid date values are considered for conversion. The ISDATE function checks if the value can be converted to a valid date, and only rows where the 'termdate' value is a valid date will be updated. Hope this helps
Using Microsoft sql and there is no str_to_date function, tried changing termdate data type but was not able to, tried convert, cast but it's not working, please help
In Microsoft SQL Server, you can use the CONVERT() function to convert a string to a date. The format string to use with CONVERT() is different from the one used with STR_TO_DATE() in MySQL. UPDATE hr SET termdate = CONVERT(DATETIME, termdate, 120) WHERE termdate IS NOT NULL AND termdate != ' ' In this example, the CONVERT() function is used to convert the termdate string to a datetime data type using the format code 120, which corresponds to the ISO 8601 format (yyyy-mm-dd hh:mi:ss(24h)). Something to keep in mind is that the CONVERT() function can be affected by the default language setting of the SQL Server instance. If you're experiencing issues with the conversion, you can try using the SET LANGUAGE statement to set the language explicitly before running the UPDATE statement: SET LANGUAGE us_english UPDATE hr SET termdate = CONVERT(DATETIME, termdate, 120) WHERE termdate IS NOT NULL AND termdate != ' ' I hope this helps : )
Irene, First I tried to download CSV, it became a text file, when I changed the file to CSV and imported to SQL, the data is not clear and has errors, please tell me step by step how to download the file from your GITHUB, and how to open in MySQL
You can also download it from the telegram if you're able to install the app. I think it should download as a CSV file. Once you join the group it'll be pinned at the top
I think you made a mistake in minute 33:17, it should be THEN '45-54' and not 44-54 and you didn't correct it, but I think everything else is fine, thanks for the video.
Depends on the column you're referring to. For dates etc, you're running the code on a different date than I did. If that's not the case then please share a screenshot in the Telegram group to allow me to assist you further
can anyone explain the aim of this project mean why we make dishbord and i cant understand dishbord mean wht is the problem behind this that we make this typle dishbord
They don't have to update to 0000-00-00. It all depends with the settings of your MySQL. If yours have remained as NULL it's okay. When writing the queries use WHERE termdate IS NULL instead of WHERE termdate = '0000-00-00'
Hi, for min and max ages did anyone not get negative values? My result doesn't show -42. It shows minimum value as 20. I tried copy pasting the query from github too. Same result. Would really appreciate any help:) Thank you for the video .
The dataset is a csv. If you're getting it from GitHub click on the file and after loading there's a download icon on the right side. If unsuccessful download from the Telegram group. It will be on the pinned messages
U did nt show sql query answer in power bi dashboard that we want to see for example u find total order in sql then u should show total order in power bi dashboard i want to see this that when u find any sql query answer as i mention total order in above then show that answer in power bi dashboard pls tell about this how will do this
When creating a dashboard it's not necessary to showcase everything just the important metrics. But if you want to visualize the result of every query just save the query results as csv file and import into PowerBI
hello, the videos is very helpful but i need some help, i dont know why my location_state column name become location_state; that contain semi colon,how do i remove that semi colon from my table name?the value is also same,from Ohio to Ohio; thanks before
UPDATE hr SET birthdate = CASE WHEN birthdate LIKE'%/%'THEN date_format(str_to_date(birthdate, '%m/%d/%Y'),'%Y-%m-%d') WHEN birthdate LIKE'%-%'THEN date_format(str_to_date(birthdate, '%m-%d-%Y'),'%Y-%m-%d') ELSE NULL END; I have been receiving an error with this. It says incorrect datetime value 2012-01-01 for function str_to date
@@herdataproject nah it is still the same. I am just wondering if I could show it to you via zoom or google video.. The error is still the same. It says Incorrect datetime value: '2012-01-01' for function str_to_date
For the termdate column error, run the following 4 queries and it'll work:
UPDATE hr
SET termdate = IF(termdate IS NOT NULL AND termdate != '', date(str_to_date(termdate, '%Y-%m-%d %H:%i:%s UTC')), '0000-00-00')
WHERE true;
SELECT termdate from hr;
SET sql_mode = 'ALLOW_INVALID_DATES';
ALTER TABLE hr
MODIFY COLUMN termdate DATE;
It worked , Thanks 🤝
Nice, thanks!
I stucked at this error and just found your precious comment, Thank you
thanks man
Can this work in Microsoft SQL server management studio pls?
This one is my first power bi report and this report will remain special forever in my life.
Irene, amazing work, quite impressive, thank you for supporting the community with your knowledge
Thanks Simon. More projects coming soon
@@herdataproject Wonderful news, I can't wait to follow them, I am currently doing an SQL course, as soon as I am done, I want to replicate this project to improve my skills.
You made it already!! Thank you so much!!! Extremely helpful!!! Keep up the videos plz your projects are unbelievably helpful for me right now as I’m work my on building a strong portfolio, I am sure you are helping many others also!
You're welcome. More videos coming soon. Thanks for watching!
The dataset can be downloaded from Telegram or GitHub.
On Telegram check the pinned message
Telegram - t.me/+7ms4DzzAYVlhYmZk
GitHub - github.com/Irene-arch/HR-Dashboard-MySQL-PowerBI
Please leave any questions or requests in the comments
Bhai data file nahi download ho raha hai
Thanks, Irene I was able to go through the tutorial and actually practice on it and I can say it is absolutely helpful to me as a beginner in the data industry. Thumbs up and keep up inspiring!! love from Bangladesh
To update age = '2060-01-01' AND birthdate < '2070-01-01';
The reason age is showing in negative is instead of 1960 it is mentioned as 2060 , so you can use DATE_SUB to subtract 100 years.
Hope this helps thanks !
Thank you!
thanks mate ... very helpful...
My youngest age is 22 , would this be because of insufficient data from import ?
@gnardi6928 i think its due to the time youre doing the project unless you got an error during import. Since we’re using current date it is different from the one I used when recording the video
Q6. How does the gender distribution vary across departments and JOB TITLES?
43:18
I think you ignored the "job title' part of this question. This was what I did.
SELECT * FROM hr;
SELECT department, jobtitle, gender, count(*) count
FROM hr
WHERE age >= 18 AND termdate = '0000-00-00'
GROUP BY department, jobtitle, gender
ORDER BY department;
Heeeey, yes I did. I think it was coz of how varied they were so decided to just go with departments to have a general overview. Thanks for sharing your solution and keep up the amazing work!!
I love your explanations. Very clear and concise.
I want to say thank you for this video! It made it using Microsoft SQL server much more understandable because I understand that it is more advanced but using my SQL workbench requires a more hands-on approach to learning the basics. You showed how to manually change things in the best way possible! Also, I will support any future videos !! Keep up the good work!
You're most welcome! I really appreciate the feedback and support
@@herdataproject Thank you for much for always ..However I got stuck trying to answer the first question ...It keeps returning an error message 'Incorrect DATE value:'0000-00-00'.. Here is my line of code ; Select gender, count(*) AS gender_count FROM hr WHERE age >= 18 AND termdate = '0000-00-00' GROUP BY gender; ..Please where am I getting it wrong ?
You're welcome and thank you😊
What are the values of your term_date column?
@@herdataproject They are in this format Ma, 0000-00-00, 2014-02-24 etc....
It's awesome!! I've watched n number of RUclips videos but i found this video is very useful especially at data cleaning and data analysis part as its crystal clear in terms of better understanding... Thanks so much ❤👍🤝.
Thank you so much for this wonderful tutorial, you are so calm in teaching and it makes it very understanding. Please can you do more videos on SQL+Power Bi.
I just started my learning journey as a Data Analyst. Please can you Mentor me
Kudos, Great video! I thoroughly enjoyed it and found the SQL Data Analysis walkthrough to be quite accessible, especially on date formatting and conversion 👏🏽. However, for enhanced practicality, consider leveraging Power BI's DAX to calculate most measures after establishing a data model. This approach enhances interactivity and reusability in any project. Looking forward to more content like this! 👏🏽👋🏽💯
Thanks for the insights. I'm glad you found the video helpful. I'll be sure to show how to use DAX in the next PowerBI project. Stay tuned and thanks for watching!!
@@herdataproject; When you begin the Video, what Application or program is that?? Is it Power BI, or SQL.
@anymjohnson1729 it's MySQL Workbench
Thank you for this video, truly. I learned so much from this video.
You’re most welcome!
I’m so happy to hear that
Thank you so much for this project🙏It was very clear and easy to follow😊Plz keep making these type of videos👍
You're most welcome.
There's more to come!
Thanks for watching
Thank you!!!
This helped me soooo much with my own portfolio project.
You're doing great!
PS - I was having issues with obtaining the data set, when I open the file that you've suggested excel doesn't read it as a table and there is an error report. I was able to find the same data set on kaggle (the data set did not include the negative age for one of the employees as shown in your video) and I was able to follow along thereafter.
You're most welcome. I'm glad you found the video helpful. Sorry for the hustle in getting the dataset. It's on Github and Telegram. So once you join Telegram, check the pinned messages and you'll be able to get the dataset
Thanks from Viet Nam. More other videos please. Hope to see them soon.
You're welcome. More to come!
I’m so glad I found your channel! I really like learning through projects, will you be adding on more videos? Thanks so much!
You're welcome. Yes there'll be more videos soon. Stay tuned!
I just bumped into this amazing and well explained tutorial. Thanks so so much...
You're welcome!! Thanks for watching
Thanks for covering various scenarios using sql.. quite helpful
You're welcome. I hope you find the most recent project helpful as well
Please can I see all the data types for your columns when trying to upload the data into table.
I cannot thank you enough for this informative video
SELECT birthdate FROM hr;
SET sql_safe_updates = 0;
UPDATE hr
SET birthdate = CASE
WHEN birthdate LIKE '%/%' THEN date_format(Str_to_date(birthdate,'%m/%d/%y'),'%Y-%m-%d')
WHEN birthdate LIKE '%-%' THEN date_format(Str_to_date(birthdate,'%m-%d-%y'),'%Y-%m-%d')
ELSE NULL
END;
03:00:08 UPDATE hr SET birthdate = CASE WHEN birthdate LIKE '%/%' THEN date_format(Str_to_date(birthdate,'%m/%d/%y'),'%Y-%m-%d') WHEN birthdate LIKE '%-%' THEN date_format(Str_to_date(birthdate,'%m-%d-%y'),'%Y-%m-%d') ELSE NULL END Error Code: 1292. Truncated incorrect date value: '06/04/1991' 0.000 sec
please could you help me with this
First import the dataset again because the values have already become NULL and then run SET SQL_MODE = ' '; then run the update query.
Hopefully it works
@@herdataproject birthdate becomes null
Yes null
@@esthernwaogu592 pls have you been able to solve this issue?
very helpful, thankyou!!! will wait for your next update👍
Thank you, more coming soon!!
Thank you for sharing
19:12
While using the query that you have used, the blank cells are not getting updated with '0000-00-00' ..
What shall I do... Please reply..
Hello, it's okay if they remain blank. Mine are updating to '0000-00-00' because strict mode in my version of SQL has been disabled. Yours is not that's why they're not updating. The only thing you'll change in your queries while answering the questions is just writing 'termdate IS NULL' instead of 'termdate = '0000-00-00'
But if you'd also like to disable strict mode on your version of SQL you can run this query
SET SQL_MODE = ' ';
Then run your queries.
Let me know if it works!
@@herdataproject thank you soooo much mam for relying I will try it
..
Thank you for being sooo kind 🙏🏻🙏🏻🙏🏻🙏🏻🙏🏻.. thank you for giving your valuable time.
🙏🏻🙏🏻🙏🏻
@@herdataproject how you are soo expert in sql queries,, please please give some suggestions to me,, how can I be good SQL queries..
You're welcome. I'm not yet an expert but to be good you need to learn and always practise what you've learnt so that you don't forget. That way you build knowledge on a lot of concepts.
@@herdataproject thank you mam 🙏🏻🙏🏻
thank you so much. I can adapt this project to make my project and learn to familiarize with data cleaning and analysis process.
You're welcome. Yes you can and keep practicing to master all the concepts
Excellent tutorial. Many thanks. Can you please make similar projects using intermediate and advanced SQL concepts?
You're welcome. I will definitely do that
Amazing video! Wow. Wish I wouldve found it sooner. Wouldve saved me so much trouble
Thank you so much!!
13:31
It shows
Error code : 1292. Truncated incorrect date vale : '06-04-1991'
What shall I do
@@herdataproject thank you for responding..
Actually i was
Using small y instead of Y
I saw your coment then i understood..
You're welcome!
Heloo Mam, why we need to analyse these data in sql instead of excel?
we didn't connect that sql analysis in power bi then why we need to find that analyse in sql just simply use one tool like power bi and easy find that analyse in power bi .what the use in that analyse in sql?
I don't know why please leave the answer mam🙏
It's just a preference. There are people who had requested to be shown how to clean and analyse data using SQL. You can use Excel or PowerBI from start to finish if that's what you prefer.
@@herdataproject Thank You very much mam👍Thanks for your reply mam✨
Thank you for this video. It was really easy to follow and understand for me. And when I got stuck with a sql queries related with termdate, I could find solution in the comment as well..
You're most welcome. I'm glad you found it helpful
why did you past all the queries in diff excel files & then imported in power bi, instead of you can directly connect to power bi using import mode....
I'm sure that works too, I think it's a matter of preference
Hi, thank you for helping me learn with this video, but I had a doubt, some of the answers I get are slightly different from yours for example I got slightly different values when I calculated the different age groups, could you please tell me why is that?
I meant for Question 3 in analysis part
27:19 , I am getting count as zero n all results as zero
Hi 1292 truncated date error value…how to fix that sir ..while doing the query changing string to date format
Bro please tell me update command not working in my SQL workbench since last 24 hours😭😭😭 I tried again and again
Pls tell me where is fault, chatGPT speaks I apologise too
Hello first and foremost thank you Irene for showing this project,as i was going through it using sql server microsoft i am stuck on changing the termdate data type anyone using ssms how did you go about it,i will appreciate for any help
This is what exactly I have been looking for. We'll made. Thanks ++. As I am just learning sql any suggestions on a good book to learn instead of only videos.
Thank you for your great tutorial. Keep it up!
Please, how did u get the Total count, Terminated count cause, hires, and termination cause it tells me the column doesn't exist
Those are derived columns from the inner query which will be run first
Clear explanations....thank you so much
You're most welcome!!
When I export to PDF, the page gets zoomed in and the export cuts out a part of the right and bottom of the viz. How do I aviod this please?
Hi, you can try checking your zoom before exporting and also the orientation. It should be landscape instead of portrait
Thank you🌻
You did a very good job. Keep it up.
Thanks Daniel. I'm glad you found it helpful
Hi, How can I apply filter on this csv files imported through sql query? i am unable to do.
wow great stuff so much value
Thank you!
Superb understanding easy way
Hi there, I am getting an error on question 5. This is my code:
SELECT AVG(DATEDIFF(termdate, hire_date))/365 AS average_length_of_employment
FROM hr
WHERE termdate =18;
This is the error I am receiving: Error Code: 1525. Incorrect DATE value: '0000-00-00'.
Any assistance would be appreciated. Thanx
You should run the set sql_mode query first to sort this error
17:19:17 UPDATE hr SET birthdate=CASE WHEN birthdate LIKE'%/%'THEN date_format(str_to_date(birthdate,'%m/%d/%Y'),'%Y-%m-%d') WHEN birthdate LIKE'%-%'THEN date_format(str_to_date(birthdate,'%m-%d-%Y'),'%Y-%m-%d') ELSE NULL
END
Error Code: 1411. Incorrect datetime value: '2012-01-01' for function str_to_date 0.012 sec
Could you help me with this?
Hello use UPDATE IGNORE hr
Remove or comment ELSE from query and then try
hope this will work
Great Work🙌
You are doing it great, keep going,,
Thank you!!
i am not able
import file into postgre sql it showing hiredate is out of range
Thank you for the video. So you prefer skipping the data modeling and DAX at all?
Hello, I skipped the data modelling section because the data was already cleaned in SQL and didn't require advanced transformations like creating measures or calculated columns. But you could do the modelling if you're looking to make you report interactive
Thank you for such an amazing video full of information and knowledge 😊👍
You're most welcome!
More to come
@@herdataproject waiting for next
Hello 👋 ! I was trying this in Power Query Editor for the data cleaning part for hire_date col. How can the null values be dealt with in this way, can you please help😅
Hello, try this. Hard to explain via text but hope it works
Select the table or range that contains the column with null values.
Go to the "Data" tab.
Click on "From Table/Range" to open the Power Query Editor.
In the Power Query Editor, find and select the column with null values in the "Query Settings" pane on the right.
Go to the "Transform" tab in the Power Query Editor ribbon.
Click on "Replace Values" from the "Any Column" group.
In the "Replace Values" dialog box, enter "null" (without quotes) in the "Value to Find" box.
Leave the "Replace With" box empty to remove null values from the column.
Click "OK" to apply the changes.
Once you've made all necessary transformations, click "Close & Apply"
HEllo, for the termdate column, I don't have the 0000-00-00 when the line is empty, I have nothing so I have an error when I want to convert the type text to a date.
There's another alternative you can use to convert the values from text to date
UPDATE hr SET termdate = IF(termdate = '', NULL, str_to_date(termdate, "%Y-%m-%d %H:%i:%s UTC")) WHERE termdate IS NOT NULL;
And in place of termdate = '0000-00-00 ' use IS NULL.
Let me know if it works for you
Big thanks to you!!!
This is great, just a question, is the dataset you provided via GitHub the exact same as the one you used in the video? I noticed the values I'm getting in my analysis as I follow along are slightly different from yours, I'm worried i might be making mistakes in my queries.. I'm using SQL server by the way, the differences are minor but they do affect the results of the analysis for example for you, the lowest turnover rate was the marketing department and mine was the business development department.
I’ve got the same problem as you😅
Hi, is this the correct way of utilizing databases for Power BI Visualizations? I mean we need to export the details as csv file then import it to Power BI? Or can we directly connect to database to get the data? Thanks
Hi, it's not the only way. You can also connect directly to your SQL database. Check Microsoft Learn website for more details.
I couldnt update termdate using PLSQL developer. STR_TO_DATE is not working. I tried using TO_CHAR(TERMDATE,'DD/MM/YYYY') , trunc(TERMDATE).. nothing works out. Datatype is Varchar 26. Plz help.
I fixed it.. following syntax works out..
UPDATE tablename
SET TERMDATE=cast(to_timestamp_tz(TERMDATE, 'yyyy-mm-dd hh24:mi:ss "UTC"')
as date)
WHERE TERMDATE IS NOT NULL;
Thank you so much for this. It will be very helpful for many other people using Oracle.
why it is showing Error code : 1193. unknown system variable 'Age' , even after successful implementation of Age column ?
I'm not sure what the issue could be. You could post the screenshot of the query and the error on Telegram and then we could try to figure it out
i get trouble with this query, the output is error :
SELECT GENDER, count(*) AS COUNT
FROM HR
WHERE AGE >= 18 AND TERMDATE = IS NOT NULL
group by GENDER;
because some part in termdate table is null? how can fix it?
Remove the = from TERMDATE = IS NULL. It should work
Mamm the 10th question it shows me error
To
Termination invalid filed from HR
Kindly share the whole error as is
Need more industry level power bi projects end to end please.. I will pay also if required
Coming soon!!
good suggestion. I would love to see near world data projects with various messy data sources. Would support with donation too!
Hi there im slightly stuck with question 10, it comes up with error code unknown column 'termination' in field list, does anyone know why?
Hi Lakita, I think the issue is the name of the column.
Make sure it's the exact name of the column as is in the database
Thank you! May I ask, what did you save your data cleaning file as from MySql? Or did you save it from somewhere else?@@herdataproject
Please I tried to download the dataset from the link on the description box, but link can't be reach. Can you help please?
Hi the link works. You can try it again here
t.me/+7ms4DzzAYVlhYmZk
If it doesn't work, try downloading from Github
github.com/Irene-arch/HR-Dashboard-MySQL-PowerBI
🙏 Am grateful, thanks
I will explore the options
Great Video. i'm confused why termdate
The assumption is that termdate is the date the employment ended. So it wouldn't make sense to have it as a future date. That's why I made sure to use the termdate as less than more equal to the current date. Hope this helps!
when we are considering the term date 00000 to select the employees, what about the employees whose term date is in future?that means they are still in the company and we are not considering them
We can't verify that for sure so we assumed it was a data entry error and documented the same as limitation in our analysis.
@@herdataproject thank you so much for the ans.
thanks for the video, very well explained, just one question if the original CSV file that you initially imported to SQL suffers any change lets say gets more rows added whitin the already existing categories would that get reflected on the MySQL values as well?
I'm not entirely sure what you mean. Are the rows added during the import?
Search for duplicates
Thanks it working
great course
when I try to change the termdate I get an error stating incorrect datetime value. mine is copied EXACTLY like yours so I'm so confused
Error Code: 1411. Incorrect datetime value: '2029-10-29 06:09:38 UTC' for function str_to_date
run this query first
SET SQL_MODE = ' ';
then run the update query
Hello ,
UPDATE hr
SET termdate = date(str_to_date(termdate, '%Y-%m-%d %H:%i:%s UTC'))
WHERE termdate IS NOT NULL AND termdate != ' '; - This part is throwing error on mysql.
Below is the response error-
Response- Error Code: 1411. Incorrect datetime value: '' for function str_to_date
@@herdataproject Error Code: 1411. Incorrect datetime value: '' for function str_to_date
So the error message suggests that you have an empty string ('') in your termdate column, which cannot be converted to a datetime value using the str_to_date() function.
To fix this, you can add an additional condition in your WHERE clause to exclude empty strings:
So add this condition as well to just take care of any kind of empty string in the column
UPDATE hr
SET termdate = date(str_to_date(termdate, '%Y-%m-%d %H:%i:%s UTC'))
WHERE termdate IS NOT NULL AND termdate != ' ' AND termdate != '';
UPDATE hr
SET termdate = IF(termdate IS NOT NULL AND termdate != '', date(str_to_date(termdate, '%Y-%m-%d %H:%i:%s UTC')), '0000-00-00')
WHERE true;
this will fix it
@@casafurix Thanks
@@casafurix it's showing this error
1411
Incorrect datetime value: "for function str_to_date
Please i have a question...
When i query for "what is the gender breakdown of employee in the company" and SQL returns the result table.....is there a way to save the result so that when I connect powerBI to the SQL database I will have access to the result table from the query rather than exporting each result as a CSV because imagine you are working with a large company and the result table from your query is 3million rows I don't think I will be able to export that into excel easily? Do you get me?
Yes I get you. I think what you're looking for is something like a database view. Create a database view with the SQL query logic. Power BI can then directly connect to this view and retrieve data without running the query every time. I have never tried to use it before with PowerBI or even Tableau but I think it would work. You can try it out and let me know.
@@herdataproject I think you're right. Just did a quick Google search and I think database views might be the solution. Thanks for the reply
At @28.34 im getting this error select gender,count(*) as count from hr where age >= 18 and termdate = '0000-00-00' group by gender LIMIT 0, 5000 Error Code: 1525. Incorrect DATE value: '0000-00-00' 0.00037 sec
Are your date values blank or in that format?
@@herdataproject no its not blank . i did the same as you did.
@adityabonde7310 please share a screenshot on telegram. Link is in the description
Thank you but where do i get this sample data from in order to do the same on my own ?
Hello check the description for the Telegram link and once you join, check the pinned messages
for the birthdate update, i keep getting truncated incorrect date value '6/29/1984', how do i correct that error
Change y to capital Y
@@ChinweAirdrop the birthdate column becomes null
Pls what's the solution?
@@ChinweAirdrop Thank you so much
Hello, how to solve the below problem in Microsoft sql
UPDATE HR
SET termdate = CONVERT(date, termdate, 120)
WHERE termdate IS NOT NULL AND termdate != ' ';
Error - Conversion failed when converting date and/or time from character string.
you can modify the UPDATE statement to exclude the problematic rows that cannot be converted to a date.
UPDATE HR
SET termdate = CONVERT(date, termdate, 120)
WHERE termdate IS NOT NULL AND termdate != ' ' AND ISDATE(termdate) = 1;
The additional condition ISDATE(termdate) = 1 is added to ensure that only valid date values are considered for conversion. The ISDATE function checks if the value can be converted to a valid date, and only rows where the 'termdate' value is a valid date will be updated.
Hope this helps
I tried converting the birthdate format but it's not working for me can anyone help
Using Microsoft sql and there is no str_to_date function, tried changing termdate data type but was not able to, tried convert, cast but it's not working, please help
In Microsoft SQL Server, you can use the CONVERT() function to convert a string to a date. The format string to use with CONVERT() is different from the one used with STR_TO_DATE() in MySQL.
UPDATE hr
SET termdate = CONVERT(DATETIME, termdate, 120)
WHERE termdate IS NOT NULL AND termdate != ' '
In this example, the CONVERT() function is used to convert the termdate string to a datetime data type using the format code 120, which corresponds to the ISO 8601 format (yyyy-mm-dd hh:mi:ss(24h)).
Something to keep in mind is that the CONVERT() function can be affected by the default language setting of the SQL Server instance. If you're experiencing issues with the conversion, you can try using the SET LANGUAGE statement to set the language explicitly before running the UPDATE statement:
SET LANGUAGE us_english
UPDATE hr
SET termdate = CONVERT(DATETIME, termdate, 120)
WHERE termdate IS NOT NULL AND termdate != ' '
I hope this helps : )
@@herdataproject thank you so much
You're welcome. I hope it worked
Still not working in Microsoft SQL server
Irene, First I tried to download CSV, it became a text file, when I changed the file to CSV and imported to SQL, the data is not clear and has errors, please tell me step by step how to download the file from your GITHUB, and how to open in MySQL
You can also download it from the telegram if you're able to install the app. I think it should download as a CSV file. Once you join the group it'll be pinned at the top
thank you so much 👌👌
Thank you very much, i found this tutorial very usefull
You're welcome!!
Amazing work. Plz make Python data Analysis Project Video.
Thank you. Coming soon!
I have seen half of this video while even I couldn't know you are Male or Female.
But video is osm fantastic ❤❤🎉🎉
Thanks! It's been helpful. :)
You're welcome!
Thanks for watching: )
I keep getting an error syntax “NUll”
Line 4: Where Termdate =18;
Remove the
Okay
i am also getting same error how to fix this?
@surbhigupta9522 remove is
hi, thanks so much for this great video , i downloaded the dataset but it's cleaned how i can get the uncleaned one please
You're most welcome. You can download it from the Telegram group. I've pinned the original dataset
I think you made a mistake in minute 33:17, it should be THEN '45-54' and not 44-54 and you didn't correct it, but I think everything else is fine, thanks for the video.
Thanks for the feedback. I'm glad you found the video helpful
Amazing work
Thank you
Thank you and you're most welcome!!
Why do I get different values with the exact same code?
Depends on the column you're referring to. For dates etc, you're running the code on a different date than I did. If that's not the case then please share a screenshot in the Telegram group to allow me to assist you further
can anyone explain the aim of this project mean why we make dishbord and i cant understand dishbord mean wht is the problem behind this that we make this typle dishbord
This is an informational dashboard. It helps management understand the status of their employees at any point in time
While operating on termdate
And executing the query the date format was changed but blank cells were not updated with ''0000-00-00"
They don't have to update to 0000-00-00. It all depends with the settings of your MySQL. If yours have remained as NULL it's okay. When writing the queries use
WHERE termdate IS NULL instead of WHERE termdate = '0000-00-00'
@@herdataproject sure and thank you
You're welcome
@@herdataproject sir will send that error in telegram group please help
@@herdataproject Is it a problem when working with Power BI, Because of a NULL value
Hi, for min and max ages did anyone not get negative values? My result doesn't show -42. It shows minimum value as 20. I tried copy pasting the query from github too. Same result. Would really appreciate any help:) Thank you for the video .
You are correct mine shows as 21 maybe because I am doing this some months after you had done yours
thank you so much for ur work.
You're most welcome!
Whys the dataset is in raw form and not csv ? how to convert it?
The dataset is a csv. If you're getting it from GitHub click on the file and after loading there's a download icon on the right side. If unsuccessful download from the Telegram group. It will be on the pinned messages
U did nt show sql query answer in power bi dashboard that we want to see for example u find total order in sql then u should show total order in power bi dashboard i want to see this that when u find any sql query answer as i mention total order in above then show that answer in power bi dashboard pls tell about this how will do this
When creating a dashboard it's not necessary to showcase everything just the important metrics. But if you want to visualize the result of every query just save the query results as csv file and import into PowerBI
hello,
the videos is very helpful
but i need some help, i dont know why my location_state column name become location_state;
that contain semi colon,how do i remove that semi colon from my table name?the value is also same,from Ohio to Ohio;
thanks before
You can rename the columns or create a new table by inserting the values of your current table into it then dropping the first table
UPDATE hr
SET birthdate = CASE
WHEN birthdate LIKE'%/%'THEN date_format(str_to_date(birthdate, '%m/%d/%Y'),'%Y-%m-%d')
WHEN birthdate LIKE'%-%'THEN date_format(str_to_date(birthdate, '%m-%d-%Y'),'%Y-%m-%d')
ELSE NULL
END; I have been receiving an error with this. It says incorrect datetime value 2012-01-01 for function str_to date
Run SET SQL_MODE = ' '; then run the update query
@@herdataproject i did. But it wont work. I have been receiving error.
The error is not about being unable to update. It is with the code itself.
I have tried revising it. It will only change certain rows. Not all rows.
The LIKE and THEN part seems like one word. Could that be the issue?
@@herdataproject nah it is still the same. I am just wondering if I could show it to you via zoom or google video..
The error is still the same.
It says
Incorrect datetime value: '2012-01-01' for function str_to_date