MS SQL Power BI Data Analysis Portfolio Project | Beginner to Expert | Power BI Dashboard

[@HisDataProject]

There's a correction in the case statement at 18:00 - the lower age should be >=, not =21 AND age =31 AND age =41 AND age

[@emmanueledozie1357]

THIS IS WONDERFUL. ONE OF THE THINGS I LEARNT IS THAT I HAVE A WHOLE LOT TO MASTER IN SQL FIRST.

[@koiroi1854]

Hi, thank you for this great project. I believe there might have been a mistake with the conditional statements 18:00 , it should be below conditions: WHEN age >=21 AND age =31 AND age =41 AND age

[@HisDataProject]

Yes, this is on my first comment pinned at the top. I couldn't edit the video to fix it.

[@brilliantMotjiang]

This is very informative please keep sharing the knowledge. This project is great

[@alexrosen8762]

Subscribed! Extremely powerful tutorial with interesting SQL queries before the actual import to the Power BI engine and the visuals to end a fantastic tutorial. I learned a lot 👌🙏👌

[@bellasvideo5620]

Hi, thank you for this great project. I believe there might have been a mistake with the conditional statements 18:00 , it should be below conditions: WHEN age >=21 AND age =31 AND age

[@HisDataProject]

Yes. This is pinned in the first comment.

[@tonygiovanni5761]

Good stuff. Very informative

[@SathishKumar-oq7tz]

Thank you sir for bringing this project.

[@HisDataProject]

My pleasure

[@Wissou_]

Thank you so much for this tutorial. We need more projecs like this please

[@vallepupraveen5137]

Nice it's helpful tq so much

[@HisDataProject]

Welcome 😊

[@MCKittu03]

Thank you so much

[@HisDataProject]

You're most welcome

[@JunSim-t2o]

Thanks for the video! May I ask where is the data originally from? Thanks

[@HisDataProject]

Original data is from Kaggle

[@oba_idan]

Please check those case statements again. The lower boundary of the conditions in the WHEN statement will result in duplication of counts(for 31-40 and 41-50 age_group). To highlight my point, see this -----> Assuming the ages we are working with are (25, 36, 22, 28, 33) We want the query to return the count of age_group 21-30 as 3 and age_group 31-40 as 2. This should be the correct thing. But based on how you put the condition, it will return age_group 31--40 as 5. I think this may be solved if the WHEN conditions are modified as follows; WHEN age>=21 AND age=31 AND age=41 AND age

[@HisDataProject]

Yes, this is true. Good catch. Thank you!

[@oba_idan]

Well done@@HisDataProject this your project has taught me so much, you are the best, I will keep learning from your other videos. 🙌

[@ruelformales5184]

The SQL mastery for this project is amazing! Is this project purposely created to be non-interactive or did I miss something? thanks!

[@HisDataProject]

Yes, to be interactive, you’d have to make use of Power BI measures and power query. You can watch this from this URL: kzbin.info/www/bejne/mGatgXuPetusbrcsi=d-hS99oO95-sYd3n

[@Data-Miner]

Would not it be easier and safe to creating new columns, transforming data in Power BI instead of using SQL Database server? In Power Query you can transform all data with a few clicks and if you insert any new column and data, you can see the applied steps for tracing and monitoring purposes.

[@HisDataProject]

Yes, you can also use power query as @themolestones had asked. The tool you use depends on your current needs.

[@demon-h5p]

how did you resolve future dates in termdate column bcoz i am getting min(age) in negative value i.e -41

[@HisDataProject]

@spacespectale, you need to have a WHERE new_termdate

[@bilalhassankhan221]

good job...

[@HisDataProject]

Thanks!

[@shivuhosamane6453]

Sir really good videos make more videos python project for data analysis please

[@HisDataProject]

Sure, I'll upload some soon!

[@javierlopez5427]

Hi, good video, how do you do with the interactions in power bi report? if all tables are from differents CSV? if you click a graph filter others?

[@HisDataProject]

This report is not interactive. The Power BI interactive report is created in a separate video.

[@raveenathakur1176]

Hello ,, In Set termdate When we format datetime its not converting from character string in my MSSMS ,, Why This Error ??

[@themolestones]

Thanks for the detailed explanation. One question, why don't use direct power query / DAX instead of SQL first?

[@HisDataProject]

You can use power query for this as well (power BI or Excel), but I wanted to highlight how you can do this in SQL.

[@themolestones]

@@HisDataProject thanks. And do you see any benefit using SQL vs Power Query? Maybe with a huge amount of rows?

[@HisDataProject]

​@@themolestones you can't compare them "either this or that". Each tool has its use and strengths. SQL can accomplish a lot in a short time. Power query is is really powerful in data analysis. The data you have in front of you determines which tool to use.

[@skkhasim983]

i mean , the question consists 4 -columns 1)department 2) total_count 3)terminated_count 4) turnover_rate . in the Dash board while uploading the columns into chart(in power bi ex:- stacked , clustered graphs) it not shows the summation column that you are uploaded , in x- axis (department) and y-axis (turnover_rate) i tried with this scenario that u have mentioned in video . but not shows well . can you help me with this miss match of columns .

[@henhunter4298]

I had the same problem. If you did exactly the same as he did in the video, go to the table view on power BI for the turnover Rate data, should have "column 1" listed showing the different departments. Use that for the X-axis slot. Then, column 4 should be showing you the percentage turnover rate (column3/column2) as we did on the SQL side. Set column 4 into the Y-Axis and switch it from count to sum of column4. You'll then see the chart correctly reflecting the data. Good luck!

[@mohitjoshi8984]

Hello it's really helpful Can you tell me who you get the data

[@HisDataProject]

You can get the data and all the files from github: github.com/kahethu/hr_data/tree/main

