Excel Power Trick - Save Time Without VBA by theexcelclub

View this thread on steempeak.com
· @theexcelclub · (edited)
$5.32
Excel Power Trick - Save Time Without VBA
In the video below you will learn an Excel Power Trick, using Excels Power Query,  to transform standard Excel data.  This trick promises to save you time month after month.

If you are new to Excels Power Tools (Power Pivot, Power Query and Power View), after you finished this example, you should really <a href="https://theexcelclub.com/excel-power-tools-the-future-of-excel/">take a look at this</a>, I know you will love it!.

But first lets take a look at the problem we are going to solve today.  If you scroll down further you can download this data and practice along.
<h3>The Problem</h3>
How often have you seen an Excel spreadsheet like the one below?  Very often, right?  I am sure many of you are even thinking ‘ What’s the problem with that spreadsheet?’

<img src="https://theexcelclub.com/wp-content/uploads/2016/12/excel-1024x576.png" alt="The Excel Club" width="789" height="444" /><br/>

However, I know that some of you can spot the problems straight away!

This matrix style reporting is common in Excel. It contains simple row headers and column headers to identify the elements of the matrix. In this worksheet we have a different for matrix for each sales rep.

But what if you wanted to analyse your report further or presented in a different way? Using visualizations instead of a table of data such as the image below. How would you go from A (the raw data above) to B (the visualization below)  every month as quick as possible?

<img src="https://theexcelclub.com/wp-content/uploads/2016/12/powequeryexample-Time-0_00_1427-1024x576.png" alt="Excel Power Trick" width="852" height="479" /><br/>
<h3>Long winded solutions</h3>
A lot, if not most of the work is transforming the structure of the worksheet. The matrix for each rep needs to be combined, right?

So you could remove the Page title header.  Then remove the total rows and also removed blank rows.  You would probable fill down the rep and now you would have a combined matrix table of data. Then add some totals. You could create a graph from this combined matrix table of data. But because the formatting is not correct, you would have problems adding slicers.

Assuming we are happy with the graph, the following month we would do the same tasks again when the new file is emailed over. A lot of work right!

Or you could create a table from each rep and then use these tables as a different series within a chart.  Again you would have problems with adding slicers because of the data formatting.  Then update the tables with new data each month from the new file emailed over?

Which method would you have used?

Do you have a better suggestion?

Why don’t you download the data and have a try?  <strong><a href="https://www.dropbox.com/s/iltdynals6fgcyn/NovTYD.xlsx?dl=0">Download data for Excel Power Trick here</a></strong>

For years now people have been creating VBA macros to clean up reports and carry out repetitive tasks. Exactly like the one above.  Power Query, known as Get and Transform data in Excel 2016 and later, has the ability to replace VBA macros for many transformation, formatting and data cleansing tasks.  You can then save this query and use it over and over as necessary.
<h2>Excel Power Trick - Time saving solution</h2>
In this Excel Power Trick, you will learn, step by step, how you can create a query to transform data structured in a format that is not efficient for reporting. You will also learn how you can save that query so it can be used again month on month.

<iframe src="https://www.youtube.com/embed/kIzjJ8ZMQcQ" width="560" height="315" frameborder="0" allowfullscreen="allowfullscreen" data-mce-fragment="1"></iframe>
<h2>Learn and Earn Activity</h2>
Earn tokens for answering the questions below:  You can use these token against the price of our courses or on other products and services that accept them.  Read more about our <a href="https://theexcelclub.com/learn-and-earn-steem-activities/">Learn and Earn activities here</a>.
<ul>
 <li>Could this be the beginning of the end of VBA macros in Excel? Comment below and tell me what you think.</li>
 <li>In the comments below, provide an alternative solution to create the chart &amp; slicers from the raw data and earn rewards</li>
 <li>I have omitted a step in error in this video.  this has caused a double count in the final pivot table.  Can you spot the error?  detail below</li>
