Microsoft Power BI: Super Charge Your Data Analysis Process

Learn Power BI for Data Analysis at Intellisoft Singapore

Do you analyze data? Whether you work in Sales, Finance, Manufacturing, Logistics, Engineering or Customer Service, there is a constant need to analyze data.

With the advent of the industrial age and the conveyor belt, gaining efficiency and reducing  errors has been a constant battle. We need more and better ways to analyze data. Today, billions of new data points are gathered every minute. There is more storage available in the cloud, and there is no dearth of computers big and strong enough to process it quickly.

Yet, there is a lack of know how, a lack of understanding on how to analyze data. As a result, more companies are still not able to tap on the promise of Big Data, Artificial Intelligence, Internet of Things or Machine Learning.

To Understand the State of Data Analysis, and look at solutions, we need to understand a few fundamentals about Data, and Data Analysis.

Why Do We Want To Analyze Data?


Of course there are a gazillion reasons.

No… Not really. The need to analyze data is routed in only a few basic reasons.

  1. To Gain Insights about our data. Why?
    • So that we can find out what works, and what does not. So we can compare the past results with another company, another product, another period, and see if there is some insight to glean.
    • When we find out what works, and find out where the problem lies, we can then perform Root cause analysis – which can tell us the real problem to fix. When we have fever, we can take a tablet of Paracetamol (Tylenol/Panadol/Aspirin).

      However, the fever is not the disease. It is a symptom that we develop, when something is not right with our body, and the body is fighting the bad bacteria/virus. During this fight, the temperature rises. The Paracetamol can reduce the fever, but it does not remove the fight.

      To go to the root cause of the problem, a visit to the doctor is required. The doctor may analyze the problem by looking at your Blood pressure, heart rate, x-ray, probes etc. and figure out the issue. If it is indeed bad bacteria causing some area to swell, the doc can issue a dose of Anti-biotics, which fight the bad guys, and treat the real problem.

      Once the bad guys are gone, the fever automatically comes down to the normal level.

  2. Make Better & Faster Decisions
    • Based on the insights, we are able to make better decision, and faster too.  And backed by data, we can have more confidence on our decision making process… rather than relying only on the gut-feeling.
    • With statistical methods of analyzing data and forecasting trends, the accuracy of the analysis, and insights gets deeper as we generate better analytics. We can then make a more informed decisions faster.

How Do We Analyze Data: The Traditional Data Analysis Process

The data analysis process is simple, and we have been following it for so long that it has almost become a habit… albeit a not so good habit. What we usually do is outlined below:

  1. Get the Raw Data File From The Source System
    • This could be a Text File, a Comma Separated Values (CSV) File, a PDF file, an SQL data dump, an Access or RDBMS source (think Oracle, Microsoft SQL Server or MySQL Database)
  2. Load into Excel
    • Load this data into Excel. This could be easily achieved, but this is a manual process, that has to be repeated every day, week, month etc., whenever the data changes, or new data arrives.
  3. Clean
    • Incoming data is seldom clean. There will be missing values, duplicates, wrong headers, mis-aligned dates in YYYYMMDD or some other odd text or  numeric layout, and other data issues to fix.
    • To do this, you’ll have to write some simple formulas, functions, data cleanup steps, fix dates, and fill blanks or nulls with an appropriate values. This is all manual process, unless automated with the help of macros.
  4. Lookup Master Data
    • Once the data is cleaned-up, we need to change the codes with values, get the correct product price, employee salary, or the date of manufacture from some master data tables. This often requires expertise with Excel formulas like VLOOKUP & HLOOKUP.

      You may have to do some interim calculations too, create some calculated columns, and create a wide table with all the fields/columns required for the analysis in the next step.

  5. Create Pivot Tables
    • To analyze the data, one of the quickest and fastest ways is to create a Pivot Table. Pivot Tables help you to summarize data – create Sum, Count, Differences, Cumulative Totals etc. and split it by the Rows & Columns.

      Further, you can create multiple Filters or Slices using the Filters or Slicers options.

  6. Keep Refreshing
    • By default, a Pivot Table does not refresh automatically. You have to either set up this option, or refresh manually.

      And even if you refresh it either way, it may not pick the additional, new data, for the range of the source data may be set explicitly to an Absolute range.

      Changing the Absolute range always is an extra step that you have to remember to do.

  7. Create Charts
    • To present the data, we mostly rely on the popular Bar Charts, Column Charts, Pie Charts, and the newly introduced Map, Funnel or Tree map charts.

      However, charts often get too busy, and there are not too many options to customize them.

      Once the charts are ready, you don’t want to send the entire Excel file, with the charts. And sending only the charts is not an option, without the underlying data.

      This brings us to the next point…

  8. Paste Charts Into PowerPoint
    • PowerPoint is the darling of the corporate boardroom.

      No presentation is ever complete without a mega slideshow. So people use Excel to analyze the data, and the paste the charts into PowerPoint.

      They create hundreds of slides, and then marvel at their slide deck. Finally, they are ready for the big presentation to the directors.

  9. Present to Management using PPT Slides
    • The PowerPoint show begins, and your audience is thrilled by your analysis, prestation and charts. You are secretly gloating over the success. Suddenly a member of the audience has a question – can you compare this quarter with a quarter 5 years ago?
    • Well… yes you can. But you don’t have the data right now.

      You’ll have to get back to your desk, do the right analysis, and probably show them the chart in your next quarterly meetup.

    • The opportunity window is so small, by the time you get back to them, they probably do not need the information any longer. You have to cut a sorry figure, and all your great work comes to nothing…

