MSPTDA 33 Part 2: Power Query & Standard PivotTable To Show All Employees Sales Or Not

This is an alternative method for what we did last video, where we need a report to show all Teams and All Employees regardless of whether or not they had sales. This shows how to deal with a Slowly Changing Dimension where the employees on Teams change over time. See Power Query M Code from Bill Szysz.
Topics:
1. (00:09) Introduction to Data Setup and Goal of Power Query Data Modeling
2. (01:46) Power Query Transformation for Dimension Table into a Fact Table. Including List Syntax to create a list of dates that we can use to expand and repeat the record for each date in the period.
3. (06:06) Build Standard PivotTable Report, including Loading Power Query Output directly to a Standard PivotTable.
4. (06:35) Summary

Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

Excelisfun, Highline College, Data Analysis, Business Intelligence, BI 348, Mike Girvin, Microsoft Power Tools for Data Analysis 32, MSPTDA 32, MSPTDA 33, MSPTDA 34, MSPTDA 35, Slowly Changing Dimensions, Data modeling Slowly Changing Dimension, Power Query Data Modeling, Power Query M Code, Standard PivotTable, List Syntax, Dates as Whole Numbers, List Syntax Trick for List of Dates, Bill Szysz, Power Query Transformation

[excelisfun]

Topics:
1. (00:09) Introduction to Data Setup and Goal of Power Query Data Modeling
2. (01:46) Power Query Transformation for Dimension Table into a Fact Table. Including List Syntax to create a list of dates that we can use to expand and repeat the record for each date in the period.
3. (06:06) Build Standard PivotTable Report, including Loading Power Query Output directly to a Standard PivotTable.
4. (06:35) Summary

[GeertDelmulle]

Just when I thought a “merged solution” wasn’t possible we get Bill Szysz to show us how it’s done!
Thank you Bill Szysz! And that cool trick to expand the dDate table from that initial dTable using that variable list function is really great!

[shoeshines]

Thank you Mike and Bill for putting this together. The List Syntax to create the list of dates expanded to new rows blew me away!

[ogwalfrancis]

MSPTDA, the greatest class, Thanks Mr. Mike

[thierrybernot]

Brilliant ! Didn't know this {1..5} kind of spilling 'trick' in between whatever beginning and ending values.

[DougHExcel]

Thanks Mike and Bill. Great solution using PQ and Pivot Tables!

[sherryizzie]

Thanks Mike and Bill. This is a very smart trick to work around by expanding the date list!

[richardhay]

Great spproach. I just finished (last night) a dax solution. Anxious to see yours. Pause/comments represent first class pedigogy!! :)

[MalinaC]

Awesome with those null values. If I knew it before... ;). It saves tons of time! Thanks Mike and Bill for sharing!

[ricos]

Cool. I just used yesterday's OT/IT syntax in a query today. Now I'm going to have to check that a list wasn't more appropriate!

[pmsocho]

That solution came to my mind when i started watching part 1 :) Thanks!

[barttitulaerexcelbart]

Second ! nice ;;) Mike I like the way you add adtional comments. If you understand just continue, if not you can pause and read. Please figure out of other viewers like this.

[SyedMuzammilMahasanShahi]

Thanks Mike for this beautiful video. Your work is always EXCELlent.

[johnborg]

Well....as long as I receive the always First. : ) : )

[othellovancalbert]

Hi Mike, I really appreciate your training. I have to admit, I have detoured for some time due to work. However, just in a few months I decided to start working with income statements reports downloaded from Quickbooks online, reconstructing via Power Query and Power Pivot, and enhance it via Power BI. I am stuck at having downloaded a P/L.xlsx and reconstructing it with Power Query and Power Pivot to having the ability to analyzing specific groups within the total expense for example. You have done incredible demos that have come close, but perhaps I am missing a step or two or three.. Have you done a video utilizing Quickbooks online Excel P/L reports format that have also come without no tracking Number? I hope this makes since... Signed Mr. Deer in the headlights.

[DanielWeikert]

Hi Mike, can you do a video explaining the evaluation of variables in DAX?

[vrajtalati]

I have been watching your videos for quite awhile and it helps me a lot but one thing is bothering me and that is what’s fact and dimension…please share a link of video with me to help me understand the difference.

[mohamedchakroun]

Thanks mike and thamks to bill syzyz :-) Union make power :-)

[BGODE]

Hi Mike, I know it's been a while since you uploaded this video but if possible could you explain:
Why does the pivot table considers all the employee names, if the pivot table comes from a connection only, and does not consider all the employees, if i load it to the data model?
And also, if i substitute the nulls for 0s, then it works for both connection only and power pivot data model.

[vijaysahal]

me to become your fen mike sir I wnt to bcom to expert in power query nd power pivot nd power Bi plz guide to me what I do