The Ops Accountant · Analyze · Power Query

Analyze · Case study 3 · Power Query

Combining non-op JIBs from different operators

Every operator sends its JIB in its own layout, with its own well names and its own property numbers. Reviewing them one file at a time doesn't scale. This query drops each month's files into one folder and turns them into a single table keyed on your well name and cost center, ready to review, accrue against and post.

The problem: same costs, different shapes

JIB_Northfork_2026-09.csv · Northfork Operating

PeriodPropertyProp NoAFEAcct DescGross AmtYour DecimalNet Amt
202609THOMPSON 2-HNF-1182LEASE OPERATING - CHEMICALS6,120.400.250000001,530.10
202609THOMPSON 2-HNF-1182LEASE OPERATING - LABOR9,845.110.250000002,461.28
202609THOMPSON 2-HNF-1182LEASE OPERATING - POWER7,100.550.250000001,775.14
202609ORTEGA 3-HNF-1240LEASE OPERATING - CHEMICALS8,210.000.200000001,642.00
202609ORTEGA 3-HNF-1240LEASE OPERATING - LABOR14,300.040.200000002,860.01
202609ORTEGA 3-HNF-1240LEASE OPERATING - POWER11,600.000.200000002,320.00

MesaRidge JIB Sep-26.xlsx · sheet "Owner Detail" · Mesa Ridge Energy

Billing MonthWellAFE #Category8/8thsWI %Owner Share
Sep-26Ramirez #1HLOE28,278.7312.53,534.84
Sep-26Ramirez #2HLOE1,200.0012.5150.00
Sep-26Kessler No. 1LOE15,931.76355,576.12
Sep-26Kessler No. 10417WO240,000.003584,000.00

Different column names, different period formats, decimals in one file and percentages in the other, upper-case names with hyphens against "#" and "No.", and neither one uses your cost centers.

The approach

One folder

Each month's operator files go in one folder. Nothing is typed or pasted.

One function per operator

Each reshapes that operator's layout into the standard columns.

Cross-reference

A small table maps each operator's well to your well name and cost center.

Validate

Unmapped wells and decimals that don't match your records drop into their own queries.

Load

One clean table feeds the review, the accrual and the JE.

The cross-reference table

Kept in the workbook as a table named WellXref. It's the only thing maintained by hand, and it changes only when a well is added or an operator renames one.

OperatorOperator wellOperator property no.Well nameCost center
Northfork OperatingTHOMPSON 2-HNF-1182Thompson 2HCC-7001
Northfork OperatingORTEGA 3-HNF-1240Ortega 3HCC-7105
Mesa Ridge EnergyRamirez #1HRamirez 1HCC-7002
Mesa Ridge EnergyKessler No. 1Kessler 1CC-7210

The M code

fnNorthfork · reshapes a Northfork file

