DEV Community: Denis Ndiritu

From Raw Data to Business Decisions: Building a Power BI Solution for JCars Logistics

Denis Ndiritu — Sat, 26 Sep 2026 13:40:44 +0000

JCars Logistics imports, sells, and delivers vehicles across regions in Kenya. I was handed a single raw flat file - 32 columns, 277 rows - describing sales orders, customers, vehicles, branches, payments, deliveries, logistics costs, returns, cancellations, and customer experience. The task: turn it into a reliable, interactive Power BI solution that helps management actually understand how the business is performing.

No cleaning had been done. Nothing was standardized. That was the point - the assessment was as much about finding the problems as fixing them.

This article walks through that journey: investigation, cleaning, modelling, DAX, dashboard design, and what I found along the way.

Step 1: Understanding the Data Before Touching It

Before writing a single transformation, I established the grain: one row = one vehicle sales order, identified by Order Id. The columns broke down into four natural groups - order/transaction details, customer details, vehicle details, and location/branch details. That grouping is what eventually became the star schema (more on that later).

Step 2: The Data Quality Audit

This dataset earned its "raw" label. A few of the more interesting problems:

Order IDs weren't really IDs. They arrived with inconsistent prefixes (LC, LCL-, ORD, plain numbers), some blank, some N/A, and - worse - several duplicated across completely unrelated rows. I rebuilt the column entirely using an incremental index (starting at 1000, prefixed ORD-), landing on 276 clean, unique order identifiers.

#"Added Index" = Table.AddIndexColumn(#"Removed Top Rows", "Index", 1000, 1, Int64.Type)

#"Inserted Prefix" = Table.AddColumn(#"Added Index", "Order Id", each "ORD-" & Text.From([Index]), type text),

Dates were a mess of formats. Order Date and Delivery Date mixed Excel serial numbers, multiple locales (en-US vs en-GB), plain-text errors like #DATE!, and outright invalid dates (2026-13-04, 31/02/2026). I built a try...otherwise parsing chain that attempts each known format in sequence and falls back to null only when nothing matches - rather than guessing.

Branches hid behind 64 different labels. Yards, HQs, city names, and abbreviations were all technically "the same place" under different labels. I cross-referenced the Sales Rep assignments - reps listed under "Thika," "Thika Yard," and "Thika Branch" turned out to be the exact same people - which confirmed these were naming variants of 8 actual branches, not distinct locations.

A typo pattern nobody would catch by eye. Several Sales Rep names had the number 1 substituted for the letter I (e.g., At1eno instead of Atieno) - likely a font/OCR-style substitution upstream. Combined with double-spaced names creating false duplicates, the rep list initially showed 20 "distinct" values that were really only 10 people. The code block below shows how the cleanup for the column was done.

CleanSalesRep = (raw as nullable text) as nullable text =>
        let
            v0 = Text.Trim(Text.Clean(raw ?? "")),
            v1 = Text.Replace(v0, "1", "i"),      
            v2 = Text.Proper(v1),
            v3 = Text.Replace(v2, "  ", " "),
            v4 = Text.Trim(v3)
    in
        if v4 = null or v4 = "" then "Unknown"
        else if v4 = "Mercy" then "Mercy Atieno"
        else if v4 = "Kevin" then "Kevin Mwangi"
        else if v4 = "Grace" then "Grace Njeri"
        else if v4 = "Mary" then "Mary Wanjiku"
        else if v4 = "Peter" then "Peter Kiptoo"
        else if v4 = "Faith" then "Faith Achieng"
        else if v4 = "Daniel" then "Daniel Kimani"
        else if v4 = "Aisha" then "Aisha Mohamed"
        else if v4 = "Brian" then "Brian Otieno"
        else if v4 = "Samuel" then "Samuel Mutua"
        else v4,

Discounts exceeding 100%. A handful of rows showed discount values like 1.2, which could mean 120% (a data error) or a misplaced decimal meant to be 12%. Rather than guess - either interpretation could be wrong, and guessing wrong is worse than leaving it blank - these were nullified and flagged for follow-up with the data source, rather than silently "corrected" into a number that might be equally incorrect.

Suspicious customer ages. The value 121 appeared three times, and -5 appeared three times too - clear signs of placeholder or default values rather than real ages. Since there was no date-of-birth field to cross-check against, these (along with 0) were treated as invalid and nullified rather than assumed.

Step 3: Standardizing Currency

Monetary columns (Unit Selling Price, Unit Cost, Discount, Delivery Fee, Logistics Cost, Revenue Recorded) arrived as text, mixing four currencies - KES, USD, EUR, ZAR - identified inconsistently by prefixes (Ksh, $, €, R) and magnitude suffixes (K for thousand, M for million).