</ul>
<strong><em>Do you want to start collecting rewards quickly for learning Excel?  Then you should try:</em></strong>

<a href="https://theexcelclub.com/excel-learn-earn/">10+ Excel Learn and Earn Activities YOU can do Today</a>
<h4><center>SIGN UP FOR OUR NEWSLETTER AND GET EXCEL &amp; POWER BI TIPS TRICKS AND TUTORIAL TO YOUR INBOX</center></h4>
<a role="button" href="https://theexcelclub.newzenler.com/f/email-signup">SIGN UP NOW</a>
<h3><center>Take A FREE course with us Today!</center></h3>
<a href="https://courses.theexcelclub.com/courses/practical-beginner-excel-365"><img src="https://theexcelclub.com/wp-content/uploads/2019/05/beginner-excel-free-training-course-257x300.png" alt="" width="257" height="300" /><br/></a>
<center>The Ultimate Excel Formulas Course</center></h3>
* includes XLOOKUP and will soon include Dynamic Arrays

<a href="https://courses.theexcelclub.com/courses/the-ultimate-excel-formulas-course-excel-365"><img src="https://theexcelclub.com/wp-content/uploads/2019/09/excel-365-formulas-course-254x300.png" alt="" width="254" height="300" /><br/></a>
<center>Become a Power Pivot Hero</center></h3>
<a href="https://courses.theexcelclub.com/courses/power-pivot-excel-365-from-zero-to-hero" data-elementor-open-lightbox=""><img src="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?fit=441%2C513&amp;ssl=1" sizes="(max-width: 441px) 100vw, 441px" srcset="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?w=441&amp;ssl=1 441w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=258%2C300&amp;ssl=1 258w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=410%2C477&amp;ssl=1 410w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=100%2C116&amp;ssl=1 100w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=275%2C320&amp;ssl=1 275w" alt="Power Pivot online training course" width="441" height="513" data-lazy-loaded="1" /><br/></a>
<center>GET and TRANSFORM DATA like a PRO</center></h3>
<strong>Power Query Excel 365</strong>

<a href="https://courses.theexcelclub.com/courses/power-query-excel-365-get-and-transform-data" data-elementor-open-lightbox=""><img src="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/10/power-query.png?fit=432%2C455&amp;ssl=1" sizes="(max-width: 432px) 100vw, 432px" srcset="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/10/power-query.png?w=432&amp;ssl=1 432w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/10/power-query.png?resize=285%2C300&amp;ssl=1 285w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/10/power-query.png?resize=410%2C432&amp;ssl=1 410w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/10/power-query.png?resize=100%2C105&amp;ssl=1 100w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/10/power-query.png?resize=275%2C290&amp;ssl=1 275w" alt="" width="432" height="455" data-lazy-loaded="1" /><br/></a>
<center>Learn DAX for Power Pivot and Power BI</center></h3>
<a href="https://courses.theexcelclub.com/courses/master-dax-fundamentals-power-bi-amp-power-pivot" data-elementor-open-lightbox=""><img src="https://i0.wp.com/theexcelclub.com/wp-content/uploads/2017/05/1.0.png?fit=434%2C460&amp;ssl=1" sizes="(max-width: 434px) 100vw, 434px" srcset="https://i0.wp.com/theexcelclub.com/wp-content/uploads/2017/05/1.0.png?w=434&amp;ssl=1 434w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2017/05/1.0.png?resize=283%2C300&amp;ssl=1 283w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2017/05/1.0.png?resize=410%2C435&amp;ssl=1 410w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2017/05/1.0.png?resize=100%2C106&amp;ssl=1 100w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2017/05/1.0.png?resize=275%2C291&amp;ssl=1 275w" alt="DAX for powerpivot course" width="434" height="460" data-lazy-loaded="1" /><br/></a>
<h6 style="text-align: center;">The Excel Club is the only Excel Blog in the world where you can Earn while you Learn Excel.  <a href="https://theexcelclub.com/learn-and-earn-steem-activities/">Find out about our Learn and Earn Activities now</a></h6>
<img src="https://theexcelclub.com/wp-content/uploads/2019/05/steem-1-150x150.png" alt="learn and earn excel activities" width="150" height="150" /><br/> <br /><center><hr/><em>Cross posted from my blog with <a href='https://wordpress.org/plugins/steempress/'>SteemPress</a> : http://theexcelclub.com/power-trick-save-time-without-vba/ </em><hr/></center>          
👍  , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , and 70 others
properties (23)
post_id82,260,769
authortheexcelclub
permlinkexcelpowertrick-savetimewithoutvba-jjw16k0ynn
categoryexcel
json_metadata{"community":"steempress","app":"steempress","tags":["excel","stem","palnet","steemleo","tutorials"],"canonical_url":"http:\/\/theexcelclub.com\/power-trick-save-time-without-vba\/"}
created2019-12-03 21:35:03
last_update2019-12-10 13:34:27
depth0
children6
net_rshares21,264,195,459,894
last_payout2019-12-10 21:35:03
cashout_time1969-12-31 23:59:59
total_payout_value2.508 SBD
curator_payout_value2.814 SBD
pending_payout_value0.000 SBD
promoted0.000 SBD
body_length9,064
author_reputation40,220,220,244,131
root_title"Excel Power Trick - Save Time Without VBA"
beneficiaries
0.
accountsteempress
weight1,500
max_accepted_payout1,000,000.000 SBD
percent_steem_dollars10,000
author_curate_reward""
vote details (134)
@shharafat ·
$0.10
Educational post.I am not good at excel.This article help me lot.