[@skkhasim983]

Can you please check the Turn over rates across Department question...? it seems to be some error while inserting into power bi insight....?

[@HisDataProject]

Are you using SQL Server 2022? I’ve found this to be a common issue. There could be variation issues

[@harishthalanki6603]

Very nice explanation. I have one question sir, who gives the questions for which you have written all those sql, customer or self designed in a IT company? Thanks for answering.

[@HisDataProject]

This data is public. Once you explore the data you can easily come up with questions that a stake holder would ask. In real life, you'd have data and questions that the stake holder wants you to answer.

[@harishthalanki6603]

​@@HisDataProject, Thanks for the reply

[@hashanpeiris4091]

First You load CSV file to sql sever but in Power BI you again work with CSV files...what is this complecation??

[@harshamokhasi]

I appreciate your work. I have one question. Do we work in this way in companies? Will the workflow be similar?

[@HisDataProject]

This project represents a "real life" problem such as what can be presented to you in the work place. If you're analyzing data using SQL and Power BI, this would be pretty close.

[@overkal310]

HI great video just 1 question where did you get the data from ???

[@HisDataProject]

Original data is from Kaggle

[@jasondeb2678]

where did you download the date originally from?

[@HisDataProject]

Original data is from Kaggle

[@oba_idan]

Does anyone know how to restore termdate to the initial content and datatype.

[@HisDataProject]

If you're referring to restoring the original values after you've run a query, I don't think you can recover that. You'd have to re-load the original data in a new database.

[@alexandretemp]

Hi. Now I have another issue. when I imported csv into Power B.I, it didn't update the query it had been created on SQL. Do you have any e-mail to sent to you my screenshot even file, please?

[@HisDataProject]

I'm not sure I understand your question. Are you using SQL server?

[@gregssmartphone4296]

There are 22,214 records in age column, If you count the results of the age distribution query you only get 21,384 ? If you query each group separately (ie, ages 21 to 30, 31 to 40 etc) and add them up you do get 22,214. So why is the CASE query above giving the wrong results???

[@alexandretemp]

Hi Guys. Does anyone else could help me? I ran this code " UPDATE hr_data SET termdate = FORMAT(CONVERT(DATETIME, LEFT(termdate, 19), 120), 'yyyy-MM-dd');" but at the end, it came up with " Conversion failed when converting date and/or time from character string." Help me, please Thank you.

[@HisDataProject]

Which version of SQL are you using? Some versions don't support the FORMAT function

[@alexandretemp]

@@HisDataProject SQL SERVER MANAGEMENT STUDIO 19.1. Could you help me, please?

[@HisDataProject]

​@@alexandretemp This error can happen if some values in "termdate" cannot be converted to DATETIME format. Maybe a TRY_CAST function can work, and if some don't work, use a CASE function for a default value for those that can't be converted. EXAMPLE: UPDATE hr_data SET termdate = CASE WHEN TRY_CAST(LEFT(termdate, 19) AS DATETIME) IS NOT NULL THEN CONVERT(VARCHAR(10), TRY_CAST(LEFT(termdate, 19) AS DATETIME), 120) ELSE '0000-00-00' -- you can set any default value here END; I'm not sure why you're getting the error, but a different approach like this could probably resolve it.

[@esthernwaogu592]

i dont understand what he did in termdate

[@HisDataProject]

What do you need to know?

[@oba_idan]

he basically converted the original termdate format to a 'yyyy-MM-dd' format which is a format easy to work with in terms of computation. Then he created a new column(new_termdate) with datatype as DATE and just copied the values of termdate into the new_termdate. I see some redundancy though as both columns now hold same details. Please does anyone know how to restore termdate to how we met it originally

[@HisDataProject]

@@oba_idan in SQL server you can't convert nvachar to date directly, you'd have to create a new column (date) and copy the data to it

[@oba_idan]

@@HisDataProject Oh I see...thanks for the clarifications

[@divyajeetsingh5049]

While getting the age group distribution , you put a condition WHERE new_termdate IS NULL. Theres a problem with this condition. What if the termdate of the person is not yet happened and is in future? You have also excluded those person by saying WHERE new_termdate IS NULL.

[@divyajeetsingh5049]

Proper expression should be WHERE new_termdate IS NULL OR new_termdate> GETDATE()

[@HisDataProject]

The idea is to filter employees who have not been terminated yet

[@divyajeetsingh5049]

@@HisDataProject You are not getting what im trying to say. There are a lot of dates in the new_termdate column which have not yet happened. Like there is a date of 2029 year. But by putting WHERE new_termdate IS NULL you are also excluding those dates. By excluding those dates of future you are also excluding those employees who have not yet terminated. Proper query should be WHERE new_termdate IS NULL OR new_termdate> GETDATE()

[@rajeshv5355]

Hi bro could you plz make a comment which would helps us to explain in interview

[@rajeshv5355]

Bcoz I have placed this project in my resume

[@rajeshv5355]

Could you plz reply with explanations so that I can present in my interview plz help me with this bro

[@HisDataProject]

You did a similar project? You can put it in your resume as long as you can explain each step

[@osoriomatucurane9511]

Awesome tutorial! Intersecting very same project and dataset from close sources her data and his data! I am newbbie to SQL and data analytics, but I understand that the convulse the code the better. For the New column age, why so mamy code lines to create and update de age column? Why not Select difffunction(year, dob, getday()) as age. In MySQl it works out like SELECT timestampdiff(year, birth_date, curdate()) FROM hr_data Ok, I am aware this does not affect the orginal dataset, only for the current visualization

[@HisDataProject]

in MySQL, it's slightly different from sql server. We can still use current visualization as SELECT DATEDIFF(YEAR, dob, GETDATE()) AS age