Posts

Reversing Row order in Excel

In many cases we face challenges in reversing row order in Excel.  There are two simple approaches for doing this. One is to add an extra column with serial numbers and then sort on that column in descending order. This works perfect for small amount of data.

For big data it is best to use Microsoft Power Query for Excel Add-in. It works great with Microsoft Excel 2010 and Microsoft Excel 2013.

 

 

Simple steps to perform the action:

  1. Convert your existing Data to Excel Table. To know more on Excel Table, you can refer to the article Working with Excel Table.
  2. Select the created Table and click the From Table under Power Query Tab as shown in the below image.
  3. The Power Query window will open. Select the Reverse Rows under the Transform Tab.

power-query-tab-table

 

power-query-reverse-rows

Benefit of Power Query?

It works on a smaller sample of data and then applies the transformation when you choose Save and Load option. This is much faster than getting all the data and then trying to sort it which is the first method.

Why is this required?

Usually required with logs where the first transactions or rows are at the bottom. So the data is received in reverse chronological order. Twitter feeds, Timeline Updates, Live blogs – all follow this pattern.

This method works independent of the time-stamp column. What is wrong with timestamp? It may be in different time formats, some rows may have same timestamp and some rows may have no timestamp at all.

Business Intelligence

Wikipedia defines Business Intelligence (BI), the set of techniques and tools for the transformation of raw data into meaningful and useful information for business analysis purposes.

There are other definitions of Business Intelligence that are used in the Corporates, few of them are:

  1. Business Intelligence refers to skills, processes, technologies, applications and practices used to support decision making.
  2. Systems that provide directed background data and reporting tools to support and improve the decision-making process.
  3. A popularized, umbrella term used to describe a set of concepts and methods to improve business decision making by using fact-based support systems. The term is sometimes used interchangeably with briefing books and executive information systems.
  4. Business Intelligence is a broad category of applications and technologies for gathering, storing, analyzing, and providing access to data to help clients make better business decisions.
  5. A system that collects, integrates, analyses and presents business information to support better business decision making.
  6. Business Intelligence is an environment in which business users receive information that is reliable, secure, consistent, understandable, easily manipulated and timely facilitating more informed decision making.

business-intelligence-word-cloud

Business Intelligence Core Capabilities

BI technologies are capable of handling large amounts of unstructured data to help identify, develop and otherwise create new strategic business opportunities. The goal of BI is to allow for the easy interpretation of these large volumes of data. Identifying new opportunities and implementing an effective strategy based on insights can provide businesses with a competitive market advantage and long-term stability.

business-intelligence-core-capabalities

BI technologies provide historical, current and predictive views of business operations. Common functions of business intelligence technologies are reporting, online analytical processing, analytics, data mining, process mining, complex event processing, business performance management, benchmarking, text mining, predictive analytics and prescriptive analytics.

BI can be used to support a wide range of business decisions ranging from operational to strategic. Basic operating decisions include product positioning or pricing. Strategic business decisions include priorities, goals and directions at the broadest level. In all cases, BI is most effective when it combines data derived from the market in which a company operates (external data) with data from company sources internal to the business such as financial and operations data (internal data). When combined, external and internal data can provide a more complete picture which, in effect, creates an “intelligence” that cannot be derived by any singular set of data.