What’s wrong with this whole scenario. Let’s find out.

Problems With Traditional Approach To Data Analysis

There are a number of major problems in the Traditional approach of Data Analysis that has been around for the past 20-30 years… pretty much all along the advent of Spreadsheets. To name a few:

  • Excel Charts and Reports Are Not Interactive
    • Traditional Excel or PowerPoint Reports/Charts are not interactive. They are just screen shots. So they don’t change dynamically. Good because it will always be the same, and speed of execution will be fast. Bad because it can’t be used again… And has to be manually refreshed each time.
  • You Can’t Share Excel Files Easily
    • People are worried when it comes to sharing their Excel files. They have security, and privacy concerns. And an Excel file is quite fragile. Change of any path, sheet, formula or cell can break the workings completely, rendering it completely useless.
  • Excel VLookups are Slow & Cumbersome To Use
    • Excel files rely on Vlookups to pick the correct employee salary, product description attributes, or prices. It works great, and has been the mainstay for Excel’s popularity. However, VLookups recalculate extremely slowly on a linked Excel file, which has anywhere in excess of 200,000 records. At half a million, things absolutely come to a standstill, and many times Excel will give you the dreaded Blue Screen and simply crash.
  • Excel Takes Time to Clean & Refresh every time
    • Newly added data has to be cleaned up. It takes time, and is a slow, painstaking process. If you forget to update, or refresh on time, it shows old/wrong data and is a complete waste. New data has to be added, and refreshed each time.
  • Security of Excel Data is Quite Fragile
    • Sending Excel reports without the data is simply not possible. And sending it with data raises Security concerns. Any leaks will destroy your pricing, and lose your competitive advantage. And any broken links will render the data useless too.
  • A Very Small, Finite Limit of Excel Rows
    • There is only a finite number of rows that can be loaded into Excel. The current limit is a million rows (1,048,576 to be exact.). But in today’s world, a million rows is considered nothing. We need to look at ways to expand the limit to millions of rows.
  • Pivot Table Limitations
    • Pivot tables are quite slow to refresh. Further, they do not refresh automatically. And, there are very few ways to visualize the data – only sum, count, average, and YTD etc. It lacks the rich data transformations like Year on Year, Quarter on Quarter Analysis. It is not very good at showing the total overall percentages and its breakdowns.
  • Wasted Time in Cleanup
    • 80% time is spent  to clean the data, and only 20% is left for us to analyze it. This does not accord the time and importance data analysis requires. The equation is completely skewed out. It should be the other way round – 20% to clean, and 80% time for analysis.

Is There A Solution To All These Data Analysis Woes?

