![ِAAA Excel English](/img/default-banner.jpg)
- 45
- 73 639
ِAAA Excel English
Приєднався 22 жов 2021
This is my new English dedicated channel; I have been posting Excel and Power BI videos for more than 4 years in both Arabic and English languages, now I decided to separate my English content from my Arabic content, please subscribe and stay tuned for high quality content about Power Query, Power Pivot, Power BI, Dynamic Array and much more
PT06.1| Excel Pivot Tables | Generate “GetPivotData” Function
#excel #pivottables
--------------------------------------------------------------------
Visit the website for more content: www.aaa-power.net
--------------------------------------------------------------------
Content:
0:00 Intro
1:09 Formulas outside pivot tables
3:06 Generate “GetPivotData” Function
4:38 Understand & Manipulate "GetPivotData"
Excel Data Analysis Series: PT- Pivot Tables
Calculations Outside Pivot Table - A Complement to Video PT04
- Activate “Get Pivot Table” Formula
- How GetPivotData formula works
- Copy & Drag “Get Pivot Table Formula”
- Impact on the calculation when “Pivot Table” layout changed
--------------------------------------------------------------------
To download the excel file:
drive.google.com/open?id=1gDKgalFjL_DKAnHqEyH_BD-xWVVW1ICx
--------------------------------------------------------------------
To watch Video PT04:
ua-cam.com/video/k7NwQKWfD-M/v-deo.html
--------------------------------------------------------------------
To subscribe:
ua-cam.com/channels/AxOgfPN8XqlB-lGmORVB5g.html
---------------------------------------------------------------------
To watch the full Playlist: PT (Pivot Tables)
ua-cam.com/play/PL5NlUYOM2iOgw8iAgG-DagodGryYSVtEA.html
---------------------------------------------------------------------
To follow on Facebook:
excelispower
---------------------------------------------------------------------
To follow on LinkedIn:
www.linkedin.com/in/amratef
--------------------------------------------------------------------
Visit the website for more content: www.aaa-power.net
--------------------------------------------------------------------
Content:
0:00 Intro
1:09 Formulas outside pivot tables
3:06 Generate “GetPivotData” Function
4:38 Understand & Manipulate "GetPivotData"
Excel Data Analysis Series: PT- Pivot Tables
Calculations Outside Pivot Table - A Complement to Video PT04
- Activate “Get Pivot Table” Formula
- How GetPivotData formula works
- Copy & Drag “Get Pivot Table Formula”
- Impact on the calculation when “Pivot Table” layout changed
--------------------------------------------------------------------
To download the excel file:
drive.google.com/open?id=1gDKgalFjL_DKAnHqEyH_BD-xWVVW1ICx
--------------------------------------------------------------------
To watch Video PT04:
ua-cam.com/video/k7NwQKWfD-M/v-deo.html
--------------------------------------------------------------------
To subscribe:
ua-cam.com/channels/AxOgfPN8XqlB-lGmORVB5g.html
---------------------------------------------------------------------
To watch the full Playlist: PT (Pivot Tables)
ua-cam.com/play/PL5NlUYOM2iOgw8iAgG-DagodGryYSVtEA.html
---------------------------------------------------------------------
To follow on Facebook:
excelispower
---------------------------------------------------------------------
To follow on LinkedIn:
www.linkedin.com/in/amratef
Переглядів: 325
Відео
PT05: Pivot Tables | generate hundreds of reports with 1 click | Report Filter Pages
Переглядів 25821 день тому
#excel #pivottables Visit the website for more content: www.aaa-power.net Content: 0:00 Intro 1:25 Accounts Receivable template 5:20 Customer Statement using Pivot Tables 8:18 tens of reports with one click 11:27 Update all reports instantly with new data Refreshable Accounts Receivable System Using : Formulas & Pivot Tables Best practical example for Accountants, specially who is working on Ac...
PT04: Excel Pivot Tables | Calculated Fields & Items | formulas inside the Pivot Table
Переглядів 457Місяць тому
#excel #pivottables Visit the website for more content: www.aaa-power.net Content: 0:00 Intro 0:58 Calculated Fields 10:42 Calculated Items To download the excel file: drive.google.com/open?id=1fzTsHyr-ujLy8nKCFZfIqhoLzI42PHAa To subscribe: ua-cam.com/channels/AxOgfPN8XqlB-lGmORVB5g.html To follow on Facebook: excelispower To follow on LinkedIn: www.linkedin.com/in/amratef
PT03.3 | Grouping of text fields | Categorize Data & Minimize File Size | Excel Pivot Tables
Переглядів 456Місяць тому
#excel #pivottables Visit the website for more content: www.aaa-power.net Content: 0:00 Intro 0:58 Use of Helper Columns 3:03 Categorize Using Pivot Tables 7:43 Close To Download the excel files: 1) Grouping using Pivot Tables: drive.google.com/open?id=1XNFSaDQ6ABmIHHLK1Jszv7oRjHRuu9WP 2) Grouping using helper columns: drive.google.com/open?id=1bjCAPj43zTzydl5RqpDzRVcYA8VD6DZt To subscribe: ua-...
Power BI (DAX) | Calendar Year vs Fiscal Year | FISCAL Date Table
Переглядів 3322 місяці тому
Visit my website: aaa-power.net Content: 0:00 intro 0:54 Example 3:46 CALENDARAUTO to Create a Date Table 5:40 Specify Fiscal year end 7:03 Month Name, Calendar & Fiscal Month # 11:36 Calendar & Fiscal Quarter # 14:59 Calendar & Fiscal Year # 20:19 Integrate the Date Table 22:07 Report according to Calendar & Fiscal years 24:30 Close To download the working files: drive.google.com/drive/folders...
PT03.2| Group Numeric Value | Sales Brackets&Performance Evaluation | Excel Pivot Tables
Переглядів 3702 місяці тому
#excel #pivottables Visit the website for more content: www.aaa-power.net Content: 0:00 Intro 0:54 Report sales buckets 5:58 Evaluate performance To download the excel files: drive.google.com/open?id=13fNKuxCX412UppBvrsSFxLOKgPUM6fJP To subscribe: ua-cam.com/channels/AxOgfPN8XqlB-lGmORVB5g.html To follow on Facebook: excelispower To follow on LinkedIn: www.linkedin.com/in/amratef
APQ16 | High speed Data Refresh | Table.Buffer | Advanced Power Query
Переглядів 2,6 тис.2 місяці тому
#excel #exceltutorial #powerquery #m_code Visit the website: www.aaa-power.net Chapters: 0:00 Intro 0:39Running Total | List.Sum / List.FirstN 4:35 Refresh before using Table.Buffer 5:42 Refresh after using Table.Buffer 7:15 Close To watch the full Playlist ua-cam.com/play/PL5NlUYOM2iOjRsboi_z9cHTyM7yqwWu58.html To download working files: drive.google.com/drive/folders/1uL_6LqPZPSWAgA60h5x-bjYs...
PT03.1| Grouping of Dates | weekly, monthly & quarterly reports | Excel Pivot Tables
Переглядів 4002 місяці тому
#excel #pivottables Visit the website for more content: www.aaa-power.net Content: 0:00 Intro 0:37 Group dates by month and quarter 4:32 Independently group 2 pivot tables 6:06 Group dates by number of days To download the excel files: drive.google.com/open?id=13kn5EokFbF5ijY9FxYZXmVq-iC-vuTds To subscribe: ua-cam.com/channels/AxOgfPN8XqlB-lGmORVB5g.html To follow on Facebook: exce...
APQ15 | How "each" & "_" work? | Advanced Power Query
Переглядів 3,1 тис.2 місяці тому
#excel #exceltutorial #powerquery #m_code Visit the website: www.aaa-power.net Chapters: 0:00 Intro 1:01 Shorthand for a custom function & an item in a list 5:42 Record in a table 9:19 Based on context 15:14 Table in a group of tables 18:15 With group by (Table.Group) 22:35 Close To watch the full Playlist ua-cam.com/play/PL5NlUYOM2iOjRsboi_z9cHTyM7yqwWu58.html drive.google.com/drive/folders/1s...
PT02: Pivot Tables | Refresh & Update New Data | Excel
Переглядів 2802 місяці тому
#excel #pivottables Visit the website for more content: www.aaa-power.net Content: 0:00 Intro 0:50 Change values inside Pivot Table data set 3:58 Copy and change Pivot Tables design 4:51 Add column/change column name in a Pivot Tables 7:29 Use named ranges with Pivot Tables 10:23 Use excel tables with Pivot Tables To download the excel files: drive.google.com/open?id=1XskUKxBl9XrF02En4PoN1Ckarc...
APQ14 | The easiest way to master “M” functions | Advanced Power Query
Переглядів 1,9 тис.3 місяці тому
#excel #exceltutorial #powerquery #m_code Visit the website: www.aaa-power.net Chapters: 0:00 Intro 0:58 Example Overview 2:17 M Language Part I: Date & Text Functions 11:14 M Language Part II: Table Functions 19:5 Close To watch the full Playlist ua-cam.com/play/PL5NlUYOM2iOjRsboi_z9cHTyM7yqwWu58.html To download the working files: drive.google.com/drive/folders/1nyZKbDPZJLXqhtqQ7GCxHaNFLmXoDg...
PT01| Pivot Tables Basics | Excel | Create, Design & Layout
Переглядів 3203 місяці тому
#excel #pivottables Visit the website for more content: www.aaa-power.net Content: 0:00 Intro 0:41 Proper Data Sets 3:24 Create a pivot table 4:58 Desing the report using pivot table fields window 10:38 Pivot table layout & style To download the excel files: drive.google.com/open?id=1tPfmvWclMZgnBypq83giREo7AtRFoGQj To subscribe: ua-cam.com/channels/AxOgfPN8XqlB-lGmORVB5g.html To follow on Face...
Stop 🛑using PIVOT TABLES Immediately | GROUPBY & PIVOTBY | Excel Worksheet Functions
Переглядів 5693 місяці тому
#excel #excelfunction Visit the website for more content: www.aaa-power.net Content: 0:00 Intro 1:01 Activate Beta Channel 2:06 GROUPBY function 8:35 Table Reference instead of Cell Reference 10:47 PIVOTBY function 13:30 Close To download the excel files: drive.google.com/drive/folders/1BplxBQd1CHzenZS16HwrrcqL4cQTBiBh?usp=drive_link To subscribe: ua-cam.com/channels/AxOgfPN8XqlB-lGmORVB5g.html...
PivotTables to Replace VLOOKUP | PowerPivot & Data Model | Relations & DAX Measures
Переглядів 4003 місяці тому
#excel #exceltutorial #powerpivot #dax Visit the website: www.aaa-power.net Auto detect / create relations between tables - Data Model Create a pivot table from multiple excel tables - Data Model Create DAX measures (DAX Formulas) SUMX - DAX iterator functions. RELATED - DAX function to replace VLOOKUP Number formatting attached to the formula (DAX Measures) Use same measure over and over with ...
APQ13 | M-Code Functions | List.Split | List.Zip | Advanced Power Query
Переглядів 2,4 тис.3 місяці тому
#excel #exceltutorial #powerquery #m_code Visit the website: www.aaa-power.net Chapters: 0:00 Intro 0:49 Example Overview 1:47 Solution: M-Code Functions (List.Split - List.Zip - Table.FromColumns) 8:42 Solution: Worksheet Functions (WRAPROWS - VSTACK) 11:40 Close To watch the full Playlist ua-cam.com/play/PL5NlUYOM2iOjRsboi_z9cHTyM7yqwWu58.html To download the working files: drive.google.com/d...
Waterfall chart 📊 (bridge) made easy with excel 365
Переглядів 2143 місяці тому
Waterfall chart 📊 (bridge) made easy with excel 365
Power Pivot (Part II) | Create calculation (MEASURE) inside Pivot Tables | DAX-SUM function
Переглядів 3603 місяці тому
Power Pivot (Part II) | Create calculation (MEASURE) inside Pivot Tables | DAX-SUM function
Excel Functions | FILTER & XLOOKUP For Approximate Match | Search Price & Discount in Changing Lists
Переглядів 3994 місяці тому
Excel Functions | FILTER & XLOOKUP For Approximate Match | Search Price & Discount in Changing Lists
Power Pivot | Create a Pivot Table from multiple tables | Actual vs Budget | One to Many Relations
Переглядів 9554 місяці тому
Power Pivot | Create a Pivot Table from multiple tables | Actual vs Budget | One to Many Relations
APQ12 | Exact & Approximate Match | Lookup changing Prices & Discount | Advanced Power Query
Переглядів 1,7 тис.4 місяці тому
APQ12 | Exact & Approximate Match | Lookup changing Prices & Discount | Advanced Power Query
PT08: PivotTables Present & Visualize | Part IV: Dashboards
Переглядів 3344 місяці тому
PT08: PivotTables Present & Visualize | Part IV: Dashboards
PT08 Part III: Pivot Tables - Slicers & Timelines
Переглядів 1934 місяці тому
PT08 Part III: Pivot Tables - Slicers & Timelines
APQ11 | Merge Quires - Next Level |Merge | M-Code | Custom Column | Records | Advanced Power Query
Переглядів 6 тис.4 місяці тому
APQ11 | Merge Quires - Next Level |Merge | M-Code | Custom Column | Records | Advanced Power Query
PT08 Part II : Pivot Charts without pivot tables & charts templates
Переглядів 2714 місяці тому
PT08 Part II : Pivot Charts without pivot tables & charts templates
PQC09 | Flex Filter | M-CODE & User Interface | Power Query Challenge Solution
Переглядів 1,8 тис.4 місяці тому
PQC09 | Flex Filter | M-CODE & User Interface | Power Query Challenge Solution
PQC09 | Power Query Challenge | Flex Filter
Переглядів 3635 місяців тому
PQC09 | Power Query Challenge | Flex Filter
Excel | Frequency Distribution | Pivot Table - Histogram - Frequency Function - Power Query
Переглядів 6145 місяців тому
Excel | Frequency Distribution | Pivot Table - Histogram - Frequency Function - Power Query
Excel | Foreign Exchange Rate History | STOCKHISTORY function
Переглядів 1545 місяців тому
Excel | Foreign Exchange Rate History | STOCKHISTORY function
λ LAMBDA explained | Create custom, reusable functions with friendly names
Переглядів 47911 місяців тому
λ LAMBDA explained | Create custom, reusable functions with friendly names
PQC08 | Power Query Challenge Solution | Change Table Layout | M-CODE
Переглядів 1,1 тис.Рік тому
PQC08 | Power Query Challenge Solution | Change Table Layout | M-CODE
you are a wonderful teacher and with a knack of simplifying complex opics. learnt so much from this series. hope u will update this series with more videos covering many more dax functions. Do you have a similar video series on Power BI
Great
Learning about Excel and pivot tables has non end ! Thanks to you !
Excellent, clear explanations. Many thanks.
Excellent 🌹🌹
Awesome Video, I got something new.. Keep creating more videos... ❤
Great Video, I learnt new thing, Thanks for creating such wonderful video.
Great video, masterful explanation. Greetings and thanks
Great example of Table.Buffer, something I've never exploited but after this video I really need to apply it more often in my solutions. Thank you for sharing and explaining it in a simple way!
Thank you for your comment 👍
Awesome video!.. Thank you for shaning...
Most welcome 🙏
i have noticed in the measure calculating gm is being divided by 1000000... any specific reason.. thanks for your clariification
Outstanding! Thank you.
You’re welcome ☺️
Thanks as always. A quick one. What should we do if the refresh time in the PQ Editor is taking too long? This is happening to me right now and I have so many qurries in one sheet
If qty equal to monthly qty it is correct
tks
Welcome 🙏
EXCELENTE curso! Gracias"
Thank you 🙏
🙏🙏🙏
👍👍
Excel-lent. Thank you very much for sharing this.
Most welcome 🙏
So glad I found your channel - been struggling with this issue for a while and this is beautiful solution
you are absolutely the best...
Hello, thanks for the video. Could you please add the link to the video where you apply formulas (Act vs Budget) to calculate variance? Thank you
Thanks a lot for the wonderful tutorial. One question, my FY begins and falls in the middle of the month, viz., mid July to mid-July. Occasionally the date varies such as 15 or 16 July and ends 365 days after. Any solution for this situation?
This is an interesting video, thank you ! I think you should have explained also the disadvantages of using Table.Buffer function, people must know it is not universally advantageous. Table.Buffer uses a certain amount of memory (not entire memory), if that amount is not sufficient (for very large amount of data, i.e. tens of thousands of rows + tens of columns at least), then data on top of allocated memory will be written on hard drive, which could make the reading/refreshing VERY slow, so the apparent advantages are gone. I guess that very fast hard disks (such as SSD) could not have this issue, but it shall be trialled. Thank you !
Great as always. Thanks
Thank you 🙏
Great again, keep bringing these techniques to ease in understand pivot tables better.
Thank you, sure will do my best
Great topic, thank you 😊. I wonder, for the inner table your can’t use “_” as the variable instead of “it” because the Table.AddColumn “each” is like an implicit “each _” so that variable is already taken up for the outer table, correct? So at 16:47 the outer tables “[Total Quantity]” could also be called “_[Total Quantity]” with no difference, correct? Thank you 🙏🏽.
Loved it
Thank you sir Realistic and informative example which will help us real life work. Thank you again
Thank you for showing the trick of using each with records to extract multiple values 🙏. At 18:03, Table.Sorts comparison requirement as a list is surprising given it says “as any”; it would be much appreciated if a video was made going over how to know when a function parameter requires a list or record, even when it doesn’t say. Or maybe it just needs to be memorized? Thank you.
Final I've been here
Fantastic, only 2 to go
I'm in Progress. :D love it
Great 👍
It's exact what all of us want in PQ. I'm here, My man
Good progress, keep going
I've been here and let you know. I'm still confused about how and when to use table, list and record in power query
I actualy love your tutos ! Thanks a lot !
Happy to hear, thank you 🙏
Happy sir now regular videos. Very useful thanks
Happy that you found it useful
Thanks for sharing such a nice series of Videos in a simple way, it would be helpful for person like me who do not have resources to purchase entire course. Thanks!
Glad that you get benefits out of it
super sir
Thank you 🙏
Thank you very much, Sir!
My pleasure
Very useful. Thank you
Most welcome 🙏
Brilliant work, I was spending full day today thinking for the same solution. Thanks for your support
Happy that you found it useful
So, if we notice there is some delay in our query, we use Table.Buffer then . Thanks
Exactly 👍
Thank you for the information! I will include the Table Buffer in my report - your assistance has been instrumental in enhancing my efficiency and productivity!
Happy to hear so 👍
super video, Thanks
You’re welcome
your effort is highly welcomed. Next Level of performance boost would be the reduction of redundant calculations. I edited your Query on the last 2 steps and so you can stay below 1 second refreshtime. let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Amount", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), TableBuffer = Table.Buffer( #"Added Index")[Amount], RT_Values = List.Accumulate(List.Skip( TableBuffer, 1 ) & {0}, [RunningTotal = {}, CurrentValue = TableBuffer{0}], (state, current) => [CurrentValue = List.Sum( {state[CurrentValue] , current } ), RunningTotal = state[RunningTotal] & { state[CurrentValue] }] )[RunningTotal], ReturnTable = Table.FromColumns(Table.ToColumns(#"Added Index") & { Value.ReplaceType(RT_Values, type {Int64.Type})}, Table.ColumnNames(#"Added Index") & {"Running Total"}) in ReturnTable
Fantastic explanation. Thanks from Portugal.
Thank you, greetings from Egypt 🇪🇬
Fantastic
Thank you 🙏
Thanks
Welcome 🙏
great video, greetings and thanks Master.
You’re most welcome dear
Just fantastic tutorial
Thank you 🙏
thank you
You’re welcome