Any value with no explicit currency marker was assumed to be KES. Everything else was converted using one consistent exchange rate set applied across the whole project:

Currency Rate to KES
USD 129.50
EUR 135.50
ZAR 7.10

_Source: https://www.investing.com/currencies _

Step 4: Cleaning in Power Query - and a Performance Lesson

Each problematic column got its own dedicated M function - dates, categorical text, monetary values, discounts, ratings - rather than one-off inline fixes scattered everywhere.

One mistake worth sharing: early on, I cleaned categorical columns (Region, County, Branch, Sales Rep, Lead Source, Car Make, Car Model) using long chains of Table.ReplaceValue steps - one per misspelling. Each of those steps re-scans and rematerializes the entire table, which meant the query got progressively slower as more steps piled up.

The fix was switching to a single Table.TransformColumns call per column, with the mapping logic expressed as one if...then...else if chain inside it:

powerquery
#"Cleaned Region" = Table.TransformColumns(#"Previous Step", {
    {"Region", each
        let v = Text.Proper(Text.Trim(_)) in
        if v = "Nbi" or v = "Nrb" or v = "Nairobii" then "Nairobi"
        else if v = "Rift" or v = "Rift-Valley" then "Rift Valley"
        else if v = "Msa Region" or v = "Cost" then "Coast"
        else if v = null or v = "" then null
        else v
    , type text}
})

Same result, a fraction of the applied steps, and a noticeably faster refresh.

Step 5: From Flat File to Star Schema

A flat table repeats every customer's, vehicle's, and branch's details on every single transaction row - that's redundant, harder to filter efficiently, and makes relationships harder to express. So the cleaned data was restructured into a star schema:

Fact_Sales - one row per order, holding the measures and foreign keys
Dim_Vehicle - Car Make, Car Model, Vehicle Type, Vehicle Year, Fuel Type, Transmission, Color
Dim_Customer - Customer Name, Customer Type, Customer Age
Dim_Branch - Region, County, City, Branch
Dim_SalesRep - Sales Rep
Dim_Date - a DAX-generated calendar table

Two modelling decisions worth explaining:

Why Customer Age lives in Dim_Customer, not Fact_Sales. Since there's no true Customer ID in the source data, Name + Type + Age was used as a proxy identity key for deduplication. This is an honest limitation, not a hidden one - two different customers sharing all three attributes would incorrectly merge into one customer record. Worth flagging to management if customer-level analysis becomes more important later.

Why some fields stayed in the fact table. Lead Source, Payment Method, Payment Status, Delivery Status, Returned, Customer Rating, and Review Count describe the transaction, not a reusable entity - splitting them into tiny standalone dimensions would add joins with no analytical payoff.

Building the fact table meant merging each dimension's descriptive columns back in as a lookup and keeping only the surrogate key. The one gotcha that cost some debugging time: Power Query's merge treats null = null as a non-match. If a descriptive column had blanks on both the fact and dimension side, that merge would silently fail for those rows. The fix was ensuring consistent non-null placeholders (e.g., "Unknown") flowed through from the cleaning stage, and spot-checking each merge with a temporary Table.RowCount() column before trusting it.

Step 6: DAX Measures

With a proper model in place, measures could finally express real business logic instead of relying on default aggregations. A sample:

dax
Total Revenue (Calculated) = 
SUMX(Fact_Sales, Fact_Sales[Units Sold] * Fact_Sales[Unit Selling Price] * (1 - Fact_Sales[Discount]))

Revenue Variance = [Total Revenue (Calculated)] - [Total Revenue (Recorded)]

Gross Profit Margin % = DIVIDE([Gross Profit], [Total Revenue (Recorded)])

Return Rate = 
DIVIDE(
    CALCULATE(DISTINCTCOUNT(Fact_Sales[Order Id]), Fact_Sales[Returned] = "Yes"),
    [Total Orders]
)

Revenue Variance is doing double duty as a validation check - comparing the recorded revenue against an independently calculated figure lets discrepancies surface naturally instead of blindly trusting one number.

Step 7: The Executive Dashboard

Page 1 had one job: let a manager understand "how is the business doing?" in under a minute, without needing anything explained.

It's organized into five zones - KPI cards up top (Revenue, Units, Gross Profit, Margin, Orders), a chronological revenue trend line, Top-10 bar charts for Branches and Car Makes, a regional revenue map, and a payment-status breakdown by value.