With the current state of Business Intelligence Tools, there is more than a glimmer of hope. The industry has finally arrived to a point where Big Data processing is becoming commonplace, and is no longer in the realm of the wealthy or the academics. Today, the common man’s BI tools are already working wonders.

  • Need for Interactive System
    • Today, the top of the breed BI tools like QlikView, Tableau and Microsoft, all offer dynamic ways to present data, and interactive ways to visualize it, in numerous ways.
  • Write Once. Use Again.
    • Now the steps of cleaning the data can be recorded, and used again and again, each month with hardly any tweaks. This reduces the need for manual cleanup and improves accuracy and reliability. It offers ways to scale up the data analysis process and work on more value added services.
  • Shift Cleanup vs Analyze Ratio to 20-80.
    • With the added interactivity and automation, finally more time can be spent in analyzing the data. Cleanup jobs are set in the background, and they can continue to run on auto-pilot. Plus, we are able to process data now in real time, which drastically improves the prospects of having usable, actionable data and insights.
  • Load Data From Multiple Sources
    • The ability to load and merge data from multiple sources is becoming much easier, and automation is making this chore into a breeze that is a fun to do activity today.
    • Data from Text files, CSV, Excel, Databases, XML, Websites, Live Tickers, On and Off site Corporate Data Warehouses is becoming a reality. This opens up new possibilities.
  • Remove need to do complex, slow VLookups
    • Forget slow VLookups, and be able to lookup any row, any column, irrespective of the order or distribution of the data rows and columns. This frees up new options and makes merging data a simple matter. In fact, now we do not need to have very wide tables..

      We can have shorter (Less wide – less number of column in a table And  have taller tables (More data rows)

    • For Tips on Data Modeling, read our Data Modeling Considerations in Power BI.
  • Load Huge Data Volume, beyond 1 million rows of Excel
    • Options like Microsoft Power Query & Microsoft Power Pivot enable you to load a few million rows a simple matter. And there is no need to worry about running out of space in Excel or its bandwidth – the ability to process a few million rows.

Microsoft Power BI: The Magic Wand To Vanquish All Your Data Analysis Problems

  • Super Fast
  • Dynamic, Interactive
  • Handle Big Data With Ease
  • Hundreds of Data Sources
  • Build Relationships. End of VLookups
  • Multiple Ways to Refresh
  • Visualize First
  • Generate Insights
  • Share With Ease
  • New Ways to Visualize – Maps, Tree Maps, Funnels, KPIs, Speedometers
  • Security of Sharing
  • Multi Device Support – Web, Desktop, Mobile, Tablet, Without installing any additional software
  • Real Time Processing
  • Multiple Dashboards
  • Slice & Dice To your Heart’s content

Article Written by Vinai Prakash, MBA, PMP, GAP, ACTA Certified

Additional Resources for Power BI

Training Courses

Data Analytics & Visualization with Power BI

Learn Microsoft Power BI Suite For Better Data Analysis & Reporting

Power BI Tips, Tricks & Video Tutorials

Power BI Tip #2: Reference Query Results in Another Query With Power Query [Video Tutorial]

Microsoft Power BI: Super Charge Your Data Analysis Process

Power BI Tip #6: Fixing The Vertical Axis in Power BI Visualisations

Power BI Tip #5: All About Slicer Controls in Power BI

Power BI Tip#4: Enter Data Into Power BI Quickly [Video]

Power BI Tip #3: Quick Formatting of Power BI Visuals

Learn Microsoft Power BI Suite For Better Data Analysis & Reporting

Learn Power BI for Reporting & Analysis at Intellisoft Singapore
  1. Are you new to Power BI?
  2. Been hearing a lot about Power Query, Power Pivot or Power BI lately?
  3. Has your management or HQ asked you to quickly learn Power BI?

If the answer to any of the above questions is Yes, then it is time you learned somethings about the latest offerings from Microsoft for Business Intelligence.

After all, Microsoft is way ahead of the competition in terms of Vision, Strategy & Speed of Execution in the space of Business Intelligence. See the latest Gartner Research on Business Intelligence for yourself.

Microsoft is now miles ahead of Tableau or Qlik.

Microsoft-Power-BI Leader in 2019 - Gartner Research
Microsoft-Power-BI Leader in 2019 – Gartner Research

No wonder companies are ditching such software and migrating their Dashboards & entire reporting environment to Microsoft Power BI.

Current State of Enterprise Reporting

Most likely your current state of Corporate Reporting is confined to Analysis in Excel, Conversion of the analysis results to a Line or Bar Chart, and then Pasting the charts into PowerPoint for presentation to the management & clients. This presents a static view of the data, shown in board rooms all over the world.

However, the key thing lacking in such reports is the interactivity. Suppose your customer suddenly asks you to compare the last quarter with the same quarter a couple of years back. While you might have the data, you don’t have the chart ready-made, right now. Chances are high that you’d have to apologise and promise to show them the requested report or chart in a subsequent meeting.

But Business can’t wait! By the time you show them the report next month, it may be useless, and people would have even forgotten about it too.

Plus, sharing data with your users is a big problem. You’d have to send huge Excel files, that take time to load, and are full of VLookups to different prices and master codes. If someone were to tinker and make a change in a place, chances are that the whole thing might collapse, and render the reporting useless.

Additionally, Reports don’t refresh automatically each month. Someone needs to load the next month’s data, and refresh the reports manually, month after month. This wastes so much corporate time & resources.

The ability to show any data, from any month, quarter or year, on the fly, can help the business answer any question they may have, and react faster!

And it would work wonders if the reports could refresh automatically, month after month, without anyone’s intervention.

Enter Power BI Suite of Products…

Microsoft Power BI Suite Traning in Singapore
Microsoft Power BI Suite Traning in Singapore

What is PowerBI Suite of Products?

Power BI is a brand new product from Microsoft. It was launched in 2015, and in less than 5 years, it has gained supremacy in the Business Intelligence space.

Power BI enables the common users to build stunning reports, dashboards, and make them available to all users, without having to download any expensive software. Users can consume the reports anytime, anywhere, even with just a phone or on an iPad, on any browser.

The reports are a visual treat, and make playing with data a breeze. It is extremely user-friendly and has hardly any learning curve.

If someone can use a web page, they can consume a dashboard done in Power BI and analyze data to their heart’s content – slice it, dice it, export it, print it, compare it with another month, year on any business segment, any category, any zone.

The Power BI Desktop is free to download.  It can fetch data from over 70 different sources, including Excel, any SQL or native Database, Web, SalesForce, SAP, Azure, Google Analytics, Mailchimp, ZenDesk, Twilio, SurveyMonkey and several other cloud services. Power BI is able to clean the data, process it, and get it ready for consumption, all in a simple, easy to use software.

PowerBI Desktop Download
PowerBI Desktop Download

Everything you need is in-built, ready to use.

There are 4 major componentsPower Query, Power Pivot, Power BI & the Power BI Online Services.

They help you to Get Data, Analyze Data, Visualize Data, and then Share Data with your users. Let’s delve into these components a bit deeper…

What is Power Query?

Power Query allows us to bring in data from almost any source, clean it, fill empty values, replace nulls, remove empty rows, and do Vlookup type operations into other tables to pick any reference values, all without writing a single piece of VBA code, or any formula.

Microsoft PowerQuery Training in Singapore
Microsoft PowerQuery Training in Singapore

Everything is done through the elaborate options, buttons, settings and features of Power Query.

I was able to clean very dirty data files in just 12 minutes, which would have taken me at least 3-4 hours to do manually in Excel.

And I don’t have to do it again! Next week, when the new data file comes from the ERP system, I just have to drop it in the correct folder, and it will be cleaned up automatically.

PowerQuery was released as an Add-On component in Excel 2010 & Excel 2013, but it is now embedded into Excel 2016, 2019 and Office 365. No need to install or enable anything.

It is ready, enabled by default, and available at a click of the button, already existing in the Microsoft Excel DATA tab, as well as in Power BI Desktop.

Have fun with it… it is an absolute delight to load and clean data using Power Query. Once the data is loaded into Power BI, we then have to learn how to create a data model in Power BI.

What is Power Pivot?

PowerPivot is the engine that powers the data visualizations in Power BI. It runs in the background in Power BI, and as an add-on within Microsoft Excel.


Power Pivot & Components
Power Pivot & Components

Loading Millions of Rows, Fast!

Each Excel worksheet has a limit of just over a million rows. 1,048,576 Rows to be exact. This is a logical limit. However, most of the time if I only load 500,000 rows of transactional data, the Excel file becomes quite large and takes forever to open.

Plus, multiple Vlookups, complex Formulas etc. can slow things down, and Excel becomes unresponsive for long periods as we make changes on large worksheets and workbooks.

Enter Power Pivot, which is an Add-on to Excel (Yes, it is still available as an Add-On on Office 2013, 2016, 2019 & Office 365).

With Power Pivot, this limit of just 1 million rows is easily eliminated. Now we can load millions of rows, and the file size does not grow considerably. Plus the Excel files are quite responsive and able to handle things quite fast.

This is because PowerPivot does not store the data in the traditional Excel way. It uses the Vertipaq Columnar Database, which compresses the data, and loads what is needed for any calculations in the RAM only.

The speed is blazing fast and allows you to work with Excel freely. Plus, it removes all the limitations that came with Excel Pivot Tables.

Now we can extend the normal pivots by loading data from multiple Tables, Multiple Files, Multiple Sources, and combine them, merge them, mash them up and report using a Data Model, which can have relationships with the different entities.

The ability to load from multiple sources, and create pivot table reports that use Big Data (1 Million Plus… usually 40-60 million rows is not a problem), and is still quite fast.

I haven’t seen Excel ever hang or blue screen on me even with this huge sized data, which is what I use most of the time. It is like working off our Corporate Oracle Database, which has over 500 Million+ rows of transactions and is over 80TB in size.

Data Model View

I can view the entire data model visually, and see the relationships… something that I couldn’t do in several other databases or reporting software. This allows you to see the tables and their relationship with other tables easily. A visual data model shows the relationships clearly.


Data Modeling in Power Pivot
Data Modeling in Power Pivot

DAX – A New Language For Writing Amazing Formulas For Visualization

With PowerPivot, Microsoft has introduced a plethora of new formulas, a completely new language of writing formulas – called DAX (Data Analysis Expressions).

DAX Expressions in Power BI & Power Pivot - Get Trained in Singapore
DAX Expressions in Power BI & Power Pivot – Get Trained in Singapore

I simply love writing DAX to calculate things which would have taken me complex formulas to compute, with a lot of helper columns, tables and worksheets.

Things like measuring Revenue from the same period last year, last quarter and available in a simple function.  Counting Distinct Rows, Calculating things over multiple tables, with multiple complex conditions is handled so seamlessly and easily, that I am amazed.

I used to be a die-hard SQL fan, being able to extract any data from any database using SQL, but with DAX, it puts my SQL skills to shame. The DAX calculations functions are aplenty and make any calculation a matter of a few minutes to write.

However, learning DAX does take time. There’s Row Context, Filter Context, and the ability to alter context on the fly take a while to understand. Newbies often get stumped in understanding these concepts and it does take time to get a good handle on writing good DAX.

Even though learning DAX is complex and takes time to master, the effort is simply worth it.

I never regretted the time I spent in learning and writing good quality DAX. It has allowed me to calculate complex things for myself and my clients.

In fact, almost all of my consulting time is spent in helping clients read, understand or write complex DAX measures. They love it, and I love teaching it too.

Learn Complex DAX Measures in Microsoft Power BI
Learn Complex DAX Measures in Microsoft Power BI

Once the DAX functions are written, it is time to visualize the data. This can be done in a normal Pivot Table in Excel, but I prefer to visualize this in Power BI – which has a number of chart types to visualize the information easily.

What is Power BI?

Power BI is the beautiful, sexy, outer world, where the clients see amazing column charts, bar charts, pie charts, maps, slicers, matrix reports, KPIs in Dynamic, self-updating Dashboards.

PowerBI is simply a class apart!

Power BI Training in Singapore
Power BI Training in Singapore

In no other BI software have I seen so many ways to visualize, slice, dice, and navigate the data, so easily. I seldom have to teach the interface to any client – because it is so intuitive, easy to use, completely user-friendly, yet extremely powerful.

Power BI lets you create multiple ways to visualize the same information. It’s a breeze to create any visual, just by dragging and dropping the different measures, and slicing them by any dimension – by country, by geography, by business unit,  by category, sub-category, by zone, by year, by quarter, by sector, by Product… almost anything you have in your data.

It can come from any dimension in any table within the data model.

Learn To Build Your Own Power BI Dashboards (Sample 2)
Learn To Build Your Own Power BI Dashboards (Sample 2)

The best part of Power BI Visualizations is that it automatically filters other visuals immediately as you touch any bar or value in any other visual.

This allows us to see things in its entirety, without having to write any extra code or effort. Plus you can a good view of the pie of the pie or how much impact does one item have on the overall value?

We can see the data, sort it, export it, and see only one visual in the Focus mode.

Once you have analyzed the data in Power BI Dashboards to your heart’s content, it is time to spread the love, and share it with other colleagues and stakeholders who could benefit from the dashboard data analysis and visualization to make better, more informed decisions.

Power BI Dashboard Example 1
Learn To Build Your Own Power BI Dashboards (Sample 1)

You don’t have to send huge, heavy files by zipping to anyone. Simply use Power BI Online Services.

What is Power BI Online

Once the Power BI Visualization Dashboard is completed, it is time to Publish it Online, publically (Free), or to your Private Group of People within the Department or Division or Company (Paid) through the Power BI Online Services.

Power BI Online Services
Power BI Online Services Training Singapore

You can share your dashboard with others by emailing them a link, and then they can consume it whenever, wherever, without installing any software. They can browse, slice and dice, visualize in any way, on any device, using any browser.

There is even a Phone View, which fits all the visuals perfectly on the phone, and makes it easy to check the KPIs on the fly.

The paid Power BI Services allow you to refresh the data every 3 hours, and you can even go to refresh it every half hour in the Enterprise server option (that’s akin to 48 refreshes each day)

Power BI Online Runs in any Browser
See Power BI Reports in any browser – on any device: Microsoft Power BI Training at Intellisoft Systems, Singapore

For those with Write or Edit access, they can even make changes to the reports and dashboards and save another copy. You can publish as many dashboards, in different workspaces, and it works seamlessly with Sharepoint, Web, Azure and all other Online platforms, refreshing data on the fly and showing you the latest numbers.

A Perfect Package of Power, Simplicity & Elegance

All these features packaged together make the whole thing work seamlessly. You don’t even realize when you moved from Power Query to Power Pivot to Power BI to the Online Services. It just feels one simple to use package that does it all.

Microsoft has put in a lot of effort to design a state of the art, cutting edge Business Intelligence Software for the information-hungry business world.

Unlike Microsoft Office, which only gets updated every 2-3 years, Power BI suite gets updated each month, with multiple features, and even new DAX formulas being released each month.

Start the Exciting Journey of Gaining Business Insights With Microsoft Power BI

It’s time to embark on the journey to understand, use and implement Power BI in your business. Help the business make better decisions with updated information available to the decision-makers. Give them the ability to slice and dice data without waiting for IT or analysts to prepare the reports manually.

Be future-ready. Don’t wait till all your competitors are using it to gain an edge.

Be the force leading change in your business. Get Started Today!

Intellisoft Systems offers 2 day hands-on workshops for Power Query, Power Pivot, Power BI & Online Services, that have been extremely popular in Singapore for SME and MNC companies embarking on the Power BI suite of products.

Power BI Training in Singapore
Power BI Training in Singapore

Written By: Vinai Prakash

Vinai is the founder & Managing Director of Intellisoft Systems, a leading Training company based in Singapore. Vinai writes regularly for the Straits Times, leading magazines and newspapers, and conducts several workshops around the world sharing his knowledge in Business Intelligence, Data Warehousing, Data Mining & Data Analysis.

At Intellisoft, Vinai conducts workshops and seminars on several topics like Power BI, Building Dashboard with Excel, Data Analysis & Project Management.

Contact us to attend a training or to organize a workshop for your entire department or company to benefit from Vinai’s impactful data analysis techniques and practical, hands-on approach that has won the accolades from thousands of delegates from around the world.

Article Written by Vinai Prakash, MBA, PMP, GAP, ACTA Certified

Additional Resources for Power BI

Training Courses

Data Analytics & Visualization with Power BI

Learn Microsoft Power BI Suite For Better Data Analysis & Reporting

Power BI Tips, Tricks & Video Tutorials

Power BI Tip #2: Reference Query Results in Another Query With Power Query [Video Tutorial]

Microsoft Power BI: Super Charge Your Data Analysis Process

Power BI Tip #6: Fixing The Vertical Axis in Power BI Visualisations

Power BI Tip #5: All About Slicer Controls in Power BI

Power BI Tip#4: Enter Data Into Power BI Quickly [Video]

Power BI Tip #3: Quick Formatting of Power BI Visuals

Have You Achieved Reporting Nirvana?

Achieved Reporting Nirvana with Intellisoft Singapore

Huh! What’s that?

Just see the image below & check where you see yourself – in Reporting Hell or Reporting Nirvana…

Reporting Hell
Manual Reporting Hell

If you are trapped in Reporting Hell, here’s your Typical Reporting Day

Step 1: Bring the latest data files each month. This could be in Text, CSV, Excel files.
Step 2: Open each file.

Step 3: Format Dates, Numbers & Text correctly in each data file.
Step 4: Remove Duplicates and check the data rows don’t have any junk data.

Step 5: Build Calculation Formulas to work out interim values.
Step 6: Once the data is in good shape, Copy and paste this into the Master Data File in Excel File.

Step 7: Change Data Source of Dropdowns, Pivots, Charts to include new data pasted in the master file.
Step 8: Refresh the Report manually for each pivot, chart or data source.

Step 9: Repeat the same steps for each data file. This is a laborious task of data cleaning, transforming & loading the data, called as the ETL process.
Step 10: Test to see if all well well, and that you did not miss anything.

Step 11: Publish the Report online & Inform the key stakeholders of the new report availability.
Step 12: Keep Fingers Crossed!

Typical Total Time Taken: 6 Hours to 3 Days at a minimum

It is extremely frustrating & time-consuming to do the same manual steps each month to load the data, clean it, transform it and update all reports manually.

Most data analysts spend almost 90% of their time in loading & cleaning the data. They hardly have any time left to analyze or visualize the data to help the management make better decisions. They are stuck in doing the mundane, routine things, which is a total waste of their time.

If you are in such a situation, you are living in Reporting Hell.

It is time to move to completely automated, dynamic reporting, that self-updates each day, week or month, and your users can consume the reports on any device, instantly. It is like living in Reporting Nirvana – a completely amazing and refreshing world, where you spend most time analyzing data productively.

Care to know more about Reporting Nirvana?

Reporting Nirvana: Typical Day

In Reporting Nirvana, here’s what your typical day looks like:

Reporting Nirvana With Power BI
Reporting Nirvana With Power BI

Step 1: Bring the latest data files in whatever format.
Step 2: Open the KPI Dashboard Report, which automatically picks the latest data files

Step 3: Everything is automatically Refreshed
Step 4: Enjoy your coffee!

Typical Total Time Taken: 3 minutes to 15 minutes at a maximum.

Achieve Reporting Nirvana

It’s time to move to Reporting Nirvana With Microsoft Power BI automation tools – Power Query, Power Pivot, & Power BI or with Excel Dashboard Creation Techniques.  Imagine the amount of time you can save by doing the manual steps only once, and getting them executed each week & each month automatically, without having to do anything?

Attend the 2 Day Excel Dashboard MasterClass & the 2 Day PowerQuery, PowerPivot & PowerBI Training

Learn how you can achieve Reporting Nirvana, and spend your day doing smarter things, analyzing data and contributing to growing your company’s topline and bottom-line revenue! Contact us at

Vinai Prakash
Founder & Master Trainer: Intellisoft Systems

Free Tips, Tutorials & Training Grants Info

Learn from expert tips, tricks and resources for Excel, PowerPoint, Photoshop, Python, Power BI, Project Management, IT, Soft Skills & more with our Email Newsletter.
Plus get the latest news on Grants. Join Today!

Found What You Were
Looking For?

Just Tell us...

We're Here To Help You!