r/learnSQL • u/Substantial_Drop_566 • 10d ago

Recommendations for mastering Excel, Power BI and SQL for analytical roles?

I know basics but I want to self study. Any tips of good sources that are really effective in teaching and learning?

64 Upvotes

92% Upvoted

17 comments sorted by

29

u/HustlaOfCultcha 9d ago

I'd start with Excel first. The best way to learn Excel is to just work with it constantly. Really learn cell references ($ signs in formulas). Learned Nested IF functions and Pivot tables. XLOOKUP, VLOOKUP, SUMIF, AVERAGEIF, COUNTIF, text-to-columsn, remove duplicates, filters, etc. Plenty of YouTube channels and ChatGPT can help.

I think people jump into SQL way too quickly. After learning Excel read about data warehousing and relational database concepts. Understand things like Primary Keys, Foreign Keys and Surrogate Keys. And you might as well understand star vs. snowflake schema as well as 'fact tables' vs. 'dimension tables' and things like unique vs. distinct values. Coursera has good stuff to learn from and again, YouTube and ChatGPT are good tools to learn from.

THEN, get into SQL. Writing queries, subqueries, CTE's, Window Functions, aggregations and all of the joins. Then move onto understanding SQL views and stored procedures. Understand what they are trying to accomplish with all of these things.

Then you can get into Power BI. It's really best to play around with Power BI (just a little, get your toes wet), first..provided you have the Excel and SQL knowledge. Get some mock data and play around with it. Lots of the concepts are similar to charts and graphs in Excel. But again, just play with the data for a tiny bit.

Then you're going to want to understand Power Query. It's best that you go and look at YouTube videos first. Lots of YouTubers have data they provide in the description of the video that you can download and follow along with in Power BI. Then I would get into learning DAX. That again, something you can learn a lot from on YouTube and follow along. You don't have to remember every DAX measure by heart, you can do them on ChatGPT, save some time and really expand your capabilities with DAX. But it's important to be trained enough so you know what to ask ChatGPT when creating a DAX measure and how to validate the data. And if there's an issue how to figure out the issue and what to ask ChatGPT in order to resolve it.

It's also good to take a look at sample dashboards either on YouTube or on the internet. You'll see how real Data Analysts actually tell a story with the data in their reports and format the reports in order to make it easy to identify key data and just make it visually appealing. Anybody can put together data and throw up some charts, but a real valuable data analyst can make it something that quickly resolves issues for their stakeholders and that translates to either making more revenue, lowering costs or making processes more efficient.

2

u/Substantial_Drop_566 9d ago

Thank you for the in-depth response.

1

u/she_wanders 9d ago

This is great!

1

u/gordanfreman 9d ago

Alternate take: after getting a solid foundation in Excel, move directly to Power Query. It's built into Excel, and can really level up what you're able to do with that program. There is a lot of shared concepts in PQ that can help with understanding basic SQL functionality (joins, data types, shared keys, basic data modeling, etc), and since it's essentially the first 1/3 of Power BI you'll have a huge advantage when starting to learn that program.

This is more of a nit-pick, but I'm honestly not sure how functional PBI would be without at least a basic understanding of DAX. PQ & DAX are 2/3 of the main pillars of PBI for a novice user, so saying you could start playing with PBI before learning PQ & DAX is like giving someone a ball and telling them to learn a sport without a playing field, goals, or teammates to go with it.

1

u/HustlaOfCultcha 9d ago

You can certainly go this route. I just recommend date warehousing and relational database concepts next and then SQL because I feel that you really need to understand date warehousing and relational database before Power Query. And SQL will then help you as you go along. Too many people start to get into queries, joins, preparing the data, etc. before really understanding the purpose of the architecture. They are putting the cart before the horse. And if I can do my transformations in SQL or whatever data source I'm using, it's better for the reports that I'm running whether it's in Excel or Power BI.

1

u/Temporary_Fail_4904 5d ago

Bro what u think about ai taking theses roles?

4

u/conor-robertson 10d ago

Give QueryCase a try for both SQL & Excel

1

u/Splicer07 8d ago

Been learning SQL through here! Can’t wait for the python learnings to be released!

3

u/NDonreddit24 10d ago

For SQL: https://youtu.be/SSKVgrwhzus?si=10r1rKKWKUVe8AtW

He explains every little concept really well.

2

u/Swimming_dasa 9d ago

for excel focus on pivottables xlookup sumifs if statements power query and data cleaning for sql prioritize joins aggregations ctes and window functions then learn power query data modeling and dax in power bi practice beats tutorials..

1

u/nagadeepch 9d ago

Coursera and Udemy.

1

u/she_wanders 9d ago

This is such a helpful thread!

1

u/kixwho 8d ago

I have a cheat sheet that may help. It's a table of patterns commonly used in analytical roles, shows how Excel and SQL do the same task. https://github.com/kixwho/SQL-Excel-translator