One small addition made a disproportionate difference: a conditionally-formatted Return Rate card - green under 30%, red above. It sounds trivial, but it turned into a genuine debugging lesson: even though the card displays as a percentage, Power BI's conditional formatting engine evaluates the underlying decimal (0.3), not the display value (30). My first attempt used whole-number thresholds (30 instead of 0.3), which meant the "red" rule was mathematically impossible to trigger and the "green" rule matched everything. The card looked "done" and was quietly wrong the entire time. Worth double-checking on any rate-based conditional formatting, not just this one.

Step 8: What I Found - Key Insights

The return rate is unusually high overall with BMW standing out sharply. 32% of all orders were returned. Nakuru Branch leads (16 of 33 orders). Whether it is a problem with Nakuru, it is an issue that needs investigation.
Digital lead sources bring high-value customers than manual ones. Instagram orders average Kes 6.5 million and Facebook Kes 5.9 million compared to Walk-Ins at Kes 3.1 million. This suggests marketing spend on social channels may be attracting a meaningfully higher-spending customers segment, worth validating with a large sample before reallocating budget on it alone.
Customer ratings and revenue are unrelated. The correlation between Customer Rating and Revenue Recorded is -0.02. Happier customers aren't spending more, and higher-value customers aren't necessarily happier.

Recommendations for Management

Open a formal quality investigation into Nakuru Branch operations. The return rate at Nakuru Branch is at 48.5%, and highest of any branch. Conduct an audit into the delivery handling and customer communication
Reassess how marketing spend is allocated across lead sources. Since our analysis shows social media sourced orders are higher compared against methods such as walk-ins, testing could be done to assess a shift towards social channels,and could be done over a long period before a permanent reallocation is done.
Audit and correct the pricing model for Toyota. Toyota is the highest revenue make with around 560 million (0.45% margin), while Subaru, returns a 6.96 % margin. Management should investigate whether the current selling prices cover the true cost to deliver.

Closing Thoughts

The hardest part of this project wasn't the DAX or the visuals - it was resisting the urge to "fix" ambiguous data by guessing. Nullifying an unclear value and documenting why is a more honest analytical decision than quietly inventing a number that looks plausible. That discipline - document the assumption, apply it consistently, and let management know where the data itself sets the limit - is the difference between a dashboard that looks finished and one that's actually trustworthy.

Presenting, Linking and Presenting Data in Power BI

Denis Ndiritu — Tue, 15 Sep 2026 05:34:04 +0000

As a data analyst, you are presented with data that can span from a single sheet with a few thousand rows, to a full worksheet with sheets, that have thousands and thousands of rows. Microsoft Excel will properly handle the single sheet effortlessly, allowing you to clean this data, and even present it using the available charts provided for you. However, when you have the complex data, Microsoft Excel might not be the best tool to analyze this data. First, you will need something that is efficient in terms of processing this complex data, present your data in a more, efficient manner, possibly have it in a few separate but linked tables, and provide a better manner to scale your data. This is where Power BI comes in.

What is Power BI?

Power BI is a business analytics and data visualization platform provided by Microsoft, that turns raw, scattered data into interactive, easy-to-understand charts and dashboards. Power BI, can be used to connect data from over 100 data sources, including Excel spreadshets, SQL databases, cloud services, etc. In this article, we look at three aspects of Power BI that are central to how data is connected: data modelling, relationships, and joins.

Data Modelling in Power BI

Data modelling is the process of structuring and organizing, and connecting data so that it can be analyzed correctly and efficiently. It involves creating relationships between tables, knowing which table holds what information, and deciding how the tables will connect to one another. A well designed data model matters more than tidiness: it directly affects how fast reports run, how simple of complex the DAX calculations become, how well the model scales as data grows, and how easy it is for someone or you as the author to maintain.

Data Modelling comes in different approaches, each with its own place.

Flat Table: Flat table in data modelling, also called one big table (OBT), is a data modelling approach where all transactional data and descriptive attributes are combined into a single, denormalized table. Instead of having data separated into multiple connected tables, every column will reside in one place.
At a glance, this is simple to read. However, it comes with real costs:there is massive data redundancy (the same customer details repeat on every row), update anomalies (if a customer changes their email, every one of their historical rows must be updates), and an inability to filter and aggregate cleanly, since the tabes mixes several different grains at once - a single row is simultaneously describing and order, a customer, and a product.
A flat table is appropriate only for small, one-off, or throwaway analyses - a quick ad hoc check, or data that already arrives denormalized and will not be reused. For any report that will grow, refresh regularly, or need ore than one grain of analysis, a flat table works against Power BI development best practices, which favor a star schema layout.
Star Schema Layout: A star schema, the recommended data modelling technique in Power BI, is a technique, where a central fact table connects to multiple dimensions table in a star-like structure. It's core components include:
- Fact Tables: Fact tables sit at the centre and store quantitative or numerical transactionsl data (e.g sales amounts, quantitis, revenue) alongside foreign keys that point out to each dimension.
- Dimension Tables: They surround the fact table and hold descriptive attributes e.g. product names, customer details, geography, and date, which provide context for filtering and grouping. Each dimension table contains a key column (or columns) that uniquely identifies each row, plus supporting descriptive columns.

