Data Analysis End to End | Project Analytics | Beginner Friendly
ฝัง
- เผยแพร่เมื่อ 5 ก.พ. 2025
- End-to-End Data Analysis Project from start to finish using Excel, SQL Server, and Power BI, we’ll build a Product Analytics Dashboard that tracks revenue, profits, and trends. You’ll learn how to break down management request, build a database, write SQL queries, and create executive level dashboard. Whether you’re just starting out or want to sharpen your skills, this is a great way to dive into real-world data analysis.
What is Covered in the Analysis
1. Build a Database
2. Develop SQL
2. Connect to Power BI
3. Create a Dashboard
***To create a Date table****
1. Click Modelling in the top ribbon
2. Choose New Table
3. Add this to your DAX formula: Calendar = CALENDAR("1/1/2022","12/31/2023")
4. There tables should connect automatically. If not, just go to modelling and build the relationship by connecting the two tables by date.
Timestamps
-------------------------------------------------------------------
0:45 Management Request
1:54 Company Overview
2:15 Dashboard Preview
3:36 Installing SQL Server and SQL Server Studio
7:25 Building your Database
14:20 Exploring the Database Tables
17:35 Developing SQL Queries
28:31 Connecting Database to Power BI
31:30 Building the Dashboard Structure
36:08 Image Slicer
38:46 Animated Image
44:28 Bar Charts (Revenue by Country and Over Time)
52:40 Pie Charts
54:47 Add Column and Profit Calculation
1:00:03 Year on Year Calculation ( see table calculation above)
1:04:17 Visual Card
1:15:08 Toolttip Page Creation
1:24:29 Cleaning up the Dashboard
You can get the zip files here on Github:
github.com/Gae...
You can get the dataset files:
absentdata.com...
See other End-to-End Projects here:
• Data Analyst End-to-En...
Try Stratascratch to practice SQL interview question from top companies
stratascratch....
Learn SQL at LearnSQL.com with great course for Data Analyst
learnsql.com/?...
I've learned more in this video, than I have elsewhere on the web. Thank you so much for this!
I'm so glad you learned so much from the vidro. Share it to help others.
Big thanks for this! Keep doing this kind of video :)
Much love!
@@absentdata Hello, thanks for the video; how did you create the 2023 and 2022 button visualizations at 59:56 as just before the report did not have these; I made buttons through the insert tab (not selecting the visualizations pane as I did not see those visualizations in the available ones) and shaped them into a trapezoid but when I select the filters pane (with my buttons selected) it does not say Filters on this visualization, it only says filters on this page. You kinda skipped that step and I am confused. I am having trouble trying to filter the the Units Yoy and Profit Yoy for 2022/2023 as a result; as you did not explain the edit relationships thing or the visualization I explained above. Could you please respond back?
Thank you so much, i'm at the beginning of the data analyst career and this is so helpful and useful! I hope there will be more videos like this.
Glad you enjoyed it. There are more videos like this on the channel playlist..
How did you prepare . can u help me ?
You're a legend for doing this!
nice tutorial. well done
Thanks for providing Dataset, helped me for practice.❤ you
My pleasure
Very interesting and easy to understand, looking forward to next contents.
Thanks, there will be more to come!
Awesome video as always, we need a video to analyse data and work with python
I definitely will add new ones. However, there quite few on the channel. Here is the playlist th-cam.com/play/PLi5spBcf0UMXc_qCX8FSwKxOlOmODtcUP.html&si=7aKmXWjjqsPy3ykx
Thanks a million sir..😊😊
Thank you for watching
Awesome video👍Thanks for making this video
My pleasure 😊
Solution to handle incorrect date format after data import:
By default, SQL Server uses system regional settings to interpret date formats (e.g., DMY for Vietnam, MDY for US),
then converts them to YMD format when storing.
-To handle DMY format, run SET DATEFORMAT DMY before importing.
-If you prefer not to change the default, update the table with:
UPDATE product_sales.dbo.product_sales
SET Date = CONVERT(DATETIME, FORMAT(Date, 'yyyy-dd-MM HH:mm:ss'), 120);
-Alternatively, change system regional settings:
+Go to Region settings.
+Set Date format to dd/MM/yyyy.
+Restart SQL Server if necessary.
-You can also edit the CSV file before importing to ensure correct date formatting.
You're a hero! Thanks for posting this
Thank you for this
@@absentdata It would be a good idea to pin this post at the top of the comment section for it to be the first one seen by the viewers as I am pretty confident a lot of the viewers following the video as a tutorial will have to face this issue.
bravo on this video. I pretty much consider myself an intermediate SQL guy - but I picked up a thing or two watching you do some SQL.
One question i have. Curious why you didn't clean any data before importing it to SQL Server.
thx again!
same question , when we can clean it in python easily , who do we have to do it in sql
It was just to keep this video short and simple. Cleaning up data is an important step but for a beginner video, I was just going for the core concepts.
You can do data cleaning at the source level which is SQL or in that BI tool or in Python. Doesn't matter where as longs it gets cleaned
Great
Skimmed through this a bit to see value of coming back later.
I think I am going to show this to my interns as it shows way more than our corproate training!
*Full disclosure question (didn't watch it all yet)
The images you have as image_url @42:44 that you selected. Can is that all local data or actual URL images from web? Never seen something like that used dynamically, always still photos.
Glad you enjoyed the content. In the video there are local and image URL used. For the slicer and main image, I used image URLs. Its easier that way and you can share the dashboard. It is possible to use just local images, it requires a bit more work to transform them with the invoke function.
First of all, I would like to thank you for your work! It is helping my data analytics studies A LOT! At 39:34 I'm encountering an error when I try to filter by clicking the images. "Error while retrieving data for this visual"
"Query (4,3) The column 'Image_url' in table 'Consulta1' cannot be found or cannot be used in this expression."
What can I do about it? I don't know if it matters, however, my OS and PowerBI language are pt-br.
Remember the errors are not decoration. Please read them. You need to follow the video, its addressed. The error says you dont have the image url in your code. So, please add the image url to your SQL code to bring so that you can avoid this error.
28:28 Connecting MS SQL Server to Power BI
Thank a lot, but I encountered an issue at minute 40:06. After adding the Product, when I tried to click on the paintbrush visual, I couldn’t find the Images menu.
I have faced same problem its very simple bro when we add card we drag the product data eventually it's stored in small multiples data field ...thats why we can't get the image .we have to drag that product to Data field just above the small multiples....
amazing video, i've learned alot
But just one issue, at around 1:05 instead of 34%, I am getting the answer as 0, for all the products.
Check out the description it will show you how to create a date table. There was an issue when I uploaded it and this short section was skipped.
Thanks for the video; how did you create the 2023 and 2022 button visualizations at 59:56 as just before the report did not have these; I made buttons through the insert tab (not selecting the visualizations pane as I did not see those visualizations in the available ones) and shaped them into a trapezoid but when I select the filters pane (with my buttons selected) it does not say Filters on this visualization, it only says filters on this page. You kinda skipped that step and I am confused. I am having trouble trying to filter the the Units Yoy and Profit Yoy for 2022/2023 as a result; as you did not explain the edit relationships thing or the visualization I explained above. Could you please respond back?
There are only filters and no buttons used. If you want to know how to build a date table, read the description.
@@absentdata Thanks for replying I don't mean the Dates table, I have that already. What did you do exactly to make the 2022/2023 black and white colored filters that trapezoid shape at 59:56 in the video next to the ASCES logo; like what steps did you take? I have tried to recreate it in but because you did not actually show them I have not made progress.
@@absentdata I know you are busy sir, but could you please respond to my follow-up query just above. Thanks.
@@absentdata No worries, through some digging I found out it is a tile slicer (2022/2023) visualization not a button
@rendezyt were you able to edit the image slicer in 36:00-40:00, i am genuinely having hard time with this as I do not get the options to edit the image slicer
Thanks sir
Welcome
Thank you so much for this video, Its indeed end-to-end. Please at 40:32 my image did not display despite following the steps outlined. Please how can I fix this?
The instructs start at 36:00 not 40:32.
@absentdata thanks so much for responding. I followed it step by step but still not displaying. The first set of 6 images is displayed,but actually referring to displaying single images by going this steps:
1. I selected card
2. Select product
3. Select image url option from visual
4. Click on FX, dropped down to select image url and ok
Still my pop up did not show the image
@@adewumiogunniyi6841 I had same challenge. Try selecting product first then choose "card(New)" and see if it can solve your problem
@@adewumiogunniyi6841 I have faced same problem its very simple bro when we add card we drag the product data eventually it's stored in small multiples data field ...thats why we can't get the image ..we have to drag that product to Data field just above the small multiples .....
@abdulvajeth9740 thank you so much, brother. I just did, and it worked!
Thanks for great tutorial. But, I ran into problem at 1:01:25, I can't find 'Data Table'. Where did you get it from?
You can create a date table by following the instructions
1. Click Modeling in the top ribbon
2. Choose New Table
3. Add this to you DAX formula: Calendar = CALENDAR("1/1/2022","12/31/2023")
Thanks for quick reply. I created a date table ad you suggested tho I don't know why the percentage is 648K%
Check your calculation and also that the discount column is a decimal and not a whole number. For example 14 should be .14
@@absentdata I fixed it by draw relationship line between Calendar and date column in the main table with many to one as the cardinality. It gives the same result (34% growth) but I dont know if it's the right way to do it. Is it right?
Alternatively you can write this expression
CalenderTable=
CALENDER(
DATE(2022,1,1),
DATE(2023,12,31)
)
Hey, thank you for the amazing content! I’m facing an issue where, when I load data using the query into Power BI, the table appears empty. It seems like the query is not returning any results. I believe there might be an issue with the join conditions or the data transformation steps in the query. Could you please help me identify what might be causing this?
You need to paste the actual error for someone to help you.
can i put this as my portfolio?
Of course, you can!
I get this error while executing the query - 13:15:28 with cte as ( select a.Product, a.Category, a.Brand, a.Description, a.Sale_Price, a.Cost_Price, a.Image_url, b.Date, b.Customer_Type, b.Discount_Band, b.Units_Sold, (sale_price*Units_Sold) as revenue, (cost_price*Units_Sold) as total_cost, format(date, 'MMMM') as month, format(date, 'yyyy') as year From product_data as a join product_sales as b on a.Product_ID = b.Product) select *, (1-discount*1.0/100) * revenue as discount_revenue from cte a join discount_data b on a.Discount_Band = b.Discount_Band and a.month = b.Month Error Code: 1054. Unknown column 'a.Sale_Price' in 'field list' 0.000 sec
Can you help me out on this?
Take a look at what the error is saying. This will always give you on indecation of what needs to be addressed. The is issue is the sales_price column. Make sure you have not altered the naming. Also just remove the alias a from sale price to see if it solves your issue.
not clear enough you havent show how the image url is working and how did you connect the every image that when you click its showing data???
I don't understand your question fully. An image url is an image that exists on a wepage. So many images have a web address or url.
@@absentdata I could not show the images as well, its showing only the url on my visualization, how can I show the images? Thank you
@@absentdata it’s displaying url and not image …any idea how I can solve this
Change it to image url. Follow the video
I think we need to upload them to webserver, just like github repository, and then calling them through the github link or url.
Thanks for the content. Seems like there is a missing part around the 1hr mark when the Data Table was created, the 2022 / 2023 slicers vs the Profit YoY / Units Sold YoY I can't make the percentages match yours. Also, the data table[date] I guess has a relationship from the Date column of the Query1 table to the Date column of the Data Table...sorry noob here
Read the description. You are right! Not sure why it got cut. However, you can get the instruction in the description. There is the DAX to create the date table.
the dax formula is not working in my case. How did you create 'Data Table' [Date] in the Dax?
Read the description Add this to you DAX formula: Calendar = CALENDAR("1/1/2022","12/31/2023")
@@absentdata and then for YoY create formula as "Profit YoY =
VAR last_year = CALCULATE(SUM('query1'[profit]),DATEADD('Calendar'[Date],-1, YEAR))
return
(SUM('query1'[profit])-last_year)/last_year"?
One question I have installed the MS SQL server but when I try to create a new database it gives me an error which is 262 and says you can't create it. Could you please help me in this how to solve this issue?
I encountered an issue where my results were different from the video tutorial because the date format was not recognized correctly by SQL Server. After some investigation, I realized that the problem was related to my computer’s regional settings. The default regional settings were causing SQL Server to interpret the date format incorrectly.
To resolve the issue, I changed my computer's regional settings to Turkey, which aligns the date format to dd/MM/yyyy. Once I did this, the date format in SQL Server was recognized correctly, and everything worked as expected.
If you're facing similar issues with date format discrepancies, try adjusting your regional settings to match the format of your data.
Awesome tip!!
Thanks, been stuck in this for days
I am still encountering this same problem. Where the month that's being returned is January only. I have tried so many things but no change still🥲😭
@alicemtopcu743
how did you fix this? I am unable to alter the date setting in my computer
I keep getting an error message after adding cte .... error message is Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'select'.
Better to post your whole query if you so people can help. The error is saying you having written something incorrectly near line 22 of code before or after the select statement. The errors are there to help you.
Bro use comma
First cte end with , then you start another query using select... So most probably it's the , i have experienced this too
I'm so interesting but I'm having difficulty how to access if you can provide me further details you will be appreciated
@@afdaylmedia5088 if you are using cte
Google it's syntax if you are having a error near select ... Comma is missing from the query
@@absentdata with cte as(
select
a.Product,
a.Category,
a.Brand,
a.Description,
a.Sale_Price,
a.Cost_Price,
a.Image_url,
b.Date,
b.Customer_Type,
b.Discount_Band,
b.Units_Sold,
(Sale_Price*Units_Sold) as revenue,
(Cost_Price*Units_Sold) as total_cost,
format(date, 'MMMM') as month,
format(date, 'yyyy') as year
from Product_data a
join product_sales b
on a.Product_ID = b.Product
Everything you have done can be done exclusively on Power BI. So why did you use SQL?
90% of company data is on a database that you need to access via SQL. No company will proper data governance will have any Dashboard running of individual Excel documents
Is your TCP/IP Enabled? I cannot connect to your SQL Server
Make sure you follow the instructions. You shouldn't have that issue
how can i change the date format . I end up having a only month January. I am not able to figure out how to fix this issue. I hope its not only me .. i tried delete and reinstall the product_sales and uncheck the option use Rich Data Type Detection like you did. But i still end up having just the only January in format(date,'MMMM')as month .. ..Any idea what am i missing here . Thanks
It's your computer's regional settings. You can change it to fix the date format.
@@absentdata sorry to bother you again, i have the date format same as yours, when importing from csv but once imported in mssql it has changed to 2023-01-12 ie yyyy-mm-dd instead its 2023-01-01 (yyyy-mm-dd). i tried changin my system date format still remain same. Any idea what causes the change. Thanks
Solution to handle incorrect date format after data import:
By default, SQL Server uses system regional settings to interpret date formats (e.g., DMY for Vietnam, MDY for US).
-To handle DMY format, run SET DATEFORMAT DMY before importing.
-If you prefer not to change the default, update the table with:
UPDATE product_sales.dbo.product_sales
SET Date = CONVERT(DATETIME, FORMAT(Date, 'yyyy-dd-MM HH:mm:ss'), 120);
-Alternatively, change system regional settings:
+Go to Region settings.
+Set Date format to dd/MM/yyyy.
+Restart SQL Server if necessary.
-You can also edit the CSV file before importing to ensure correct date formatting.
@absentdata i wrote this Dax formula After connecting the tables "Profit YoY =
var last_year = CALCULATE(SUM(Query1[Profit]),DATEADD(CalenderTable[Date],-1,YEAR))
return
(SUM(Query1[Profit])-last_year)/last_year" then after creating the card it is showing "infinity" can please guide me what to do ?
Read the description
@@absentdata I have created the date table according to the description and tried the formula byt instead of 34%, I am getting the answer as infinity.
Sir, just a question while importing data on power bi from SQL Sever getting an error while loading pls help on below error!
Microsoft SQL: Incorrect syntax near ';'. Incorrect syntax near ')'.
There is a syntax error, so you have an error in the sql code you wrote. The error is telling you that error is near the comma in your code. Make sure read the errors. They tell you the problem
Hey bro, thanks for the awsome visuals. however, i am not able to get the profit yoy(34%) you got for 2023 . please can you assist with an explanation
Sir i calculated profit yoy using pivot table in excel and manually verified it, it's coming out to be 6.686%, kindly verify because It differs vastly from your 34%. Thank You.
Read the description
@@absentdata I did create the date table and connected it manually.
Can i add this in my resume...? & Portfolio
Of course you can
You are just doing sneaky edits. Following you means wasting time.
Unsubscribe and watch something else
@absentdata Definitely will do that. Don't worry. Thanks for your suggestion though.
@@nirjharacharjee1088 wdym?
*not so beginner friendly
Glad you enjoyed it. Share it within anyone who wants to learn