(file as binary) as table =>
let
    Source   = Csv.Document(file, [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
    Headers  = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    Typed    = Table.TransformColumnTypes(Headers, {
                   {"Gross Amt", type number}, {"Your Decimal", type number}, {"Net Amt", type number}}),
    Month    = Table.AddColumn(Typed, "month", each Text.Start([Period], 4) & "-" & Text.End([Period], 2), type text),
    CostType = Table.AddColumn(Month, "cost_type", each
                   if [AFE] = "" then "LOE"
                   else if Text.Contains([Acct Desc], "WORKOVER") then "Workover" else "Capital", type text),
    Renamed  = Table.RenameColumns(CostType, {
                   {"Property", "operator_well"}, {"Prop No", "operator_prop_no"}, {"AFE", "afe_no"},
                   {"Gross Amt", "gross_amount"}, {"Your Decimal", "company_wi"}, {"Net Amt", "net_amount"}}),
    Out      = Table.AddColumn(Renamed, "operator", each "Northfork Operating", type text)
in
    Table.SelectColumns(Out, {"month", "operator", "operator_well", "operator_prop_no", "afe_no",
                              "cost_type", "gross_amount", "company_wi", "net_amount"})

fnMesaRidge · reshapes a Mesa Ridge workbook

(file as binary) as table =>
let
    Book     = Excel.Workbook(file, true),
    Sheet    = Book{[Item = "Owner Detail", Kind = "Sheet"]}[Data],
    Typed    = Table.TransformColumnTypes(Sheet, {
                   {"8/8ths", type number}, {"WI %", type number}, {"Owner Share", type number}}),
    Month    = Table.AddColumn(Typed, "month", each
                   Date.ToText(Date.FromText("01-" & [Billing Month], [Format = "dd-MMM-yy", Culture = "en-US"]), "yyyy-MM"), type text),
    Decimal  = Table.AddColumn(Month, "company_wi", each [#"WI %"] / 100, type number),
    CostType = Table.AddColumn(Decimal, "cost_type", each
                   if [Category] = "LOE" then "LOE" else if [Category] = "WO" then "Workover"
                   else if [Category] = "PA" then "P&A" else "Capital", type text),
    AfeNo    = Table.AddColumn(CostType, "afe_no", each if [#"AFE #"] = null or [#"AFE #"] = "" then "" else "MR-" & Text.From([#"AFE #"]), type text),
    Renamed  = Table.RenameColumns(AfeNo, {{"Well", "operator_well"}, {"8/8ths", "gross_amount"}, {"Owner Share", "net_amount"}}),
    Out      = Table.AddColumn(Renamed, "operator", each "Mesa Ridge Energy", type text),
    PropNo   = Table.AddColumn(Out, "operator_prop_no", each "", type text)
in
    Table.SelectColumns(PropNo, {"month", "operator", "operator_well", "operator_prop_no", "afe_no",
                                 "cost_type", "gross_amount", "company_wi", "net_amount"})

NonopJIB · combines the folder and maps to well name + cost center

let
    Folder    = Folder.Files(JibFolderPath),                         // a parameter: this month's folder
    Tagged    = Table.AddColumn(Folder, "Parsed", each
                    if Text.StartsWith([Name], "JIB_Northfork") then fnNorthfork([Content])
                    else if Text.StartsWith([Name], "MesaRidge") then fnMesaRidge([Content])
                    else null),
    Known     = Table.SelectRows(Tagged, each [Parsed] <> null),
    Combined  = Table.Combine(Known[Parsed]),
    // Normalize names so "Ramirez #1H" and "RAMIREZ #1H" match the cross-reference
    Clean     = Table.TransformColumns(Combined, {{"operator_well", each Text.Upper(Text.Trim(_)), type text}}),
    XrefClean = Table.TransformColumns(WellXref, {{"Operator well", each Text.Upper(Text.Trim(_)), type text}}),
    Mapped    = Table.NestedJoin(Clean, {"operator", "operator_well"}, XrefClean, {"Operator", "Operator well"}, "x", JoinKind.LeftOuter),
    Expanded  = Table.ExpandTableColumn(Mapped, "x", {"Well name", "Cost center"}, {"well_name", "cost_center"}),
    Grouped   = Table.Group(Expanded, {"month", "operator", "well_name", "cost_center", "afe_no", "cost_type", "company_wi"}, {
                    {"gross_amount", each List.Sum([gross_amount]), type number},
                    {"net_amount",   each List.Sum([net_amount]),   type number}})
in
    Grouped

Validation queries · referenced from NonopJIB, loaded to their own sheet

// Unmapped wells: billed to us, but not in the cross-reference
UnmappedWells = Table.SelectRows(NonopJIB, each [well_name] = null),

// Decimal check: the operator's decimal against our own records for that well
DecimalCheck =
let
    Joined = Table.NestedJoin(NonopJIB, {"well_name", "cost_center"}, Wells, {"well_name", "cost_center"}, "w", JoinKind.Inner),
    Ours   = Table.ExpandTableColumn(Joined, "w", {"company_wi"}, {"our_wi"}),
    Off    = Table.SelectRows(Ours, each Number.Abs([company_wi] - [our_wi]) > 0.00000001)
in
    Off

The result

September, both operators, one shape. The amounts tie to each file's totals.

MonthOperatorWell nameCost centerAFECost typeGrossCompany WINet
2026-09Northfork OperatingThompson 2HCC-7001LOE23,066.060.250000005,766.52
2026-09Northfork OperatingOrtega 3HCC-7105LOE34,110.040.200000006,822.01
2026-09Mesa Ridge EnergyRamirez 1HCC-7002LOE28,278.730.125000003,534.84
2026-09Mesa Ridge EnergyKessler 1CC-7210LOE15,931.760.350000005,576.12
2026-09Mesa Ridge EnergyKessler 1CC-7210MR-0417Workover240,000.000.3500000084,000.00
2026-09Mesa Ridge EnergyUnmapped: RAMIREZ #2HLOE1,200.000.12500000150.00

Ramirez #2H isn't in the cross-reference, so it lands in UnmappedWells instead of being posted to a guess. Either it's a new well that needs a cost center, or the operator billed an owner who isn't in it. Either way, someone decides before it posts.

Why it's built this way

All operators, wells, file names and figures are made up. M code is shown as written; file paths and sheet names would be set to your own.