Dimension tables generally hold a relatively small number of rows. Fact tables, on the other hand, can contain a large number of rows and continue to grow over time.

The Star Schema is preferred in Power BI for several reasons:

Faster Performance - There are fewer joins for the engine to resolve, since every dimension is a single hop from the fact table.
Simpler DAX - Star schema makes Data Analytics Expressions (DAX) calculations cleaner, more predictable, and less prone to error.
Less Redundancy - The star schema stores descriptive text once in dimension tables rather than repeating it across rows in transaction logs.
One-to-Many Relationships - The relationships connect the "one" side (dimension table) to the "many" side (fact table) with filters flowing strictly from dimensions to facts.

Even so, a star schema is not entirely free of trade-offs. Each dimension carries a small amount of denormalization of its own - for instance, a product's category name is stored directly on the product row rather than in a separate table - which is a deliberate compromise made in exchange for speed and simplicity, and is quite different from the redundancy problem a flat table has. A star schema also assumes the business process is reasonably well understood upfront, since the fact table's grain has to be decided early and is expensive to change later.
A star schema is appropriate for the large majority of Power BI reporting models. It is the default recommendation for new projects, unless there is a specific reason - usually a very large, shared, hierarchical dimension - to normalize further.

Snow Flake Schema: A snowflake scheme takes a star schema one step further, where some dimension tables are normalized into sub-dimensions, E.g instead of a DimProduct holding the category name directly, that column is split out into a separate DimCategory table, and DimProduct links to it instead.

While normalization in a snowflake model is high, it comes with disadvantages:

Power BI loads more tables, which is less efficient from a storage and performance perspective.
Longer relationship filter propagation chains need to be traveresed, and this might be less efficient that filters applied to a single table.
The Data pane presents more model tables to report authors, which can result in a less intuitive experience, especially when snowflake dimension tables contain only one or two columns.

A snowflake schema is appropriate when a dimension is large, genuinely hierarchical, and reused by more than one fact table - enough that the storage saved by normalizing it outweighs the cost of the extra relationship hop. Outside of that specific case, it is usually better to flatten the dimension back into a star using Power Query before loading.

Fact Tables and Dimenstion Tables

Fact and dimension tables were introduced briefly above, but the distinction deserves a closer look on its own, since almost every modelling decision in Power BI comes back to it.

Fact table - Stores the measurable business events - things that happened, with numbers attached. Its rows are typically numerous, and its numeric columns are measures: values meant to be summed, averaged, or counted, such as Quantity, UnitPrice, Discount, or Amount. Alongside these measures, a fact table holds foreign keys pointing out to each relevant dimension. Common examples include FactSales, FactOrders, and FactTransactions.

Dimension table - Stores the descriptive attributes used to slice, filter, and label the facts - the “who, what, where, and when” of a business event. Its rows are comparatively few, and it is identified by a primary key. Typical examples are DimCustomer, DimProduct, DimDate, and DimLocation.

The difference between a measure and an attribute matters here: a measure produces a meaningful result when aggregated - SUM(Quantity) makes sense - while an attribute is a label used to group or filter (summing a CustomerID, or averaging a City name, is meaningless). This is why fact tables tend to be "tall and narrow" - few columns, many rows - while dimension tables are "wide and short" - many descriptive columns, fewer rows.

Relationships in Power BI

When discussing data models, we slightly mentioned relationships.

A Relationship connect two or more tables using a shared column or key so that reports can filter and aggregate data correctly across them. Without a relationship, tables sit in the model completely isolated from one another.
Some of the key terms used in relationships include:

Primary key(PK): This is a column in a table that uniquely identifies each row, with no duplicates.
Foreign key(FK): This is a column in another table that refers to the primary key described above. They can also repeat in a table.
Cardinality: Whether "one" side and "many" side columns contain unique values.
Referential Integrity: Ensures that every foreign key value in the fact table should exist in the dimension's Primary key column. Orphaned keys referencing a key not existing in the dimension table shows up as a blank/unknown member in visuals.
Active and Inactive Relationships: An active relationship in Power BI is the default path used automatically for filtering and calculations, while an inactive relationship is ignored until explicitly activated using DAX.

