SQL & PowerBI Portfolio Project for Data Analysts
HTML-код
- Опубликовано: 25 июл 2024
- Send me a gift : )
www.paypal.com/ncp/payment/AM...
Shop my merch here
- my-store-f72451.creator-sprin...
- her-data-project-shop.fourthw...
The dataset can be downloaded from my Telegram Group
Join using the link below:
t.me/+7ms4DzzAYVlhYmZk
GitHub - github.com/Irene-arch/HR-Dash...
This is major a data cleaning and analysis in MySQL guided project and can also be done in PostgreSQL.
While doing this guided project you will learn how to do data cleaning and analysis in MySQL. You will learn how to convert dates, how to use subqueries and filters in your data to get correct outputs. The query results are saved as csv files to be used later on in creating a simple PowerBI dashboard.
00:00 Importing Data
02:10 Dataset walkthrough
05:05 Data Cleaning
27:21 Analysis
01:00:15 Visualization in PowerBI
01:22:48 Upload to Github
Comment below other projects you would like to see.
Get your own professional website built for free
bit.ly/freeprowebsite
SUBSCRIBE!!
LET'S CONNECT
Twitter : / herdataproject
Instagram: / herdataproject
Website: / irenenafula
Some of the links on this channel are affiliate links, meaning that, at no additional cost to you, I will earn a commission if you click through and make a purchase. This helps support my channel and allows me to continue creating valuable content for you. I only recommend products or services that I personally use and believe will add value to you. Thank you for being so supportive! 
Наука
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.
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!
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
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 ❤👍🤝.
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.
Superb understanding easy way
I just bumped into this amazing and well explained tutorial. Thanks so so much...
You're welcome!! Thanks for watching
Big thanks to you!!!
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
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
very helpful, thankyou!!! will wait for your next update👍
Thank you, more coming soon!!
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!
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
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
Thanks for covering various scenarios using sql.. quite helpful
You're welcome. I hope you find the most recent project helpful as well
Clear explanations....thank you so much
You're most welcome!!
Amazing video! Wow. Wish I wouldve found it sooner. Wouldve saved me so much trouble
Thank you so much!!
Thanks! It's been helpful. :)
You're welcome!
Thanks for watching: )
You did a very good job. Keep it up.
Thanks Daniel. I'm glad you found it helpful
wow great stuff so much value
Thank you!
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
Amazing work
Thank you
Thank you and you're most welcome!!
thank you so much 👌👌
Thank you very much, i found this tutorial very usefull
You're welcome!!
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
Excellent tutorial. Many thanks. Can you please make similar projects using intermediate and advanced SQL concepts?
You're welcome. I will definitely do that
Thank you for such an amazing video full of information and knowledge 😊👍
You're most welcome!
More to come
@@herdataproject waiting for next
You are doing it great, keep going,,
Thank you!!
Keep going. Nice!
Thank you!!
worked for me
ty a greate tutorial for me
thanks for sharing
Thanks for watching!
very informative
thanks
You're welcome!!
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
Super
Please can I see all the data types for your columns when trying to upload the data into table.
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...
Thanx Mam
You're welcome!
Thanks for watching
Amazing work. Plz make Python data Analysis Project Video.
Thank you. Coming soon!
Hi 1292 truncated date error value…how to fix that sir ..while doing the query changing string to date format
thank you for the video you created, it's an awesome project and the way you explain it makes the code syntax easier to understand. anyway, may i add this project to my resume?
Thank you, and thanks for watching.
Yes you can for now but I'd advise finding your own dataset and working on it so that you can make sure you have mastered the concepts as well as the entire process. And also in case a recruiter came across this video they don't assume you just did copy and paste.
@@herdataproject thank you again madam. I'll write your name on the description and perhaps the link to the video. After completing this project, i will start to build my own one.
I'm glad to hear that. Good luck and if you encounter any issues you can always reach out on the Telegram Community. Plenty of people willing to help
Thank you Ma@@herdataproject
@@herdataproject thank you again mam and good luck for your next project as well, im joining right now.
Hi, How can I apply filter on this csv files imported through sql query? i am unable to do.
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
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
Hi, it was a great video.
I'm working on a HR Analytics Portfolio Project.
The way You imported data in Power BI, can you please tell me how make this Dashboard Interactive ?
Hi,you can add slicers to the report to be able to filter by your fields of interest. That will make the dashboard interactive
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
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
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"
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
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
i am not able
import file into postgre sql it showing hiredate is out of range
I tried converting the birthdate format but it's not working for me can anyone help
please make vedio on orderback prediction and inventary management.
Noted!! I have added it to my list of projects
This is an excellent project. however I wonder how possible is it to filter or slice data when you importing separate csv files to powerBI? I always add filters and slicers to my visualizations I found your methodology challenging to do so.
Hello,I assume adding slicers to the dashboard would make it work.
Though I'd love to know what methodology you use to import several datasets into PowerBI
27:19 , I am getting count as zero n all results as zero
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
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
I have seen half of this video while even I couldn't know you are Male or Female.
But video is osm fantastic ❤❤🎉🎉
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
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
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
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!
Please which software do you use for the screen recording?
OBS studio
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
thank you so much for ur work.
You're most welcome!
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.
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😅
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
thanks good video but the value 365
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
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
i have been receiving an error with the birthdate change format. I have change it to this but it is still not working. UPDATE hr
SET birthdate = CASE
WHEN STR_TO_DATE(birthdate, '%m/%d/%Y') IS NOT NULL THEN DATE_FORMAT(STR_TO_DATE(birthdate, '%m/%d/%Y'), '%Y-%m-%d')
WHEN STR_TO_DATE(birthdate, '%m-%d-%Y') IS NOT NULL THEN DATE_FORMAT(STR_TO_DATE(birthdate, '%m-%d-%Y'), '%Y-%m-%d')
ELSE NULL
END;
Why is it taking more time while importing the CSV file? Can anyone please help?
I think the resources on your PC such as CPU, memory and disk speed could be a factor. Try and close all applications you're not using or extra tabs on your browser and try again.
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
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
I am having ERROR 1292 issue, can some1 help me?
Mamm the 10th question it shows me error
To
Termination invalid filed from HR
Kindly share the whole error as is
WHILE DOING THE DATA VISUALIZATION, THE TABLES ARE NOT MAKING RELATION WITH EACH OTHER SINCE WE DONT HAVE COMMON COLUMN IN EACH TABLE. HOW ARE WE GOING TO DEAL WITH THAT?
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
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
Hello, in this when trying to update birthdate after writing a code it is running but not showing the results in birthdate column it is showing null
Hi, kindly check one of the comments. It has a solution to this
@@herdataproject hi, I don't think anyone answered it in the comments cos I'm also having the same issue of the birthdate column showing null
Pls help
please can help with me with the dataset so i can practice along side
Join the Telegram group linked in the description and check the pinned message or download it from my GitHub account
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
Pls can u help, Microsoft server management keep giving error
What error
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
Pls I can't find the dataset on the telegram pls how can you help out thanks
Once you join the group, check the pinned message
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
Can I use Ms SQL?
Yes you can
Thank you so much for this project
When I try
Update hr
Set termdate = date(str_to_date(termdate, '%Y-%m-%d %H:%i:%s UTC'))
Where termdate is not null and termdate != ' ';
It shows error incorrect datetime value
Hello please refer to the pinned comment
@@herdataproject thank you 🤗
While I'm writing same to same QUERY like you ❤❤😢😢
Hi, which exact command isn't working?
@@herdataproject Update command on birthday column, he reflects error like truncated incorrect date value 😭😭 I tired since 3 days not proceeding further 🙏
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✨
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!
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
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.
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
@@FVGsetups the birthdate column becomes null
Pls what's the solution?
@@FVGsetups Thank you so much
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.
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
Where can I get this data, telegram link showing me error
Check on Github as well using the link in the description