Posted using [Partiko Android](https://partiko.app/referral/shharafat)
👍  , ,
properties (23)
post_id82,261,357
authorshharafat
permlinkshharafat-re-theexcelclub-excelpowertrick-savetimewithoutvba-jjw16k0ynn-20191203t220523658z
categoryexcel
json_metadata{"app":"partiko","client":"android"}
created2019-12-03 22:05:24
last_update2019-12-03 22:05:24
depth1
children0
net_rshares564,613,360,081
last_payout2019-12-10 22:05:24
cashout_time1969-12-31 23:59:59
total_payout_value0.048 SBD
curator_payout_value0.048 SBD
pending_payout_value0.000 SBD
promoted0.000 SBD
body_length137
author_reputation36,869,450,645,195
root_title"Excel Power Trick - Save Time Without VBA"
beneficiaries[]
max_accepted_payout1,000,000.000 SBD
percent_steem_dollars10,000
author_curate_reward""
vote details (3)
@steemshiro ·
$0.05
Great example of using PQ, Thank you
👍  , ,
properties (23)
post_id82,266,010
authorsteemshiro
permlinkq1yxbp
categoryexcel
json_metadata{"tags":["excel","stem"],"app":"stemgeeks\/0.1","canonical_url":"https:\/\/stemgeeks.net\/@steemshiro\/q1yxbp"}
created2019-12-04 03:21:27
last_update2019-12-04 03:21:27
depth1
children0
net_rshares331,786,509,576
last_payout2019-12-11 03:21:27
cashout_time1969-12-31 23:59:59
total_payout_value0.027 SBD
curator_payout_value0.027 SBD
pending_payout_value0.000 SBD
promoted0.000 SBD
body_length36
author_reputation31,866,424,217,867
root_title"Excel Power Trick - Save Time Without VBA"
beneficiaries[]
max_accepted_payout1,000,000.000 SBD
percent_steem_dollars10,000
author_curate_reward""
vote details (3)
@detlev ·
$0.02
I love excel and I love to learn....

_Greetz from @detlev and a !BEER for you!_
👍  
properties (23)
post_id82,313,112
authordetlev
permlinkq221cd
categoryexcel
json_metadata{"users":["detlev"],"app":"steemit\/0.1"}
created2019-12-05 19:41:03
last_update2019-12-05 19:41:03
depth1
children0
net_rshares129,975,814,363
last_payout2019-12-12 19:41:03
cashout_time1969-12-31 23:59:59
total_payout_value0.010 SBD
curator_payout_value0.010 SBD
pending_payout_value0.000 SBD
promoted0.000 SBD
body_length80
author_reputation241,113,885,807,160
root_title"Excel Power Trick - Save Time Without VBA"
beneficiaries[]
max_accepted_payout1,000,000.000 SBD
percent_steem_dollars10,000
author_curate_reward""
vote details (1)
@beerlover ·
<div class='pull-right'>https://cdn.steemitimages.com/DQmaHThyECGhEx8tSfHZbiMFRNYjJ35K92cDgiJjkzBUaJo/One%20sip%20of%20BEER%20for%20you.gif<p><sup><a href='https://steem-engine.com/?p=market&t=BEER'>View or trade </a> <code>BEER</code>.</sup></p></div><center><br><br> <p> Hey @theexcelclub, here is a little bit of <code>BEER</code> from @detlev for you. Enjoy it!</p> </center><div></div>
properties (22)
post_id82,313,115
authorbeerlover
permlinkre-excelpowertrick-savetimewithoutvba-jjw16k0ynn-20191205t194115z
categoryexcel
json_metadata{"app":"beem\/0.21.1"}
created2019-12-05 19:41:18
last_update2019-12-05 19:41:18
depth1
children0
net_rshares0
last_payout2019-12-12 19:41:18
cashout_time1969-12-31 23:59:59
total_payout_value0.000 SBD
curator_payout_value0.000 SBD
pending_payout_value0.000 SBD
promoted0.000 SBD
body_length390
author_reputation16,011,958,080,429
root_title"Excel Power Trick - Save Time Without VBA"
beneficiaries[]
max_accepted_payout1,000,000.000 SBD
percent_steem_dollars10,000
@steempress-io · (edited)
$0.07
n043rxzcfo
you make it look easy<br/> - db2<br/>
 > This comment was made by a guest account using @steempress. Rewards will be sent to the user once they have a Steem account
👍  ,
properties (23)
post_id82,475,677
authorsteempress-io
permlink52n6if37hlh0co1
categoryexcel
json_metadata{"app":"steempress\/2.0","author":"db2","text_length":21,"author_id":244}
created2019-12-11 18:55:45
last_update2019-12-15 16:02:00
depth1
children1
net_rshares399,060,578,610
last_payout2019-12-18 18:55:45
cashout_time1969-12-31 23:59:59
total_payout_value0.033 SBD
curator_payout_value0.033 SBD
pending_payout_value0.000 SBD
promoted0.000 SBD
body_length164
author_reputation8,403,195,053,816
root_title"Excel Power Trick - Save Time Without VBA"
beneficiaries
0.
accountsteempress
weight100
max_accepted_payout1,000,000.000 SBD
percent_steem_dollars10,000
author_curate_reward""
vote details (2)
@paulag ·
Thanks, I hope you will give it a try because it is as easy as it looks
properties (22)
post_id82,505,753
authorpaulag
permlinkq2f2jq
categoryexcel
json_metadata{"app":"steemit\/0.1"}
created2019-12-12 20:35:51
last_update2019-12-12 20:35:51
depth2
children0
net_rshares0
last_payout2019-12-19 20:35:51
cashout_time1969-12-31 23:59:59
total_payout_value0.000 SBD
curator_payout_value0.000 SBD
pending_payout_value0.000 SBD
promoted0.000 SBD
body_length71
author_reputation224,445,607,823,384
root_title"Excel Power Trick - Save Time Without VBA"
beneficiaries[]
max_accepted_payout1,000,000.000 SBD
percent_steem_dollars10,000