This is exactly why, for example, CustomerID is unique in DimCustomer. Each customer appears exactly once, but the same CustomerID repeats many times in FactSales, once for every order that customer has places. The dimenstion describes the customer once, while the fact table will record every even that customer was involved in.

Let's now look at the types of relationships that apply in PowerBI:

Types of relationships

One-to-many(1:*) - In this relationship, one row in the dimension relates to many rows in the fact. It is used whenever a dimension describes many fact events.
One-to-one(1:1) - Each row in one table matches exactly one row in the other, both sides being unique. A proper example is with an employee table and and employe badge table, one badge per employee. It is however rare, and indicates that the two tables should simply be merged into one, since they carry no extra "many" value.
Many-to-many(* : *) - In this relationship, neither side is unique, e.g. a student table and a course table where each student takes many courses and each course has many students. This relationship is supported natively by Power BI via a shared column with duplicates on both sides, but it should be used cautiously as it can produce ambiguous or unexpected duplicate results. The remedy is usually to introduce proper bridge/junction tables that turns it into two clean one-to-many relationships instead.

As much as relationships have been established, we need to understand that they are not just connections. They also define which way filters travel. In power BI, filters will travel in two ways, which we will discuss below.

Filter Direction

Single Direction - This is the default filter direction for 1:many relationships. Filterig the "one" side (dimension) filters the "many" side(fact), but not the reverse.
Both/bidirectional: Filters flow both ways. Selecting a product in a dimension table filters the fact table, and filering the fact table can filter back and affect the visible rows in the dimension table.

Joins in Power Query

A join physically combined rows from tables in Power Query, based on matching key columns, before the data ever gets loaded into the model. Joins are performed using the Merge Queries feature to combine two tables. The following are the joins we have in Power Query:

Left Outer Join: This join keeps all rows from the first (left) table and matching rows from the second (right) table. Unmatched rows show as null.
Right Outer Join: Keeps all rows from the second (right) table, and matching rows from the first (left) table.
Full Outer Join: This join returns all rows from both tables, matching or not.
Inner Join: This join returns only rows that have matching values in both tables.
Left Anti-Join: Returns only the rows from the first(left) table that have no match in the second table.
Right Anti-Join: Returns only the rows from the second (right) table that have no match in the first table.

Source(Excel Unplugged)

Power Query Joins vs Power BI Relationships

It is worth nothing the difference between merging tables in Power Query and creating relationships in the Power BI data model.
Does a Power Query merge physically combine data? Yes. A merge copies matching columns from the second table into the first, producing one wider, denormalied table.
Does creating a relationship combine the tables? No. The tables remain fully separate in teh model, each with its won rows and columns. A relationship is basically a live link used at query time to propagate filters, not a physical copy of data.
At what stage does each happen? Merges happen in the Power Query Editor, during the transform/ETL stage, before data is loaded. Relationships are created afterward, in Model view, nce the tables already exist in the model.
When would you choose a merge instead of a relationship? When a single flattened table is needed for a specific purpose e.g. exporting a denormalized extract, or resolving a snowflaked dimension back into its parent so the model does not need the extra relationship hop.
How can excessive merging affect a model's structure? Merging fact-level detail into dimenstions, or merging multiple fact tables together, recreated the flat-table problem discussed earlier: redundancy returns, file size grows, refreshes slow down, and the odel loses the single clean grain per table that made the star schema fast and prdicatable.
Why keep the fact and dimension tables separate? Separation preserves a single source of truth for every attribute, keeps the fact table narrow and fast to scan, and lets Power BI's relationship enginde do the aggregation work it is build for.

Recommended Power BI Model

Weighing everything discussed above, the recommended approach for a typical business intelligence project is a star schema, built with single-direction, one-to-many relationships flowing from each dimension into the fact table, with bidirectional filtering reserved only for specific, justified cases such as a many-to-many bridge table.

This recommendation holds up against each of the factors that matter in practice:

Performance - a star schema aligns with how Power BI's VertiPaq engine compresses and scans data; narrow, single-hop relationships resolve fastest.
DAX simplicity - SUM, CALCULATE, and time-intelligence functions behave predictably when every dimension sits one hop from the fact table; both flat tables and snowflakes complicate this in different ways.
Model readability - a star schema is visually obvious in Model view; the fact table and its surrounding dimensions are immediately clear to anyone opening the file.
Scalability - adding a new dimension, such as DimPromotion, is a single new relationship, not a redesign of the whole model.
Data redundancy - dimensions carry a small, deliberate amount of denormalization (like a flattened category name), a reasonable trade for speed, and nowhere near the repetition problem of a flat table.
Maintainability - a clear separation between facts and dimensions makes future changes, such as renaming a column or adding an attribute, low-risk and localized to one table.
Ease of reporting - report authors work with a small, well-labelled set of dimension fields to slice by, rather than searching through one enormous table.
Filter propagation - single-direction relationships give unambiguous, predictable filtering, which becomes essential once a report has more than one or two slicers.
Model complexity - a star schema keeps the number of relationship hops to one, which keeps the whole model easy to hold in your head.

