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
| Period | Property | Prop No | AFE | Acct Desc | Gross Amt | Your Decimal | Net Amt |
|---|---|---|---|---|---|---|---|
| 202609 | THOMPSON 2-H | NF-1182 | LEASE OPERATING - CHEMICALS | 6,120.40 | 0.25000000 | 1,530.10 | |
| 202609 | THOMPSON 2-H | NF-1182 | LEASE OPERATING - LABOR | 9,845.11 | 0.25000000 | 2,461.28 | |
| 202609 | THOMPSON 2-H | NF-1182 | LEASE OPERATING - POWER | 7,100.55 | 0.25000000 | 1,775.14 | |
| 202609 | ORTEGA 3-H | NF-1240 | LEASE OPERATING - CHEMICALS | 8,210.00 | 0.20000000 | 1,642.00 | |
| 202609 | ORTEGA 3-H | NF-1240 | LEASE OPERATING - LABOR | 14,300.04 | 0.20000000 | 2,860.01 | |
| 202609 | ORTEGA 3-H | NF-1240 | LEASE OPERATING - POWER | 11,600.00 | 0.20000000 | 2,320.00 |
MesaRidge JIB Sep-26.xlsx · sheet "Owner Detail" · Mesa Ridge Energy
| Billing Month | Well | AFE # | Category | 8/8ths | WI % | Owner Share |
|---|---|---|---|---|---|---|
| Sep-26 | Ramirez #1H | LOE | 28,278.73 | 12.5 | 3,534.84 | |
| Sep-26 | Ramirez #2H | LOE | 1,200.00 | 12.5 | 150.00 | |
| Sep-26 | Kessler No. 1 | LOE | 15,931.76 | 35 | 5,576.12 | |
| Sep-26 | Kessler No. 1 | 0417 | WO | 240,000.00 | 35 | 84,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.
| Operator | Operator well | Operator property no. | Well name | Cost center |
|---|---|---|---|---|
| Northfork Operating | THOMPSON 2-H | NF-1182 | Thompson 2H | CC-7001 |
| Northfork Operating | ORTEGA 3-H | NF-1240 | Ortega 3H | CC-7105 |
| Mesa Ridge Energy | Ramirez #1H | Ramirez 1H | CC-7002 | |
| Mesa Ridge Energy | Kessler No. 1 | Kessler 1 | CC-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.
| Month | Operator | Well name | Cost center | AFE | Cost type | Gross | Company WI | Net |
|---|---|---|---|---|---|---|---|---|
| 2026-09 | Northfork Operating | Thompson 2H | CC-7001 | LOE | 23,066.06 | 0.25000000 | 5,766.52 | |
| 2026-09 | Northfork Operating | Ortega 3H | CC-7105 | LOE | 34,110.04 | 0.20000000 | 6,822.01 | |
| 2026-09 | Mesa Ridge Energy | Ramirez 1H | CC-7002 | LOE | 28,278.73 | 0.12500000 | 3,534.84 | |
| 2026-09 | Mesa Ridge Energy | Kessler 1 | CC-7210 | LOE | 15,931.76 | 0.35000000 | 5,576.12 | |
| 2026-09 | Mesa Ridge Energy | Kessler 1 | CC-7210 | MR-0417 | Workover | 240,000.00 | 0.35000000 | 84,000.00 |
| 2026-09 | Mesa Ridge Energy | Unmapped: RAMIREZ #2H | LOE | 1,200.00 | 0.12500000 | 150.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
- The operator's layout is isolated in one function. When an operator changes its file, one function changes. Everything downstream stays the same.
- Map to well name and cost center, not to a name match. The cross-reference is explicit, so a lookalike well can never be picked up by accident.
- Unmatched rows are kept, not dropped. A left join plus an unmapped query means nothing billed to you disappears silently.
- Check the operator's decimal against your own. An operator billing at the wrong WI is caught before the costs are booked.
- The output matches the accrual and SQL tables. The same columns feed the non-op accruals and the SQL exception queries.
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.