Microsoft Excel Pivot Tables, Power Query, Power PivotDax

Best Release

2 years ago

Last updated 1/2023
MP4 | Video: h264, 1280×720 | Audio: AAC, 44.1 KHz
Language: English | Size: 12.69 GB | Duration: 18h 22m
Microsoft Excel Pivot Tables, Get and Transform (Power Query) and Power Pivot (DAX) for advanced data analysis. 70-779.

What you’ll learn
Be able to create PivotTables and PivotCharts, both basic and advanced
You will be able to format numbers and dates, and create advanced calculations.
You will be able to Get and Transform multiple sources of data, including merging multiple files from a folder, pivoting and unpivotting.
Building and refine models, including adding calculated columns and measures.
You will learn the important M and DAX functions, to enable you to build your own formulas.
Requirements
Before you begin this course, you should have a PC with Microsoft Excel 2016 or later.
The first part of this course will work with Macs, but the second and third parts are not compatible with the Mac version of Excel. (It will work if you are using the PC version of Excel, including using Parallel Desktop.)
You should also know how to open an Excel workbook, and have an interest in analysis.
It would also be good if you had prior experience in using Excel formulas, but that is not essential.
That’s it! The more experience in using Excel, the better, but it is not essential.
Description
This course covers the visualizations and analysing content required for Microsoft’s 70-779 certification exam. (Note: this exam is no longer available, but the topics are well worth learning!)ReviewsNora: "I liked that it went through really fast through the process, as expected if you consider yourself an advanced Excel user. Really enjoyable"Dilbag: "Phillip’s presentation of content is amazing. He has in-depth knowledge of the subject. I would highly recommend this course."Amy: "I want to add how this course is by far the best one I have found on the subject. It really delves deeply into each piece of material. While many courses focus on getting you ready for the exam, this one makes sure you know how to use it in the real world as well. I would suggest this course to anyone who not only wants to pass the 70-779 test, but utilize it in their job."Khalid: "Great great teacher. knowledgable in the tech and its usages, Speed, info delivery, and his voice is friendly. I’m lucky to choose this course over another one ;). Thank you all"—————-Do you already use the PC version of Excel to create data analysis, but find that you need to create more advanced analyses? Do you want to combine various sets of data, and or manipulate existing data sets? Do you want to save time, and have your analysis be automatically refreshed with new data? In this course, learn the skills that Microsoft want you to know, and add another tool to your CV, and even go for the Microsoft certification.Excel is one of the most requested analysis tools that are requested in the job market, and the ability to being able to use it at an advanced level could be very useful for your current work, and your next job hunt.This course comes in three parts:Part 1 – Creating Pivot Tables and Pivot ChartsThis part of the course covers one of the most useful, but scariest-sounding, functions in Microsoft Excel; PIVOT TABLES.It sounds difficult, but in fact can be done in just a few clicks. We’ll do our first one in a couple of minutes – that’s all it takes. We’ll also add a chart as well in that time.After only these first few minutes, you will be streets ahead of anyone who doesn’t know anything about Pivot Tables – it is really that important.After this introduction, we’ll go into some detail into how to set up your Pivot Table – the initial data, and the various options that are available to you. We will go into advanced options that most people don’t even know about, but which are very useful.By the end, you will be an Expert user of Pivot Tables, able to create reliable analyses which are able to be drilled-down quickly, and you’ll be able to help others with their data analysis.Part 2 – Get and Transform Data (also known as Prepare the Data)We see how data can be transformed, saving you time in analysing the data. We’ll look at sorting and filtering, split columns, and other transform activities. We’ll merge, append and combine queries together. We’ll Pivot and Unpivot, and transform text, numbers, dates and times, and create custom columns using the M language.Part 3 – Refining the model (also known as Model the Data)We’ll see how relationships can be made through multiple tables, and refine the data with custom columns and measures using the DAX language.The course will take around 16 hours to complete, but completing this will enable you to create vizzes, dashboards and stories of your own, and know how to overcome common problems.The course increases in difficulty slowly, so you’ll create for instance a table or basic bar chart, then turn it into a stacked bar chart, and investigate more of their properties, step by step.The course is fairly relaxed – there will be a few "wrong turns", so you can see what problems might arise, but every lesson works towards an end goal at a relatively slow pace, so you can follow on your own computer easily. I assume that you know how to use a computer, including installing programs, but the actual analysis will be at a basic level, and I’ll introduce every component as we go on.At the end of the course, you can download a certificate of completion, so you can show everyone your new-found skills, and be able to start creating analyses for yourselves.Please note: Microsoft will retire the 70-779 exam on 31 January 2021. No replacement has yet been given by Microsoft.
Overview
Section 1: Introduction
Lecture 1 Introduction
Lecture 2 Welcome to Udemy
Lecture 3 The Udemy Interface
Lecture 4 Do you want auto-translated subtitles in more languages?
Lecture 5 Curriculum
Lecture 6 Resources
Section 2: Creating our first PivotTable and PivotChart
Lecture 7 Our first PivotTable
Lecture 8 PivotTable Menus
Lecture 9 Our first PivotChart
Lecture 10 PivotChart Menus and An Introduction to Practice Activity 1
Lecture 11 Practice Activity Number 1
Lecture 12 Answer to Practice Activity 1
Section 3: Expanding our PivotTable
Lecture 13 Source data requirements
Lecture 14 Adding additional values
Lecture 15 Recommended PivotTables
Lecture 16 Moving PivotTable
Lecture 17 Refresh PivotTable – why it doesn’t auto-update
Lecture 18 Extracting data
Lecture 19 Sorting
Lecture 20 Practice Activity Number 2
Lecture 21 Answer to Practice Activity 2
Section 4: Going deeper in our PivotTable
Lecture 22 Adding additional row fields
Lecture 23 Different layouts – Compact, Outline and Tabular
Lecture 24 Blank Rows
Lecture 25 Group Records
Lecture 26 Showing or hiding additional detail – the +/- buttons
Lecture 27 Adding columns
Lecture 28 Adding page fields
Lecture 29 Adding filters
Lecture 30 Changing source
Lecture 31 Using a table source
Lecture 32 Other PivotTable – Analyze options
Lecture 33 Introduction to Practice Activity 3
Lecture 34 Practice Activity Number 3
Lecture 35 Answers to Practice Activity 3
Section 5: Modify field selections
Lecture 36 Change aggregate (COUNT, MIN, MAX)
Lecture 37 Repeat All
Lecture 38 Using Conditional Formatting to hide Repeat All
Lecture 39 Grand Totals
Lecture 40 Adding subtotals
Lecture 41 Show items with no data
Lecture 42 Other field settings
Lecture 43 Practice Activity Number 4
Lecture 44 Answers to Practice Activity 4
Section 6: PivotTable Options
Lecture 45 Layout
Lecture 46 Totals & Filters, and Alt Text
Lecture 47 Display
Lecture 48 Print
Lecture 49 Data
Lecture 50 Practice Activity Number 5
Lecture 51 Answers to Practice Activity 5
Section 7: Slicers
Lecture 52 Create slicers
Lecture 53 Identifying impossible options, and selecting more than one option at once.
Lecture 54 Join to multiple PivotTables
Lecture 55 Options
Lecture 56 Practice Activity Number 6
Lecture 57 Answer to Practice Activity 6
Section 8: Dates and Timeline
Lecture 58 Aggregates
Lecture 59 Group by time period
Lecture 60 Timelines
Lecture 61 Practice Activity Number 7
Lecture 62 Answer to Practice Activity 7
Section 9: Formatting
Lecture 63 Formatting number values
Lecture 64 Custom Formatting – number formatting
Lecture 65 Custom Formatting – dates and text
Lecture 66 Custom Formatting – sections and colors
Lecture 67 Conditional Formatting – Highlighting cells
Lecture 68 Conditional Formatting – Data Bars and Color Sets
Lecture 69 Formatting printing
Lecture 70 Styles
Lecture 71 Practice Activity Number 8
Lecture 72 Answer to Practice Activity 8
Section 10: Advanced calculations
Lecture 73 Percentage of total
Lecture 74 Percentage of total – Advanced
Lecture 75 Cumulative totals
Lecture 76 Rank and Index
Lecture 77 Difference from
Lecture 78 Calculated fields
Lecture 79 Calculated Items
Section 11: PivotCharts
Lecture 80 Relationship of Pivot Chart with Pivot Table
Lecture 81 Options and Styles
Lecture 82 Bubble charts
Lecture 83 Formatting charts
Lecture 84 Adding legends
Lecture 85 Positioning and Resizing charts and graphs, inc. moving charts into chart sheets
Lecture 86 Modifying chart and graph parameters
Lecture 87 Applying chart layouts and styles
Lecture 88 Conclusion to Part 1
Section 12: Part 2 – Get and Transform Data – Home Part 1
Lecture 89 Introduction – let’s Get some more Data
Lecture 90 Exploring the Power Query Editor interface
Lecture 91 Introducing the M language
Lecture 92 Let’s start look at the Home tab
Lecture 93 Home menu – Manage Columns
Lecture 94 Home menu – Reduce Rows and Use First Row as Headers
Lecture 95 Practice Activity Number 9
Lecture 96 Practice Activity Number 9 – The Solution
Section 13: Get Data – Home Part 2
Lecture 97 Sort and Filter
Lecture 98 Split Column
Lecture 99 Other Transform activities
Lecture 100 Practice Activity Number 10
Lecture 101 Practice Activity Number 10 – The Solution
Section 14: Getting Multiple files – Don’t forget to create Visualizations.
Lecture 102 Merge Queries and Expand Table
Lecture 103 Different types of Joins
Lecture 104 Merge Queries with Group By
Lecture 105 Appending two queries together
Lecture 106 Appending three or more queries together + resolving a problem with data types
Lecture 107 Combine Files (getting information from a folder)
Lecture 108 Load To…
Lecture 109 Practice Activity Number 11
Lecture 110 Practice Activity Number 11 – The Solution
Section 15: Transform Menu
Lecture 111 Transform – Table and Any Column
Lecture 112 Pivot Column
Lecture 113 Unpivot
Lecture 114 Practice Activity Number 12
Lecture 115 Practice Activity 12 – The Solution
Lecture 116 Unpivot in conjunction with other Transform features
Lecture 117 Practice Activity Number 13
Lecture 118 Practice Activity 13 – The Solution
Section 16: Transform – Text and Numbers
Lecture 119 Transform/Add Column – Text – Format
Lecture 120 Transform/Add Column – Text – Merge Columns
Lecture 121 Transform/Add Column – Text – Extract
Lecture 122 Transform/Add Column – Text – Parse
Lecture 123 Transform/Add Column – Number Column – Statistics and Standard
Lecture 124 Transform/Add Column – Other Number Column functions
Lecture 125 Practice Activity Number 14
Lecture 126 Practice Activity 14 – The Solution
Section 17: Transform – Dates and Time
Lecture 127 Creating a list of dates
Lecture 128 Transform/Add Column – Date
Lecture 129 Transform/Add Column – Dates in other cultures/languages
Lecture 130 Transform/Add Column – Time
Lecture 131 Transform/Add column – Duration
Lecture 132 Practice Activity Number 15
Lecture 133 Practice Activity Number 15 – The Solution
Section 18: Add Columns, View and Help Menus
Lecture 134 Column from examples
Lecture 135 Conditional Column
Lecture 136 Resolving Errors from Conditional Columns
Lecture 137 Index Column and Duplicate Column
Lecture 138 Custom Column – If Then Else
Lecture 139 Practice Activity Number 16
Lecture 140 Practice Activity Number 16 – The Solution
Section 19: View and Help menus and advanced functionality
Lecture 141 Other M Functions
Lecture 142 View and Help menus
Lecture 143 Advanced Editor
Lecture 144 Custom Functions
Lecture 145 DateTimeZone date type and Functions
Lecture 146 Worked Practice Activity 17 – Dividing Annual data into Months
Section 20: Get other types of data
Lecture 147 Introduction to SQL Server
Lecture 148 Importing database data into Get and Transform, and Query Folding
Lecture 149 Expanding multiple tables in SQL Server
Lecture 150 Importing data from SQL Server Analysis Services (SSAS)
Lecture 151 Setting up Azure SQL Database
Lecture 152 Using Azure SQL Database in Get and Transform
Lecture 153 Using Big Data
Section 21: Part 3: Creating a Data Model
Lecture 154 Get multiple data sets
Lecture 155 Connecting multiple data sets together
Lecture 156 The problems with direction of relationships between data sets
Lecture 157 Practice Activity Number 18
Lecture 158 Practice Activity Number 18 – The Solution
Section 22: Developing the Data Model
Lecture 159 Filtering, sorting and formatting fields
Lecture 160 Hiding columns from Client Tools, and other column tools
Lecture 161 Refreshing (or processing) data
Lecture 162 Perspectives
Lecture 163 Creating quick measures
Lecture 164 Hierarchies
Lecture 165 Practice Activity Number 19
Lecture 166 Practice Activity Number 19 – The Solution
Section 23: Using your Data Model in PivotTable
Lecture 167 Non-Visual Totals
Lecture 168 Show no data in rows and columns
Lecture 169 Using formulas to query Data Model
Section 24: An introduction to DAX functions, including Logical functions
Lecture 170 DAX functions – A useful Resource
Lecture 171 Calculated columns – an introduction
Lecture 172 Basic operators
Lecture 173 IF, BLANK and ISBLANK
Lecture 174 AND, OR and NOT
Lecture 175 SWITCH
Lecture 176 Other functions
Lecture 177 Practice Activity Number 20
Lecture 178 Practice Activity Number 20 – The Solution
Section 25: Statistical functions
Lecture 179 Measures – an introduction, with standard aggregations including Countblank
Lecture 180 Aggregation of calculations
Lecture 181 Other statistical functions
Lecture 182 Practice Activity Number 21
Lecture 183 Practice Activity Number 21 – The Solution
Section 26: Mathematical functions
Lecture 184 Introduction to mathematical functions
Lecture 185 Rounding functions
Lecture 186 Division functions – MOD and QUOTIENT
Lecture 187 SIGN (and use with SWITCH) and ABS
Lecture 188 Exponential functions
Lecture 189 Other functions
Lecture 190 Practice Activity Number 22
Lecture 191 Practice Activity Number 22 – The Solution
Section 27: Text functions
Lecture 192 Text searching
Lecture 193 Text extraction and substitution
Lecture 194 Text conversion
Lecture 195 Other functions
Lecture 196 Practice Activity Number 23
Lecture 197 Practice Activity Number 23 – The Solution
Section 28: Information Functions
Lecture 198 ISERROR and LOOKUPVALUE
Lecture 199 Other functions
Lecture 200 Practice Activity Number 24
Lecture 201 Practice Activity Number 24 – The Solution
Section 29: Filter and Value Functions
Lecture 202 RELATED
Lecture 203 RELATEDTABLE and COUNTROWS
Lecture 204 Context
Lecture 205 ALL
Lecture 206 FILTER
Lecture 207 CALCULATE
Lecture 208 ALLEXCEPT
Lecture 209 ALLSELECTED
Lecture 210 Other functions
Lecture 211 Practice Activity Number 25
Lecture 212 Practice Activity Number 25 – The Solution
Section 30: Time Intelligence Functions
Lecture 213 Date and Time Functions
Lecture 214 Sort by Column
Lecture 215 FIRSTDATE, LASTDATE
Lecture 216 Start of… and End of…
Lecture 217 Previous… and Next…
Lecture 218 DATESINPERIOD
Lecture 219 DATESMTD, DATESQTD, DATESYTD, TOTALMTD, TOTALQTD, TOTALYTD
Lecture 220 Opening Balance and Closing Balance
Lecture 221 SAMEPERIODLASTYEAR and PARALLELPERIOD
Lecture 222 Other Time Intelligence Functions
Lecture 223 Practice Activity Number 26
Lecture 224 Practice Activity Number 26 – The Solution
Section 31: Well done
Lecture 225 Congratulations for finishing the course!
Lecture 226 Bonus Lecture
This reporting course is meant for anyone who has no prior, or only a little experience of Pivot Tables, Get and Transform, and Power Pivot.,No prior experience in reporting tools is required.,This course is probably not for you if you always know how to how to do the advanced functionality of Get and Transform and Power Pivots, although we will be looking at some more advanced topics, including the M and DAX languages.

Homepage

www.udemy.com/course/analyzing-and-visualizing-data-with-microsoft-excel-data-modeling/

Buy Premium From My Links To Get Resumable Support,Max Speed & Support Me

Links are Interchangeable – No Password – Single Extraction