A snowflake schema is only worth its added complexity when a dimension is genuinely large, hierarchical, and shared across multiple fact tables - enough that the storage and maintenance savings outweigh the extra relationship hop and DAX complexity it introduces. A flat table should be reserved for small, one-off analyses that will never need to scale. For everything else - which, in practice, is most Power BI projects - the star schema, with single-direction, one-to-many relationships, is the right default.

Getting Started With Excel for Data Analytics: From Basics to Data Cleaning

Denis Ndiritu — Tue, 01 Sep 2026 03:31:34 +0000

In today's world, decisions are not based on intuition or guesses. They are not based on what one thinks is the right thing. If you own a business, you would like to make a decision based on certain trends. If you are a teacher, you may want to see the progress of a student over a certain period. This and more requires one key ingredient; data. This data is collected using various methods, over a long period of time, and you are able to analyze it to get meaningful information from it. As the name says, the final output, informs you on a decision you should make, based on the trends that your data has shown.

The data, however, may not be clean. The data can contain duplicates, extra spaces on certain values, blank rows and columns, wrong data types on values, inconsistent cases, and many more. Getting insights from such data may not give you accurate insights. This is where data cleaning comes in handy.

You would get provided with a piece of data either in csv or xlsx format, and use Microsoft Excel to start the cleaning process. Let's dig deep into this.

Opening An Excel File

First things first, we would want to open our data in Microsoft Excel. We also need to make sure that we have a copy of our raw data somewhere safe so that we can come to it for any references. To safely get the data from the excel file open Microsoft Excel and click Blank workbook. This will open a new workbook with no information in it. If you had already opened it, then simply navigate to File and click Blank workbook to create a new workbook.

Getting Data From our Raw File

We now have our blank workbook. We now need to get our data into the workbook so that we can get started with the cleaning process. To do this, we will navigate to the Data tab on our top bar menu. We shall then click the Get Data button that has a cylinder+table icon. Once clicked, we get a few options. Microsoft provided a ton of options which you can choose to get your data from, depending on your source. You can get data from files e.g Excel Workbook, Text/CSV, XML, JSON, PDF, Folder, or SharePoint Folder. You can even get data from Database, Azure etc.

Once you identify you source e.g. CSV, simply select it. You will then get a window to navigate to where your raw data is located. Navigate to that location and select your data. Click the Open button or double click your data source. This will then open a preview of the data inside the selected file, showing the encoding format of the file, the delimiter, and the data inside the file. Click the Load button at the bottom of your preview window.

This will now open your raw data inside a table. Note that this is now single worksheet that you have of your raw data. In our worksheet, we may want to duplicate our data so that we can separate it from the raw worksheet, and now what we shall clean. If you did the above steps properly, you would notice a tab at the right part of your window saying Queries & Connections. What happened is that the Get Data created a live connection to the external data source using an underlying query. When we click on this query, we are taken to the Power Query Editor where we can now make certain changes. These changes are structured as steps which are recorded and can be undone. The changes do not touch your worksheet until you Close & Load.

Why Should One Use Power Query Over Normal Excel?

The Power Query Editor is preferred over traditional Excel the moment that your data cleaning would need automation, has grown in terms of volume, or needs to have steps reproducible. This enhances visibility of steps done on your data, which can also be repeated over and over again.

Identifying Data Types in Excel

An excel file can have as low as one column and as many columns as possible. Each column holds information, which has been stored in a certain data type. It is important that you ensure that each column has consistent data type depending on what the column is.

A data type is a classification that specifies which type of value a field or variable can hold. Excel recognizes the following data types in cells: Text, Number, Dates, Times, Logical (Boolean) values, and Errors.

When cleaning your data, it is therefore important to ensure that each column has a consistent data type across its cells. Cleaning up data is very simple. Simply select the column you would like to specify its data type. In the Home tab, in the Number menu, specify the data type. Repeat the steps for all the other columns.

Similar Values in a Column, Duplicates or Not?

When cleaning our data, we mentioned that we may have values that repeat themselves. At times, this might be erroneous, while some may have repetitive values, but values in the subsequent columns be different, which might change how we handle the duplicate values.

