DAME 2 (Updated): MS Data Analysis Tools: Excel, Power Query, Power BI, Data Flow, DAX & M Code!
HTML-код
- Опубликовано: 15 июл 2024
- Download files: people.highline.edu/mgirvin/A...
Pdf notes to read online: people.highline.edu/mgirvin/A...
Alternative link for zipped folder: excelisfun.net/files/Video01-...
Free RUclips Data Analysis Class about Microsoft Power Tools in 2024 taught by Excel MVP and Highline College Professor, Mike “excelisfun” Girvin.
In this video learn about ALL the Microsoft Power Tools: Excel, Power Query, Power Pivot, Data Model, Power BI, Dataflow, Worksheet formulas, Dynamic Spilled Array Formulas, M Code formulas, DAX formulas, and much more!
Topics:
1. (00:00) Introduction
2. (00:13) List of MS Power Tools taught in video
3. (00:44) Preview of 7 examples
4. (01:48) Why use Worksheet as a data analysis solution?
5. (03:09) #1 Worksheet Formula solutions: XLOOKUP to do data modeling , PivotTables, dynamic spilled array formulas, and the GROUPBY and PIVOTBY functions
6. (22:41) #2: Power Query to import data (.txt & .xlsx), transform the data create a report.
7. (34:49) #3: Use Power Query to load a table to the PivotTable Cache.
8. (41:07) Fix broken On Premises File Path: Data Source Settings.
9. (43:02) #4: Json data, Power Pivot, Data Model, DAX formulas, Row Context and Filter Context for creating Data Model PivotTable Reports.
10. (01:09:12) #5 Xml data, Power BI Desktop, Data Model, DAX formulas and Interactive Visuals. Visualization tips too.
11. (01:33:58) #6 Power BI Online Services
12. (01:38:57) #7 Dataflow (Online Power Query)
13. (01:45:25) Summary
14. (01:45:55) Conclusion
This is Updated DAME w MPT #2 with the correct "RELATED function and Row Context in a Calculated Column" section at 50:05 minute mark. The first video that I made had a catastrophic hardware and app failure and the section got deleted. I added it back in this Updated #2. The new part in this video is at 50:05 and is important because the concepts of row context sets up the SUMX part later in the video... Go Team!!!!!
The cool thing is that I saw the video when i was going to work, and I played the knowledge directly to prepare report, it's amazing what we can do with power pivot, good job Mike 👍
Glad this helps, DC!!!!!
This video, among all the other one posted in this wonderful channel, is pure quality. I think quality is the word the most represents your work and your commitment Mike.
Thank you so much amazing Mike for this EXCELlent video.
Thanks Mike, especially the last part with dataflows, I have to figure out how this works in my organization. But mindblowing how you explain so much in such a short (relative😉) time!!🙏🙏
I am so glad that this helps. My organization shares data with google drive and encourages all employees to use google sheets. I tried to persuade the tech administrators to not go this direction, but...
@@excelisfun HI Mike, does this dataflow (Online Power Query) thing also need PBI premium capacity? Because a lot of users only have PRO licenses, can still create and publish, share with others who also have PRO licenses. But the whole thing becomes quite dependent, linked to personal account. If people leave the company then everything is lost
@@stoneage8810 Yes, you must have premium.
Amazing
Glad it is amazing for you!!!
Many thanks ..great as usual
Glad you like it!!!!
thank you Mike
Thanks Mike!! :) :)
Thanks Mike!
I am happy to try an help the Team : ), Chris M!!!!
I didn't have a chance to watch this video until now. Glad I procrastinated intil youvreleased the "updated" version!! Great summary long butvyou had a lot to summarize! Does the DANE seriies replace MSPTDA? Or is it intended to be a complement/supplement to that series in your classes?
Well, all the classes E-DAB, MSPTDA, 4_video short class, DAME are all the spring class that I teach BI 348. They are all fine, the newer ones match up better with the user interface : ) Technically, MSPTDA has the most topics. DAME is hard core comparing all the tools and when to use them.
Is Alteryx a competitor to all of these?
-could you show how to create a hierarchy in query? Eg sap extract from trial balance shows subtotals. Whereas these subtotals are part of another subtotals (accounts receivable net -> current assets->total assets) Great video !
I’m at 1:14. Where did the Ave.DailySales02, COGS01 and TotalSales01 come from in the fTransaction in the data model. Did I miss something?? Excellent video btw. 💯💯
Please can you explain how we use the dynamic segmentation to do an aged balance ( for customer) by defining rules for example (Last 30 days; Between 31 and 60 days;Between 61 and 120 days;Between 121 and 180 days....)( dax solution) thanks in advance
🖥 😁 👍 ⚡ 👌🖥
At 1:10:51 - when I load the XML in Power query in Power BI, the Dates are in text and I'm getting an error when I changed the format to date
Perhaps your computer has a different setting for dates. You can try "Using Local feature:
ruclips.net/video/JGppsnepJFo/видео.html
@@excelisfun awesome. That works! You're the best
Hello there, THis is DAME series is totally great. Anyway, Got some confusion about why , in power Bi section, my Ave.Dailysale with AverageX result was (blank)?
What is the formula that you used?
@@excelisfun Already found the root cause, it's the date type in dDate table was in Text instead of the date type which caused the Blank() result. To answer ur q, It's Avg X. Anyway, it's my bad and became my lesson learned.
Thx you sensei,
@@nongartinunt2014 : ) : )
@@excelisfun 😅😅
i tried to make same steps for three times in microsoft bi each time i reach graph section it gone wrong please help me and each time i import xml file or jason and try to cange date from text to date it is filled with errors
I have to return to 22:00
Will the sound issue be there in the next videos 🥹
What is the update in the video? I already watched it once it was published
Would be better to thank him first for the great job he is done
.and for free
No, the sound will be better. It was just that I had a catastrophic hardware and app failure and tried to work between there different computer, and it did not work so well. The new part in this video is at 50:05 and is about RELATED function and Row Context in a Calculated Column. This is important because the concepts of row context set up the SUMX part...
@@wajdimatoussi he is the best excel trainer in RUclips, I’m thanking him always,
Don’t be sensitive and illusive
@@BaniMoniah sorry for the misunderstanding