Chidinma Onyemachi

Problem

Before any visual analysis of the revenue discrepancy could begin at Meridian Retail (simulated retailer), Nura Ibrahim needed all relevant source tables available in Power BI. The task was to connect to the PostgreSQL database, load eight specific tables in Import mode, and establish the relationships between them.

What I did

Each table was loaded using PostgreSQL.Database() in Power Query, pointing to the correct server and database, with Import mode selected so data would be cached locally for fast reporting. Seven relationships were defined to link the tables: order_items to orders and products, sessions to orders, inventory to products and stores, orders to stores, and products to categories. This set of joins produces a star-schema structure with orders and order_items at the centre. Proactive type-change steps were also added in Power Query for columns known to have capitalisation inconsistencies, casting channel, status, and payment_method to text in the relevant tables.

Outcome

The semantic model gave Nura a connected, Import-mode dataset ready for DAX measures and visual development in subsequent tasks.

let
 Source = PostgreSQL.Database("ep-frosty-glade-ab4k8hyw-pooler.eu-west-2.aws.neon.tech", "meridian_retail"),
 retail_categories = Source{[Schema="retail",Item="categories"]}[Data]
in
 retail_categories

This M code connects to the Meridian PostgreSQL database and retrieves the retail.categories table by schema and item name, the pattern repeated for all eight tables in the model.

Next step

With the model in place, a useful next check would be to verify row counts in Power BI against the SQL layer for each table, to confirm Import mode loaded complete data and no rows were silently dropped during connection.

Problem

The CFO at Meridian Retail (simulated retailer), Nura Ibrahim, flagged a revenue discrepancy and needed a clear picture of what order statuses existed in the system before any deeper analysis could begin. The specific question was: what distinct statuses are present and how many orders carry each one?

What I did

To answer this, I wrote a single aggregation query against the retail.orders table, using the schema prefix to scope it correctly to the right database object. GROUP BY status collapses all rows to one per distinct status value, and COUNT(*) tallies the rows in each group. Ordering by order_count DESC puts the most common statuses at the top, which makes the output immediately readable for a non-technical stakeholder reviewing it cold.

Outcome

Nura received a clean, ranked breakdown of all order statuses, giving her the foundation needed to decide which statuses to exclude from net revenue calculations in subsequent steps.

SELECT status, COUNT(*) AS order_count 
FROM retail.orders
GROUP BY status
ORDER BY order_count DESC;

This query groups every row in retail.orders by its status value and counts how many orders fall into each group, sorted so the largest group appears first.

Next step

A useful follow-on would be to filter to only the negative-revenue statuses (cancelled, returned, refunded) and compute their combined order value, to quantify how much gross revenue those statuses represent.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) needed to see Q4 2023 revenue broken down by order status across October, November, and December, so she could assess whether the cancellation pattern in October was worsening or isolated. The deliverable was a PivotTable with a supporting stacked bar chart, built in Excel.

What I did

I built the PivotTable in a dedicated sheet named Status Breakdown, placing order status on rows and months on columns, with the sum of total_naira as the value field. A Grand Total row was included at Nura's request. The values were formatted to show the naira symbol with two decimal places so the output required no further formatting before sharing. For the chart, a stacked bar was the right choice because it shows both the total revenue per month and the within-month composition by status in a single view.

Outcome

Nura received a formatted, month-by-month breakdown of revenue by status, paired with a chart that makes the relative contribution of each status visible across the quarter at a glance.

Next step

The next useful cut would be to isolate only the non-revenue statuses (cancelled, returned, refunded) and compute their share of gross revenue by month, to give Nura a single trend line for order attrition across Q4.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) needed the orders table ready for revenue visuals, but total_cents was stored in kobo and the created_at field carried a time component that was not relevant for date-level reporting. Both issues needed to be resolved in Power Query before any measures could be built.

What I did

The kobo conversion was added as a custom column step using each [total_cents] / 100, producing the naira value. A rename step then changed the column from Custom to Total (NGN), matching the agreed naming convention. In the same type-change step, Total (NGN) was explicitly cast to type number and created_at was cast to type date, stripping the time component. Keeping all of this in the Power Query layer means the transformation is applied at load time and does not need to be repeated in DAX.

Outcome

The cleaned orders table gave downstream DAX measures a correctly typed, naira-denominated total column and a date field suitable for time-based slicing without further transformation.

Next step

A worthwhile follow-up would be to confirm that no rows have a null or zero value in total_cents before the conversion, since a null divided by 100 would silently produce a null Total (NGN) that could undercount revenue in aggregated measures.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) identified that all price columns in the order_items table were stored in kobo rather than naira, making them unusable for revenue and margin reporting. The task was to add three correctly converted columns, Unit Price in naira, Discount in naira, and Line Total in naira, as the canonical figures for all downstream calculations.

What I did

Each of the three conversions was added using Table.AddColumn in the Power Query M code, dividing the relevant cents column by 100. After adding each column, I applied explicit type number casting so that downstream DAX measures would not need to handle ambiguous types. The original _cents source columns were left intact, preserving a traceable link back to the raw source values. The same division-by-100 pattern was also applied to the orders table under a separate task, so the approach is consistent across both tables.

Outcome

The order_items table now carries naira-denominated price columns that can be used directly in revenue and margin measures without any further conversion step in DAX.

Next step

It would be worth checking whether the cents values in any rows produce a fractional naira amount that rounds unexpectedly when summed, particularly for discount columns where small rounding differences could accumulate across millions of line items.