For example, if we were checking for duplicates on a column such as a product name, and the product had batch numbers, it would not be a surprise when we have repeating product names, but different batch numbers. This would suggest that we have variations of our product, due to the batch number. In such a case, we would not remove the duplicate product. However, if the batch number was the same across the same product, this would highly suggest that some of the records are duplicates, which would necessitate us to remove one record, and remain with a single non-duplicate record.

Formatting Data

We had already started formatting our data but had not gone through other methods of formatting our data. In the Power Query Editor, one can format columns as they would wish to. Some options provided are:

lowercase -> Normalizes the selected column to have all its values as lowercase.
UPPERCASE -> Normalizes the selected column to have its values as upper case.
Capitalize Each Word -> Capitalizes the first letter of each word in each cell in the selected column.
Trim -> Removes leading and trailing whitespaces in each cell of the selected column.
Clean -> This will remove all non-printable characters in each cell of the selected column.
Add Prefix -> This will add a text that you will specify and will be appended before each value in the cells of the column you have selected.
Add Suffix -> This will add a text that you will specify and will be appended after each value in the cells of the column you have selected.

These are some of the ways you can manipulate your text values in Excel to have consistency.

Sorting and Filtering

Sorting

When cleaning your data, you may want it to appear in a certain order. You may want the first column to be sorted in ascending order. You may also want to combine sorting so that your data is arranged based on more than one column. This is where sorting comes in. Sorting does not add or remove any data. It just changes how they order themselves to allow one to see patterns, trends or even outliers. Some of the methods of sorting include:

