Oluwademilade Dipe

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) flagged that the orders table stored monetary values in kobo, and that the created_at column carried a time component that was not needed for the analysis. Both issues needed to be resolved in Power Query before any visuals could be built.

What I did

In the retail orders M code, I used Table.TransformColumnTypes to convert the created_at column to type date, stripping the time component cleanly at the query stage rather than in a calculated column. A new naira column was added with Table.AddColumn using each [total_cents]/100, then renamed to Total (NGN) via Table.RenameColumns. A final type-cast step was applied to the new column to confirm it as type number, ensuring the field behaves correctly in any downstream measure or visual. Doing the conversion and renaming in Power Query rather than in DAX means the clean column is available across the whole model without an additional measure.

Outcome

Nura's model now holds a ready-to-use Total (NGN) column in naira and a clean date field, so revenue visuals can be built without any further unit or type handling.

Next step

A follow-up check on whether any total_cents values are null or negative would confirm whether the division step produces any unexpected nulls or negative naira figures that could distort revenue totals.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) wanted to see whether cancellation rates were worsening across Q4 2023 or whether October had been an anomaly. She asked for a breakdown of revenue by order status across October, November, and December 2023, presented as both a PivotTable and a stacked bar chart.

What I did

A PivotTable was built on a new sheet called Status Breakdown, sourced from the full Orders range, with order status on rows and order date grouped by month on columns, and total_naira as the summed value field. Grouping the date field by month inside the PivotTable keeps the structure dynamic rather than requiring manual column splits. All seven statuses appear as row items, a Grand Total row is included, and every value cell carries the Nigerian Naira currency format. A stacked bar chart referencing the Status Breakdown ranges was added beneath the PivotTable to make the monthly split visible at a glance.

Outcome

Nura received a single sheet containing a formatted, filterable breakdown of Q4 revenue by status and a chart that makes month-on-month changes in each status visible without any further manipulation.

Next step

Calculating each status as a percentage of its month's Grand Total, rather than showing absolute naira values alone, would make it easier to judge whether cancellations rose as a share of total orders or just in absolute terms as overall volume grew.

Problem

Nura Ibrahim, a stakeholder at Meridian Retail (simulated retailer), needed a visual analysis of a revenue discrepancy. Before any visuals could be built, the Meridian PostgreSQL database had to be connected to Power BI Desktop and all required tables loaded in Import mode.

What I did

Using the PostgreSQL.Database() connector in Power Query, I loaded all eight required tables by navigating each with schema-scoped M code, ensuring Import mode was used rather than DirectQuery or a live connection. Each table reference follows the pattern of selecting the correct schema and item name from the source, which keeps the queries explicit and maintainable. Power BI automatically detected nine relationships across the tables; I reviewed these and confirmed that the sessions-to-orders relationship was correctly set as inactive to avoid ambiguous filter propagation through the customer path. That single configuration decision protects the integrity of any measure that filters across both sessions and orders.

Outcome

Nura received a Power BI model ready for measure development and visual building, with a clean relational structure that will not produce misleading results from filter ambiguity.

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

This M code shows the schema-scoped navigation pattern used for each of the eight tables, connecting to the correct server and database and selecting the target table by schema and item name.

Next step

A useful next step would be to verify whether the auto-detected relationship cardinalities are all correct, particularly for the inventory table which joins on both product_id and store_id, since a composite key relationship may need manual adjustment.

Problem

Before any analysis of Meridian Retail (simulated retailer) data could begin, a structured audit of Meridian_Data.xlsx was needed to document the file's structure and surface data quality problems. The task required identifying at least three issues and recording findings in a new Data Audit sheet.

What I did

Working through each of the five sheets, I documented the sheet names, the 16 columns in the Orders sheet, and all seven distinct order statuses. Rather than hardcoding counts, I used live COUNTIF formulas to flag the 200 duplicate customer records in the Customers sheet and the 66 negative stock_qty rows in Inventory, so the counts remain accurate if the underlying data changes. The category format inconsistency in Order_Items, where both titled formats and slug formats appear in a single column producing 24 distinct values instead of the expected 12, was identified through browsing the column values. Two further issues, the redundant brand column in Products and the missing legacy_order_ref values in Orders, were also documented.

Outcome

The Data Audit sheet gave the analysis team a documented starting point for data cleaning decisions before any revenue or status work was carried out.

=COUNTIF(G:G,"<0")

This live COUNTIF formula, placed in the Inventory audit row, counts negative stock_qty values so the figure updates automatically if the source data changes rather than being a hardcoded number.

Next step

Investigating whether the 200 duplicate customer records share orders with their original records would clarify whether deduplication would materially affect any revenue or order-count metrics.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) needed an October 2023 revenue figure calculated in Excel to cross-check against the Finance system. Specifically, she wanted both gross revenue across all order statuses and net revenue excluding cancelled, returned, and refunded orders, along with the gap between the two.

What I did

Both figures were calculated with SUMIFS formulas referencing the total_naira and order_date columns in the Orders sheet. Date boundaries were built using DATE(2023,10,1) and DATE(2023,11,1) rather than text strings, so the criteria work correctly regardless of how Excel displays the date column. Three additional criteria pairs using the not-equal-to operator exclude the cancelled, refunded, and returned statuses from the net revenue calculation. All three output cells are formatted with the Nigerian Naira currency format including thousand separators, and the gap is a simple subtraction referencing the two formula cells rather than a hardcoded number.

Outcome

Nura received a self-contained, labelled sheet she could use directly to cross-check the Finance system figure, with the cancelled, returned, and refunded portion of revenue made visible as a single gap value.

Next step

It would be worth checking whether any orders have a status not in the seven known values, since an unexpected status string would silently inflate net revenue without triggering any of the exclusion criteria.