Thanks @meenasudhanshu Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - th-cam.com/video/rtDlORLmjlE/w-d-xo.html Excel Green Marks - Error Checking - Best Practices - th-cam.com/video/XeLtlzd9lRY/w-d-xo.html Excel Best Practices - Part 1 of 3 - Data Management - th-cam.com/video/rr_1ha6g6lc/w-d-xo.html Excel Best Practices - Part 2 of 3 - Formulas - th-cam.com/video/kz_zAvMINAk/w-d-xo.html Excel Best Practices - Part 3 of 3 - Analytics - th-cam.com/video/JNR5yx_Pg4A/w-d-xo.html 10 Excel Settings You Must CHANGE! - th-cam.com/video/vXrrXdKyJFk/w-d-xo.html Automatic data clean up with Excel Flash Fill - th-cam.com/video/N3p_x_lXT_c/w-d-xo.html Instant Excel Audit, Comparison and Analysis - Inquire - th-cam.com/video/cDdvUZxOUis/w-d-xo.html Six powerful Excel Navigation Shortcuts - th-cam.com/video/bR-yMbGPq50/w-d-xo.html Handle millions of rows in Excel - Slow to fast - th-cam.com/video/93h7rRsLF7Y/w-d-xo.html Convert crosstab to tabular - Unpivot - Excel Power Query - th-cam.com/video/mTtOtZH-nNM/w-d-xo.html Cheers. Doc
Thanks @mcegirl4 Check out my other best practices videos as well. Efficient Tasks Management - th-cam.com/video/vLFpWVfUfQ8/w-d-xo.html Task Apps Comparison: th-cam.com/video/ViWJIzMPQZg/w-d-xo.html Teams Meeting - Agenda, Action Points, Notes - th-cam.com/video/k0t8_mNkMDw/w-d-xo.html Microsoft Teams - 15 Best Practices - th-cam.com/video/W8Ufx_znKxI/w-d-xo.html OneDrive Best Practices Part 1 - th-cam.com/video/D7ZrfphW4vo/w-d-xo.html Part 2 - th-cam.com/video/nFx6YQxc-b4/w-d-xo.html Smart and Effective Email - 5 powerful ways - th-cam.com/video/6NEFSMqHgQE/w-d-xo.html Outlook Calendar Best Practices : Part 1 - th-cam.com/video/GzsQnecjAQo/w-d-xo.html and Part 2 - th-cam.com/video/padKH8ys5Cs/w-d-xo.html OneNote - Best Practices - th-cam.com/video/m-4AY1cMi8s/w-d-xo.html Microsoft 365 Best Practices - th-cam.com/video/kVC_YcL5ObU/w-d-xo.html Cheers. Doc.
Absolutely excellent. I have already subscribed and liked. This is one of the biggest needs in Excel, in my view: We are often stuck with disparate data from different locations that we must combine via "connectors" between the tables. THEN, we can attack one data table and do analytics. Of course, you were trying to make this presentation efficient, but often the data needs to be cleaned up a bit before any merging can occur. I will see if you have a video on how to clean up all sorts of formatting/data problems. Just great.
Thanks @JohnYoga Here is how you do data clean up - 11 rules for clean data - input data - for Excel , Power BI or any other analytical tool - th-cam.com/video/GuAROFcKqM0/w-d-xo.html Data Cleanup - Cross-tab and Multiple headers - Rules 1, 5, 6, 10 - th-cam.com/video/rX83Bp7mndE/w-d-xo.html Cleaning hierarchical data - Rules 6 and 8 - th-cam.com/video/h9nSoOl1jiY/w-d-xo.html Crosstab cleanup th-cam.com/video/mTtOtZH-nNM/w-d-xo.htmlsi=DTvp68hJ5qJuxwCx Multiple header crosstab data clean up - Excel - Power Query - th-cam.com/video/ObvXeuTen-o/w-d-xo.html Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - th-cam.com/video/rtDlORLmjlE/w-d-xo.html Excel Green Marks - Error Checking - Best Practices - th-cam.com/video/XeLtlzd9lRY/w-d-xo.html Excel Best Practices - Part 1 of 3 - Data Management - th-cam.com/video/rr_1ha6g6lc/w-d-xo.html Excel Best Practices - Part 2 of 3 - Formulas - th-cam.com/video/kz_zAvMINAk/w-d-xo.html Excel Best Practices - Part 3 of 3 - Analytics - th-cam.com/video/JNR5yx_Pg4A/w-d-xo.html 10 Excel Settings You Must CHANGE! - th-cam.com/video/vXrrXdKyJFk/w-d-xo.html Automatic data clean up with Excel Flash Fill - th-cam.com/video/N3p_x_lXT_c/w-d-xo.html Instant Excel Audit, Comparison and Analysis - Inquire - th-cam.com/video/cDdvUZxOUis/w-d-xo.html Six powerful Excel Navigation Shortcuts - th-cam.com/video/bR-yMbGPq50/w-d-xo.html Handle millions of rows in Excel - Slow to fast - th-cam.com/video/93h7rRsLF7Y/w-d-xo.html Convert crosstab to tabular - Unpivot - Excel Power Query - th-cam.com/video/mTtOtZH-nNM/w-d-xo.html Cheers. Doc
Thanks @208935 Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - th-cam.com/video/rtDlORLmjlE/w-d-xo.html Excel Green Marks - Error Checking - Best Practices - th-cam.com/video/XeLtlzd9lRY/w-d-xo.html Excel Best Practices - Part 1 of 3 - Data Management - th-cam.com/video/rr_1ha6g6lc/w-d-xo.html Excel Best Practices - Part 2 of 3 - Formulas - th-cam.com/video/kz_zAvMINAk/w-d-xo.html Excel Best Practices - Part 3 of 3 - Analytics - th-cam.com/video/JNR5yx_Pg4A/w-d-xo.html 10 Excel Settings You Must CHANGE! - th-cam.com/video/vXrrXdKyJFk/w-d-xo.html Automatic data clean up with Excel Flash Fill - th-cam.com/video/N3p_x_lXT_c/w-d-xo.html Instant Excel Audit, Comparison and Analysis - Inquire - th-cam.com/video/cDdvUZxOUis/w-d-xo.html Six powerful Excel Navigation Shortcuts - th-cam.com/video/bR-yMbGPq50/w-d-xo.html Handle millions of rows in Excel - Slow to fast - th-cam.com/video/93h7rRsLF7Y/w-d-xo.html Convert crosstab to tabular - Unpivot - Excel Power Query - th-cam.com/video/mTtOtZH-nNM/w-d-xo.html Cheers. Doc
Thanks @GauravGupta-tp6ve Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - th-cam.com/video/rtDlORLmjlE/w-d-xo.html Excel Green Marks - Error Checking - Best Practices - th-cam.com/video/XeLtlzd9lRY/w-d-xo.html Excel Best Practices - Part 1 of 3 - Data Management - th-cam.com/video/rr_1ha6g6lc/w-d-xo.html Excel Best Practices - Part 2 of 3 - Formulas - th-cam.com/video/kz_zAvMINAk/w-d-xo.html Excel Best Practices - Part 3 of 3 - Analytics - th-cam.com/video/JNR5yx_Pg4A/w-d-xo.html 10 Excel Settings You Must CHANGE! - th-cam.com/video/vXrrXdKyJFk/w-d-xo.html Automatic data clean up with Excel Flash Fill - th-cam.com/video/N3p_x_lXT_c/w-d-xo.html Instant Excel Audit, Comparison and Analysis - Inquire - th-cam.com/video/cDdvUZxOUis/w-d-xo.html Six powerful Excel Navigation Shortcuts - th-cam.com/video/bR-yMbGPq50/w-d-xo.html Handle millions of rows in Excel - Slow to fast - th-cam.com/video/93h7rRsLF7Y/w-d-xo.html Convert crosstab to tabular - Unpivot - Excel Power Query - th-cam.com/video/mTtOtZH-nNM/w-d-xo.html Cheers. Doc
Thanks @syedaneesdurez8766 Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - th-cam.com/video/rtDlORLmjlE/w-d-xo.html Excel Green Marks - Error Checking - Best Practices - th-cam.com/video/XeLtlzd9lRY/w-d-xo.html Excel Best Practices - Part 1 of 3 - Data Management - th-cam.com/video/rr_1ha6g6lc/w-d-xo.html Excel Best Practices - Part 2 of 3 - Formulas - th-cam.com/video/kz_zAvMINAk/w-d-xo.html Excel Best Practices - Part 3 of 3 - Analytics - th-cam.com/video/JNR5yx_Pg4A/w-d-xo.html 10 Excel Settings You Must CHANGE! - th-cam.com/video/vXrrXdKyJFk/w-d-xo.html Automatic data clean up with Excel Flash Fill - th-cam.com/video/N3p_x_lXT_c/w-d-xo.html Instant Excel Audit, Comparison and Analysis - Inquire - th-cam.com/video/cDdvUZxOUis/w-d-xo.html Six powerful Excel Navigation Shortcuts - th-cam.com/video/bR-yMbGPq50/w-d-xo.html Handle millions of rows in Excel - Slow to fast - th-cam.com/video/93h7rRsLF7Y/w-d-xo.html Convert crosstab to tabular - Unpivot - Excel Power Query - th-cam.com/video/mTtOtZH-nNM/w-d-xo.html Cheers. Doc
Thanks @Sumanth1601 Yes. PBI and Excel should be taught, learnt and used together. They are adjuncts. Not separate compartments. Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - th-cam.com/video/rtDlORLmjlE/w-d-xo.html Excel Green Marks - Error Checking - Best Practices - th-cam.com/video/XeLtlzd9lRY/w-d-xo.html Excel Best Practices - Part 1 of 3 - Data Management - th-cam.com/video/rr_1ha6g6lc/w-d-xo.html Excel Best Practices - Part 2 of 3 - Formulas - th-cam.com/video/kz_zAvMINAk/w-d-xo.html Excel Best Practices - Part 3 of 3 - Analytics - th-cam.com/video/JNR5yx_Pg4A/w-d-xo.html 10 Excel Settings You Must CHANGE! - th-cam.com/video/vXrrXdKyJFk/w-d-xo.html Automatic data clean up with Excel Flash Fill - th-cam.com/video/N3p_x_lXT_c/w-d-xo.html Instant Excel Audit, Comparison and Analysis - Inquire - th-cam.com/video/cDdvUZxOUis/w-d-xo.html Six powerful Excel Navigation Shortcuts - th-cam.com/video/bR-yMbGPq50/w-d-xo.html Handle millions of rows in Excel - Slow to fast - th-cam.com/video/93h7rRsLF7Y/w-d-xo.html Convert crosstab to tabular - Unpivot - Excel Power Query - th-cam.com/video/mTtOtZH-nNM/w-d-xo.html Cheers. Doc
Thanks @sachindotel6005 Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - th-cam.com/video/rtDlORLmjlE/w-d-xo.html Excel Green Marks - Error Checking - Best Practices - th-cam.com/video/XeLtlzd9lRY/w-d-xo.html Excel Best Practices - Part 1 of 3 - Data Management - th-cam.com/video/rr_1ha6g6lc/w-d-xo.html Excel Best Practices - Part 2 of 3 - Formulas - th-cam.com/video/kz_zAvMINAk/w-d-xo.html Excel Best Practices - Part 3 of 3 - Analytics - th-cam.com/video/JNR5yx_Pg4A/w-d-xo.html 10 Excel Settings You Must CHANGE! - th-cam.com/video/vXrrXdKyJFk/w-d-xo.html Automatic data clean up with Excel Flash Fill - th-cam.com/video/N3p_x_lXT_c/w-d-xo.html Instant Excel Audit, Comparison and Analysis - Inquire - th-cam.com/video/cDdvUZxOUis/w-d-xo.html Six powerful Excel Navigation Shortcuts - th-cam.com/video/bR-yMbGPq50/w-d-xo.html Handle millions of rows in Excel - Slow to fast - th-cam.com/video/93h7rRsLF7Y/w-d-xo.html Convert crosstab to tabular - Unpivot - Excel Power Query - th-cam.com/video/mTtOtZH-nNM/w-d-xo.html Cheers. Doc
Thanks @ariewibowo4951 Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - th-cam.com/video/rtDlORLmjlE/w-d-xo.html Excel Green Marks - Error Checking - Best Practices - th-cam.com/video/XeLtlzd9lRY/w-d-xo.html Excel Best Practices - Part 1 of 3 - Data Management - th-cam.com/video/rr_1ha6g6lc/w-d-xo.html Excel Best Practices - Part 2 of 3 - Formulas - th-cam.com/video/kz_zAvMINAk/w-d-xo.html Excel Best Practices - Part 3 of 3 - Analytics - th-cam.com/video/JNR5yx_Pg4A/w-d-xo.html 10 Excel Settings You Must CHANGE! - th-cam.com/video/vXrrXdKyJFk/w-d-xo.html Automatic data clean up with Excel Flash Fill - th-cam.com/video/N3p_x_lXT_c/w-d-xo.html Instant Excel Audit, Comparison and Analysis - Inquire - th-cam.com/video/cDdvUZxOUis/w-d-xo.html Six powerful Excel Navigation Shortcuts - th-cam.com/video/bR-yMbGPq50/w-d-xo.html Handle millions of rows in Excel - Slow to fast - th-cam.com/video/93h7rRsLF7Y/w-d-xo.html Convert crosstab to tabular - Unpivot - Excel Power Query - th-cam.com/video/mTtOtZH-nNM/w-d-xo.html Cheers. Doc
Absolutely amazing demonstration, I am in the middle of learning both and this video is really helpful for my understanding of both tools better. Many thanks.
Thanks @azwarmzafar Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - th-cam.com/video/rtDlORLmjlE/w-d-xo.html Excel Green Marks - Error Checking - Best Practices - th-cam.com/video/XeLtlzd9lRY/w-d-xo.html Excel Best Practices - Part 1 of 3 - Data Management - th-cam.com/video/rr_1ha6g6lc/w-d-xo.html Excel Best Practices - Part 2 of 3 - Formulas - th-cam.com/video/kz_zAvMINAk/w-d-xo.html Excel Best Practices - Part 3 of 3 - Analytics - th-cam.com/video/JNR5yx_Pg4A/w-d-xo.html 10 Excel Settings You Must CHANGE! - th-cam.com/video/vXrrXdKyJFk/w-d-xo.html Automatic data clean up with Excel Flash Fill - th-cam.com/video/N3p_x_lXT_c/w-d-xo.html Instant Excel Audit, Comparison and Analysis - Inquire - th-cam.com/video/cDdvUZxOUis/w-d-xo.html Six powerful Excel Navigation Shortcuts - th-cam.com/video/bR-yMbGPq50/w-d-xo.html Handle millions of rows in Excel - Slow to fast - th-cam.com/video/93h7rRsLF7Y/w-d-xo.html Convert crosstab to tabular - Unpivot - Excel Power Query - th-cam.com/video/mTtOtZH-nNM/w-d-xo.html Cheers. Doc
Thanks @jaswinderbhatti3352 Check out my other best practices videos as well. Efficient Tasks Management - th-cam.com/video/vLFpWVfUfQ8/w-d-xo.html Task Apps Comparison: th-cam.com/video/ViWJIzMPQZg/w-d-xo.html Teams Meeting - Agenda, Action Points, Notes - th-cam.com/video/k0t8_mNkMDw/w-d-xo.html Microsoft Teams - 15 Best Practices - th-cam.com/video/W8Ufx_znKxI/w-d-xo.html OneDrive Best Practices Part 1 - th-cam.com/video/D7ZrfphW4vo/w-d-xo.html Part 2 - th-cam.com/video/nFx6YQxc-b4/w-d-xo.html Smart and Effective Email - 5 powerful ways - th-cam.com/video/6NEFSMqHgQE/w-d-xo.html Outlook Calendar Best Practices : Part 1 - th-cam.com/video/GzsQnecjAQo/w-d-xo.html and Part 2 - th-cam.com/video/padKH8ys5Cs/w-d-xo.html OneNote - Best Practices - th-cam.com/video/m-4AY1cMi8s/w-d-xo.html Microsoft 365 Best Practices - th-cam.com/video/kVC_YcL5ObU/w-d-xo.html Cheers. Doc.
If we use standard merge query, it results in nested join which is very slow, especially if both tables to be merged are large. It is better to switch to simple "Table.Join" than "Table.NestedJoin" if both tables are large sized.
Sure. Table.Join will get all fields. We need control over which fields are merged. Hence nested join is better. Secondly, in this type of scenario, one table - the master table - is usually small. Thirdly, to use Table.Join properly, you must choose the appropriate join algorithm. This is difficult to expect at a regular end user level. However, if both tables are very large, trying Table.Join is certainly a good idea.
Thanks @mogarrett3045 Yes. Power Query is the solution to enormous amount of time wasted every day in data clean up. Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - th-cam.com/video/rtDlORLmjlE/w-d-xo.html Excel Green Marks - Error Checking - Best Practices - th-cam.com/video/XeLtlzd9lRY/w-d-xo.html Excel Best Practices - Part 1 of 3 - Data Management - th-cam.com/video/rr_1ha6g6lc/w-d-xo.html Excel Best Practices - Part 2 of 3 - Formulas - th-cam.com/video/kz_zAvMINAk/w-d-xo.html Excel Best Practices - Part 3 of 3 - Analytics - th-cam.com/video/JNR5yx_Pg4A/w-d-xo.html 10 Excel Settings You Must CHANGE! - th-cam.com/video/vXrrXdKyJFk/w-d-xo.html Automatic data clean up with Excel Flash Fill - th-cam.com/video/N3p_x_lXT_c/w-d-xo.html Instant Excel Audit, Comparison and Analysis - Inquire - th-cam.com/video/cDdvUZxOUis/w-d-xo.html Six powerful Excel Navigation Shortcuts - th-cam.com/video/bR-yMbGPq50/w-d-xo.html Handle millions of rows in Excel - Slow to fast - th-cam.com/video/93h7rRsLF7Y/w-d-xo.html Convert crosstab to tabular - Unpivot - Excel Power Query - th-cam.com/video/mTtOtZH-nNM/w-d-xo.html Cheers. Doc
If tables are clean, then we can just create relationship which will add tables in Data model automatically. Coming thru Power Query keeps original files intact. 👍
Rights. Usually input data is never really clean. So Power Query needs to be used. But even input tables are clean, I always prefer to leave original data alone. If data is in Excel sheet and you add it to Data Model, it is occupying space twice. (Not exactly twice the amount of space. Data Model compresses it well. But still it is wasting space). Keep original files separate, do whatever you want in Power Query - a more modular and less error prone approach. In some cases, Excel itself encourages people to add local table to data model. That is not an optimal approach. Insert Pivot - From Range - It asks you to add to data model. Very bad idea. Learning Power Query is empowerment. Adding local data to data model is a disastrous bad habit. Of course in certain cases, there is justification for adding local data to Data Model. Small lookup tables which do not change often, for example. The key concept here is - Power Query gives you control over what goes inside Data Model. Add to Data Model does not give you any control at all. Therefore, always have PQ as a mediator.
Very informative and precise to the point.
Thanks @meenasudhanshu
Check out my popular Excel videos
How to enter and edit Excel Formulas - Back to Basics - th-cam.com/video/rtDlORLmjlE/w-d-xo.html
Excel Green Marks - Error Checking - Best Practices - th-cam.com/video/XeLtlzd9lRY/w-d-xo.html
Excel Best Practices - Part 1 of 3 - Data Management - th-cam.com/video/rr_1ha6g6lc/w-d-xo.html
Excel Best Practices - Part 2 of 3 - Formulas - th-cam.com/video/kz_zAvMINAk/w-d-xo.html
Excel Best Practices - Part 3 of 3 - Analytics - th-cam.com/video/JNR5yx_Pg4A/w-d-xo.html
10 Excel Settings You Must CHANGE! - th-cam.com/video/vXrrXdKyJFk/w-d-xo.html
Automatic data clean up with Excel Flash Fill - th-cam.com/video/N3p_x_lXT_c/w-d-xo.html
Instant Excel Audit, Comparison and Analysis - Inquire - th-cam.com/video/cDdvUZxOUis/w-d-xo.html
Six powerful Excel Navigation Shortcuts - th-cam.com/video/bR-yMbGPq50/w-d-xo.html
Handle millions of rows in Excel - Slow to fast - th-cam.com/video/93h7rRsLF7Y/w-d-xo.html
Convert crosstab to tabular - Unpivot - Excel Power Query - th-cam.com/video/mTtOtZH-nNM/w-d-xo.html
Cheers.
Doc
Thank you for your fabulous instruction on these concepts - tremendous help!
Thanks @mcegirl4
Check out my other best practices videos as well.
Efficient Tasks Management - th-cam.com/video/vLFpWVfUfQ8/w-d-xo.html
Task Apps Comparison: th-cam.com/video/ViWJIzMPQZg/w-d-xo.html
Teams Meeting - Agenda, Action Points, Notes - th-cam.com/video/k0t8_mNkMDw/w-d-xo.html
Microsoft Teams - 15 Best Practices - th-cam.com/video/W8Ufx_znKxI/w-d-xo.html
OneDrive Best Practices Part 1 - th-cam.com/video/D7ZrfphW4vo/w-d-xo.html Part 2 - th-cam.com/video/nFx6YQxc-b4/w-d-xo.html
Smart and Effective Email - 5 powerful ways - th-cam.com/video/6NEFSMqHgQE/w-d-xo.html
Outlook Calendar Best Practices : Part 1 - th-cam.com/video/GzsQnecjAQo/w-d-xo.html and Part 2 - th-cam.com/video/padKH8ys5Cs/w-d-xo.html
OneNote - Best Practices - th-cam.com/video/m-4AY1cMi8s/w-d-xo.html
Microsoft 365 Best Practices - th-cam.com/video/kVC_YcL5ObU/w-d-xo.html
Cheers. Doc.
Absolutely excellent. I have already subscribed and liked.
This is one of the biggest needs in Excel, in my view: We are often stuck with disparate data from different locations that we must combine via "connectors" between the tables. THEN, we can attack one data table and do analytics. Of course, you were trying to make this presentation efficient, but often the data needs to be cleaned up a bit before any merging can occur.
I will see if you have a video on how to clean up all sorts of formatting/data problems.
Just great.
Thanks @JohnYoga
Here is how you do data clean up -
11 rules for clean data - input data - for Excel , Power BI or any other analytical tool - th-cam.com/video/GuAROFcKqM0/w-d-xo.html
Data Cleanup - Cross-tab and Multiple headers - Rules 1, 5, 6, 10 - th-cam.com/video/rX83Bp7mndE/w-d-xo.html
Cleaning hierarchical data - Rules 6 and 8 - th-cam.com/video/h9nSoOl1jiY/w-d-xo.html
Crosstab cleanup th-cam.com/video/mTtOtZH-nNM/w-d-xo.htmlsi=DTvp68hJ5qJuxwCx
Multiple header crosstab data clean up - Excel - Power Query - th-cam.com/video/ObvXeuTen-o/w-d-xo.html
Check out my popular Excel videos
How to enter and edit Excel Formulas - Back to Basics - th-cam.com/video/rtDlORLmjlE/w-d-xo.html
Excel Green Marks - Error Checking - Best Practices - th-cam.com/video/XeLtlzd9lRY/w-d-xo.html
Excel Best Practices - Part 1 of 3 - Data Management - th-cam.com/video/rr_1ha6g6lc/w-d-xo.html
Excel Best Practices - Part 2 of 3 - Formulas - th-cam.com/video/kz_zAvMINAk/w-d-xo.html
Excel Best Practices - Part 3 of 3 - Analytics - th-cam.com/video/JNR5yx_Pg4A/w-d-xo.html
10 Excel Settings You Must CHANGE! - th-cam.com/video/vXrrXdKyJFk/w-d-xo.html
Automatic data clean up with Excel Flash Fill - th-cam.com/video/N3p_x_lXT_c/w-d-xo.html
Instant Excel Audit, Comparison and Analysis - Inquire - th-cam.com/video/cDdvUZxOUis/w-d-xo.html
Six powerful Excel Navigation Shortcuts - th-cam.com/video/bR-yMbGPq50/w-d-xo.html
Handle millions of rows in Excel - Slow to fast - th-cam.com/video/93h7rRsLF7Y/w-d-xo.html
Convert crosstab to tabular - Unpivot - Excel Power Query - th-cam.com/video/mTtOtZH-nNM/w-d-xo.html
Cheers.
Doc
Best explained ,Thanks sir
Thanks @208935
Check out my popular Excel videos
How to enter and edit Excel Formulas - Back to Basics - th-cam.com/video/rtDlORLmjlE/w-d-xo.html
Excel Green Marks - Error Checking - Best Practices - th-cam.com/video/XeLtlzd9lRY/w-d-xo.html
Excel Best Practices - Part 1 of 3 - Data Management - th-cam.com/video/rr_1ha6g6lc/w-d-xo.html
Excel Best Practices - Part 2 of 3 - Formulas - th-cam.com/video/kz_zAvMINAk/w-d-xo.html
Excel Best Practices - Part 3 of 3 - Analytics - th-cam.com/video/JNR5yx_Pg4A/w-d-xo.html
10 Excel Settings You Must CHANGE! - th-cam.com/video/vXrrXdKyJFk/w-d-xo.html
Automatic data clean up with Excel Flash Fill - th-cam.com/video/N3p_x_lXT_c/w-d-xo.html
Instant Excel Audit, Comparison and Analysis - Inquire - th-cam.com/video/cDdvUZxOUis/w-d-xo.html
Six powerful Excel Navigation Shortcuts - th-cam.com/video/bR-yMbGPq50/w-d-xo.html
Handle millions of rows in Excel - Slow to fast - th-cam.com/video/93h7rRsLF7Y/w-d-xo.html
Convert crosstab to tabular - Unpivot - Excel Power Query - th-cam.com/video/mTtOtZH-nNM/w-d-xo.html
Cheers.
Doc
Explained very well!
Got more clarity on how to mange data in data model.
Thanks @GauravGupta-tp6ve
Check out my popular Excel videos
How to enter and edit Excel Formulas - Back to Basics - th-cam.com/video/rtDlORLmjlE/w-d-xo.html
Excel Green Marks - Error Checking - Best Practices - th-cam.com/video/XeLtlzd9lRY/w-d-xo.html
Excel Best Practices - Part 1 of 3 - Data Management - th-cam.com/video/rr_1ha6g6lc/w-d-xo.html
Excel Best Practices - Part 2 of 3 - Formulas - th-cam.com/video/kz_zAvMINAk/w-d-xo.html
Excel Best Practices - Part 3 of 3 - Analytics - th-cam.com/video/JNR5yx_Pg4A/w-d-xo.html
10 Excel Settings You Must CHANGE! - th-cam.com/video/vXrrXdKyJFk/w-d-xo.html
Automatic data clean up with Excel Flash Fill - th-cam.com/video/N3p_x_lXT_c/w-d-xo.html
Instant Excel Audit, Comparison and Analysis - Inquire - th-cam.com/video/cDdvUZxOUis/w-d-xo.html
Six powerful Excel Navigation Shortcuts - th-cam.com/video/bR-yMbGPq50/w-d-xo.html
Handle millions of rows in Excel - Slow to fast - th-cam.com/video/93h7rRsLF7Y/w-d-xo.html
Convert crosstab to tabular - Unpivot - Excel Power Query - th-cam.com/video/mTtOtZH-nNM/w-d-xo.html
Cheers.
Doc
Sir Explained in very gud manner and easy to understand.
Looking forward for more tips on excel to make work more easier and work efficiently.
Thanks @syedaneesdurez8766
Check out my popular Excel videos
How to enter and edit Excel Formulas - Back to Basics - th-cam.com/video/rtDlORLmjlE/w-d-xo.html
Excel Green Marks - Error Checking - Best Practices - th-cam.com/video/XeLtlzd9lRY/w-d-xo.html
Excel Best Practices - Part 1 of 3 - Data Management - th-cam.com/video/rr_1ha6g6lc/w-d-xo.html
Excel Best Practices - Part 2 of 3 - Formulas - th-cam.com/video/kz_zAvMINAk/w-d-xo.html
Excel Best Practices - Part 3 of 3 - Analytics - th-cam.com/video/JNR5yx_Pg4A/w-d-xo.html
10 Excel Settings You Must CHANGE! - th-cam.com/video/vXrrXdKyJFk/w-d-xo.html
Automatic data clean up with Excel Flash Fill - th-cam.com/video/N3p_x_lXT_c/w-d-xo.html
Instant Excel Audit, Comparison and Analysis - Inquire - th-cam.com/video/cDdvUZxOUis/w-d-xo.html
Six powerful Excel Navigation Shortcuts - th-cam.com/video/bR-yMbGPq50/w-d-xo.html
Handle millions of rows in Excel - Slow to fast - th-cam.com/video/93h7rRsLF7Y/w-d-xo.html
Convert crosstab to tabular - Unpivot - Excel Power Query - th-cam.com/video/mTtOtZH-nNM/w-d-xo.html
Cheers.
Doc
Excellent content 👌 especially for beginner's..most of these concept are based on PBI, but Excel has this functionality too. Kudos ❤
Thanks @Sumanth1601
Yes. PBI and Excel should be taught, learnt and used together.
They are adjuncts. Not separate compartments.
Check out my popular Excel videos
How to enter and edit Excel Formulas - Back to Basics - th-cam.com/video/rtDlORLmjlE/w-d-xo.html
Excel Green Marks - Error Checking - Best Practices - th-cam.com/video/XeLtlzd9lRY/w-d-xo.html
Excel Best Practices - Part 1 of 3 - Data Management - th-cam.com/video/rr_1ha6g6lc/w-d-xo.html
Excel Best Practices - Part 2 of 3 - Formulas - th-cam.com/video/kz_zAvMINAk/w-d-xo.html
Excel Best Practices - Part 3 of 3 - Analytics - th-cam.com/video/JNR5yx_Pg4A/w-d-xo.html
10 Excel Settings You Must CHANGE! - th-cam.com/video/vXrrXdKyJFk/w-d-xo.html
Automatic data clean up with Excel Flash Fill - th-cam.com/video/N3p_x_lXT_c/w-d-xo.html
Instant Excel Audit, Comparison and Analysis - Inquire - th-cam.com/video/cDdvUZxOUis/w-d-xo.html
Six powerful Excel Navigation Shortcuts - th-cam.com/video/bR-yMbGPq50/w-d-xo.html
Handle millions of rows in Excel - Slow to fast - th-cam.com/video/93h7rRsLF7Y/w-d-xo.html
Convert crosstab to tabular - Unpivot - Excel Power Query - th-cam.com/video/mTtOtZH-nNM/w-d-xo.html
Cheers.
Doc
Loved the video, thank you for making 🙏
Thanks @sachindotel6005
Check out my popular Excel videos
How to enter and edit Excel Formulas - Back to Basics - th-cam.com/video/rtDlORLmjlE/w-d-xo.html
Excel Green Marks - Error Checking - Best Practices - th-cam.com/video/XeLtlzd9lRY/w-d-xo.html
Excel Best Practices - Part 1 of 3 - Data Management - th-cam.com/video/rr_1ha6g6lc/w-d-xo.html
Excel Best Practices - Part 2 of 3 - Formulas - th-cam.com/video/kz_zAvMINAk/w-d-xo.html
Excel Best Practices - Part 3 of 3 - Analytics - th-cam.com/video/JNR5yx_Pg4A/w-d-xo.html
10 Excel Settings You Must CHANGE! - th-cam.com/video/vXrrXdKyJFk/w-d-xo.html
Automatic data clean up with Excel Flash Fill - th-cam.com/video/N3p_x_lXT_c/w-d-xo.html
Instant Excel Audit, Comparison and Analysis - Inquire - th-cam.com/video/cDdvUZxOUis/w-d-xo.html
Six powerful Excel Navigation Shortcuts - th-cam.com/video/bR-yMbGPq50/w-d-xo.html
Handle millions of rows in Excel - Slow to fast - th-cam.com/video/93h7rRsLF7Y/w-d-xo.html
Convert crosstab to tabular - Unpivot - Excel Power Query - th-cam.com/video/mTtOtZH-nNM/w-d-xo.html
Cheers.
Doc
THANKSS SIR !!
Thanks @ariewibowo4951
Check out my popular Excel videos
How to enter and edit Excel Formulas - Back to Basics - th-cam.com/video/rtDlORLmjlE/w-d-xo.html
Excel Green Marks - Error Checking - Best Practices - th-cam.com/video/XeLtlzd9lRY/w-d-xo.html
Excel Best Practices - Part 1 of 3 - Data Management - th-cam.com/video/rr_1ha6g6lc/w-d-xo.html
Excel Best Practices - Part 2 of 3 - Formulas - th-cam.com/video/kz_zAvMINAk/w-d-xo.html
Excel Best Practices - Part 3 of 3 - Analytics - th-cam.com/video/JNR5yx_Pg4A/w-d-xo.html
10 Excel Settings You Must CHANGE! - th-cam.com/video/vXrrXdKyJFk/w-d-xo.html
Automatic data clean up with Excel Flash Fill - th-cam.com/video/N3p_x_lXT_c/w-d-xo.html
Instant Excel Audit, Comparison and Analysis - Inquire - th-cam.com/video/cDdvUZxOUis/w-d-xo.html
Six powerful Excel Navigation Shortcuts - th-cam.com/video/bR-yMbGPq50/w-d-xo.html
Handle millions of rows in Excel - Slow to fast - th-cam.com/video/93h7rRsLF7Y/w-d-xo.html
Convert crosstab to tabular - Unpivot - Excel Power Query - th-cam.com/video/mTtOtZH-nNM/w-d-xo.html
Cheers.
Doc
Absolutely amazing demonstration, I am in the middle of learning both and this video is really helpful for my understanding of both tools better. Many thanks.
Thanks @azwarmzafar
Check out my popular Excel videos
How to enter and edit Excel Formulas - Back to Basics - th-cam.com/video/rtDlORLmjlE/w-d-xo.html
Excel Green Marks - Error Checking - Best Practices - th-cam.com/video/XeLtlzd9lRY/w-d-xo.html
Excel Best Practices - Part 1 of 3 - Data Management - th-cam.com/video/rr_1ha6g6lc/w-d-xo.html
Excel Best Practices - Part 2 of 3 - Formulas - th-cam.com/video/kz_zAvMINAk/w-d-xo.html
Excel Best Practices - Part 3 of 3 - Analytics - th-cam.com/video/JNR5yx_Pg4A/w-d-xo.html
10 Excel Settings You Must CHANGE! - th-cam.com/video/vXrrXdKyJFk/w-d-xo.html
Automatic data clean up with Excel Flash Fill - th-cam.com/video/N3p_x_lXT_c/w-d-xo.html
Instant Excel Audit, Comparison and Analysis - Inquire - th-cam.com/video/cDdvUZxOUis/w-d-xo.html
Six powerful Excel Navigation Shortcuts - th-cam.com/video/bR-yMbGPq50/w-d-xo.html
Handle millions of rows in Excel - Slow to fast - th-cam.com/video/93h7rRsLF7Y/w-d-xo.html
Convert crosstab to tabular - Unpivot - Excel Power Query - th-cam.com/video/mTtOtZH-nNM/w-d-xo.html
Cheers.
Doc
Thanks again …. sir
Thanks @jaswinderbhatti3352
Check out my other best practices videos as well.
Efficient Tasks Management - th-cam.com/video/vLFpWVfUfQ8/w-d-xo.html
Task Apps Comparison: th-cam.com/video/ViWJIzMPQZg/w-d-xo.html
Teams Meeting - Agenda, Action Points, Notes - th-cam.com/video/k0t8_mNkMDw/w-d-xo.html
Microsoft Teams - 15 Best Practices - th-cam.com/video/W8Ufx_znKxI/w-d-xo.html
OneDrive Best Practices Part 1 - th-cam.com/video/D7ZrfphW4vo/w-d-xo.html Part 2 - th-cam.com/video/nFx6YQxc-b4/w-d-xo.html
Smart and Effective Email - 5 powerful ways - th-cam.com/video/6NEFSMqHgQE/w-d-xo.html
Outlook Calendar Best Practices : Part 1 - th-cam.com/video/GzsQnecjAQo/w-d-xo.html and Part 2 - th-cam.com/video/padKH8ys5Cs/w-d-xo.html
OneNote - Best Practices - th-cam.com/video/m-4AY1cMi8s/w-d-xo.html
Microsoft 365 Best Practices - th-cam.com/video/kVC_YcL5ObU/w-d-xo.html
Cheers. Doc.
If we use standard merge query, it results in nested join which is very slow, especially if both tables to be merged are large. It is better to switch to simple "Table.Join" than "Table.NestedJoin" if both tables are large sized.
Sure. Table.Join will get all fields. We need control over which fields are merged.
Hence nested join is better. Secondly, in this type of scenario, one table - the master table - is usually small.
Thirdly, to use Table.Join properly, you must choose the appropriate join algorithm. This is difficult to expect at a regular end user level.
However, if both tables are very large, trying Table.Join is certainly a good idea.
Power Query is the answer
Thanks @mogarrett3045
Yes. Power Query is the solution to enormous amount of time wasted every day in data clean up.
Check out my popular Excel videos
How to enter and edit Excel Formulas - Back to Basics - th-cam.com/video/rtDlORLmjlE/w-d-xo.html
Excel Green Marks - Error Checking - Best Practices - th-cam.com/video/XeLtlzd9lRY/w-d-xo.html
Excel Best Practices - Part 1 of 3 - Data Management - th-cam.com/video/rr_1ha6g6lc/w-d-xo.html
Excel Best Practices - Part 2 of 3 - Formulas - th-cam.com/video/kz_zAvMINAk/w-d-xo.html
Excel Best Practices - Part 3 of 3 - Analytics - th-cam.com/video/JNR5yx_Pg4A/w-d-xo.html
10 Excel Settings You Must CHANGE! - th-cam.com/video/vXrrXdKyJFk/w-d-xo.html
Automatic data clean up with Excel Flash Fill - th-cam.com/video/N3p_x_lXT_c/w-d-xo.html
Instant Excel Audit, Comparison and Analysis - Inquire - th-cam.com/video/cDdvUZxOUis/w-d-xo.html
Six powerful Excel Navigation Shortcuts - th-cam.com/video/bR-yMbGPq50/w-d-xo.html
Handle millions of rows in Excel - Slow to fast - th-cam.com/video/93h7rRsLF7Y/w-d-xo.html
Convert crosstab to tabular - Unpivot - Excel Power Query - th-cam.com/video/mTtOtZH-nNM/w-d-xo.html
Cheers.
Doc
If tables are clean, then we can just create relationship which will add tables in Data model automatically. Coming thru Power Query keeps original files intact. 👍
Rights. Usually input data is never really clean. So Power Query needs to be used.
But even input tables are clean, I always prefer to leave original data alone.
If data is in Excel sheet and you add it to Data Model, it is occupying space twice.
(Not exactly twice the amount of space. Data Model compresses it well. But still it is wasting space).
Keep original files separate, do whatever you want in Power Query - a more modular and less error prone approach.
In some cases, Excel itself encourages people to add local table to data model. That is not an optimal approach.
Insert Pivot - From Range - It asks you to add to data model. Very bad idea. Learning Power Query is empowerment.
Adding local data to data model is a disastrous bad habit.
Of course in certain cases, there is justification for adding local data to Data Model.
Small lookup tables which do not change often, for example.
The key concept here is - Power Query gives you control over what goes inside Data Model. Add to Data Model does not give you any control at all. Therefore, always have PQ as a mediator.