Alphabetical (A to Z or Z to A -> This sorting method is used for text columns e.g. product names, or employee names.
Numerical (Smallest to Largest or Largest to Smallest) -> This is used for financial or quantity data and can be used for cases such as getting the lowest prices, finding the product with the highest quantity etc.
Chronological (Oldest to Newest or Newest to Oldest -> This can used to track dates e.g. oldest student in a class, the nearest deadline etc.
Multi-Level Sorting -> This kind of sorting allows one to sort data by more than one column at a time.

Filtering

You may want to see data based on specific criteria. This is made possible by Microsoft Excel using the magic of Filters. Filters allows you to focus on what you need, while temporarily hiding the rows that don't meet the criteria specified. You can always get the full data back by clearing your filter. Some of the filter methods include:

Checkbox Filtering -> The filter allows you to just check or uncheck boxes. Rows with the unchecked values will be filtered out, remaining with what has been checked.
Text Filtering -> The text filters allow one to search for values based on criteria such as Equals, Does Not Equal, Begins With, Ends With, Contains, and Does Not Contain. This will also provide you with a custom filter to create your own.
Number Filtering -> This allows you to filter numbers according to criteria such as Equals, Does Not Equal, Greater Than, Greater Than Or Equal To, Less Than, Less Than Or Equal To, Between, Top 10, Above Average, and Below Average. You can also create custom filters just like with the text filters.

Functions

We have cleaned our data, we have sorted it, and we have also filtered. We would want to have a bit of insights now. To do this, we need to use the functions provided by Microsoft Excel.

Instead of rearranging, we will now use functions to analyze our data. In this article, we shall talk about statistical functions and date functions offered in excel to help in analyzing data.

Statistical Functions

Statistical functions allow you to get a single value (metric) from a range of data points. Examples include:

MIN and MAX -> The two functions help you get the lowest and maximum values in a range respectively. You can get, for example, the highest salary given to your employees, or the lowest age among your staff.
AVERAGE -> This function will add up all selected values and divide by the total count.
MEDIAN -> Median is used to identify the exact middle number in a sorted list.
MODE -> The Mode function allows you to find the value that appears the most in your dataset. ### Date Functions Date functions are important when used in a dataset, as they can help you extract and analyze your data by time periods. For example:
YEAR -> The YEAR function extracts the four-digit year from a full date.
MONTH -> The MONTH function pulls the month from a full date, returning a value from 1 (January) to 12 (December).
DAY -> The DAY function pulls the day from a full date, returning a value ranging from 0 to 31.

Closing

This is just a scratch to the powerful world of Excel, and yet we can do much with the little we have covered. It is important that one understands all skills, as they build up as one continues exploring more and more functionalities of
Microsoft Excel.

Understanding The Git Workflow: Working Directory, Staging, Commit and Push

Denis Ndiritu — Sun, 23 Aug 2026 15:13:55 +0000

Imagine working on a project for several weeks. You make hundreds of changes across dozens of files. Then your computer crashes. Which version of your project was working yesterday? What did you change? Which changes did your colleague make? And how do you combine everyone's work without overwriting each other's changes?

This is where Git comes in.

What is Git?

Git is an open-source system that enables users to manage their projects, from a single file to large projects. Git is basically a version control system. It provides the necessary resource to help one manage files in the local machine, track the files that have been changed, attach a pointer which marks files which were changed, who did it, and when, and to make it redundant, these files can be pushed into a remote shareable project called repository in Github.

Git also has something very key that is called branches. A branch is basically an isolated area of your project where you can work on a copy of the main project without making changes to the main work. Once you are done working on your branch, Git makes it possible to merge all the changes to the main branch.

For this article, I will cover these concepts, guiding one on the step by step process of how to do all this. This article assumes that one has Git installed and configured, and has a Github account. We shall also assume that one has a text editor installed, or is conversant with commands in the terminal. We shall cover these concepts in another article.

Creating a Project On Your Machine

To start, we need to create a folder where we shall be working on our project. On your machine, you can use the good old right click and select New Folder, or, if in terminal, use the command mkdir as follows:
mkdir new_project
This creates a folder called new_project

We shall then open our folder in the terminal, so that we can do the following:

Initialize Git
Create a file
Stage it
Commit it
Link local repository to remote repository
Push to Github account

Initializing Git

At this point, we have our folder. So to open it in terminal, we can open our favorite terminal, or simply open our folder in our text editor. Once this is done, we can now initialize Git using this command:
git init
This command simply initializes a local Git repository in your project folder. You can confirm this by confirming that we have a .git folder inside our main folder. During initialization, you will note that you will be in a default branch called main or master. To confirm this, simply run the command:
git branch

Creating a File

In our terminal, we can be able to create a file using the command:
touch <filename>
This will create a file inside our main folder. For this case let's call our file main.py At this point, you can run certain commands like:
git status
which allows us to see files that we have not started to track, files we have modified, files we have staged, files we have modified after staging, and so on. Files that are not tracked means that they will not be pushed to Github once we get to that.

We can now write a simple sentence on our terminal using a command called echo as follows:
echo "print('This is a new file')" > main.py
The echo command prints text, but when used together with the greater than sign (>) and the file name, will write the text into the file specified. At this point, we are on our working directory. Git does not know what it needs to stage. So how do we stage files?

Staging a File(s)

Staging a file basically tells Git that we want to commit our changes that we have made so far. Moreover, it will also stage any new, modified, or deleted paths too. We can do this by basically staging everything or staging a single file, or staging specific files.
To do so, we can do the following

git add . // Stages everything that is yet to be tracked.
git add <filename> // Stages a single file.
git add <filename_1> <filename_2> ... // Stages specific files.

At this point, we are in the staging area. We know what we want to commit/update in our remote repository. In the next stage, we will commit the staged files.

Committing a File(s)

We now commit our changes so Git records a snapshot of what we have done. This is important, as git uses these commits to help the user go back to a certain point in your branch.
To create a commit, We use this command:
git commit -m "Add main Python file"
The commit message should be in double quotes. Once this is done, we need to to point our local folder to our remote repository. If not created, you will need to create a repository.

Note: At this stage of our workflow, we are now in the local repository. We have a full committed history of what we have done, but it is local. We are yet to update on our remote repository.

Link Our Local Repository to Remote Repository

We will first create a repository on Github. To do this, simply navigate to your Github account on your browser and click New Repository button on your Repositories page. You will then provide a name for your repository, provide a description, and you can later have it as Private or Public of other users to access it. Once that is done, you can click the Create repository button. For this case, our repository is called health_records_analysis

This has now created our repository. This repository contains a link which we can share to other users, and this is the link that we need to link our local folder with the newly created repository. To do this, simply access it once you scroll down in the page you are in the simply copy the http link. This is what we shall use. The link usually looks like this http://github.com//

We will then go back to the terminal we were in and enter the following command
git remote add origin <repository_link>

Great! We can now conduct the final step, pushing

Pushing Our Changes To Github

To push to Github, we only need a single command:
git push -u origin main

git push will send our local commit to the remote repository
origin basically is the name of our remote repository
main is the branch we are pushing to
-u will establish origin/main as the remote branch.

You can confirm this by going back to your Github account and refresh. You will see the file we just pushed, and the commit message. As you may have guessed, this is the remote repository. This is now where we have pushed our changes, and all this can be shared to relevant users.

Closing

Learning Git is important as a person working with any kind of projects that require proper tracking, can be collaborative, and even makes your work neat. It is a skill that is important, and one that I, personally has had fun learning, and look forward to sharing my progress.