Implementing Business Rules through Advanced Transformations In this article, I’ll continue from the article [Power Query – Transformations] and illustrate some advanced transformations available in the Query Editor. I’ll begin with the same data from the previous article. Split Column By Delimiter First, I wish to parse “Order ID” values so I end up with…
Implementing Business Rules through Transformations In this article, I’ll continue from the article Excel Power Query – Introduction and illustrate some transformations available in the Query Editor. I’ll begin with the data loaded into the Query Editor. Remove Columns Since I don’t need the field Ship Mode, I’ll use “Remove Columns” to remove this column.…
Leveraging Power Query to harness your data. The Power Query Add-On provides power capabilities to connect to multiple data sources, import data, query and transform data. Power Query allows you to load millions of rows into Excel’s Data Model which keeps the data in memory instead of numerous worksheets. This enormous data storage allows analysis…
Automate scenario analysis of constraints to achieve goals. Product management leverages numerous factors to achieve optimal success with product outcomes such as constraints (limitations upon resources) and decisions decisions on those constraints. Optimization allows team members to influence certain decisions about constraints, delivering differing results according to the team strategy. In this article, I will…
Using Excel to create a script to batch-rename images. First, I’ll begin with a simple set of images I intend to rename. Then, I’ll create a new Excel spreadsheet from which I’ll delete the last two worksheets, leaving only one. In my new spreadsheet, I’ll enter those image names into an Excel spreadsheet (or use…
Please see my other Statistics articles. Ensuring Tables Intuitively Communicate Data During most data analysis, team members must review many small to large data sets. Often, these data sets resides within poorly designed tables, wasting much time when they should have first re-designed those tables to make the data more intuitive and easier to discern…
Using excel to query the database to produce reports. Using the ubiquitous Office application Excel, users may easily perform queries against your database to display reports within the worksheet and then utilize Excel’s robust tools to customize their reports. For this example, I’ll begin with a simple customers tables from AdventureWorks db. First, I’ll open…
Understanding event outcomes by their frequency. One of the most important patterns studied within statistical research are frequency distributions and their cumulativeness to understand events. In this example we’ll begin with a dataset illustrating adult student ages with the goal of understanding how often certain-aged students participate in school. Next, I’ll add to my spreadsheet…
Presenting column headers as rows. If you have ever worked with a long list of column headers – too long so it displays off the screen to the right, you know how arduous such a list can be to work with – use Transpose. I’ll begin with a list of columns which goes up to…
Combine data from worksheets into a new worksheet. Beginning with two worksheets containing fictitious data where both share a column – you need at least one “key” between the two, I will use Excel to combine both sheet’s data into one worksheet. First, save the spreadsheet somewhere easily accessible. Define Dataset Ranges In Excel, name…
TechMbaBi 