In Episode 1, we saw how quickly you can digitize your invoices with Microsoft Power Automate and AI Builder. But once those invoices become a neat table in Excel, what’s next? How do you turn raw invoice data into useful, actionable insights for your restaurant?
So, we’ve got our US Foods invoice data neatly captured in a spreadsheet – great! But as any chef knows, you can’t just throw raw ingredients into a pot and hope for a great dish. Preparation is key. In the data world, that means cleaning and transforming our raw invoice data so that it’s consistent, accurate, and ready to be analyzed. Think of this step as organizing your kitchen and prepping ingredients before the dinner rush: a bit of effort here makes everything that follows go smoothly.
Data Transformation 101: Using tools like Excel’s Power Query (or Power BI’s built-in Power Query editor), we can automate a lot of the cleanup. Here are some key transformations and fixes we apply to the invoice data:
Standardize and correct data: We fix inconsistencies in spelling, units, and formats. For example, ensure the vendor name is always recorded as “US Foods” (and not sometimes “U.S. Foods”), and that dates use a uniform format. If the invoice has quantity units like CS (case) or EA (each), we make sure those are interpreted consistently (we might translate them to full words or a standard unit of measure for clarity).
Calculate per-unit costs: In many invoices, you get a total price for a line item that represents multiple units (e.g. $120 for 24 bottles of olive oil). We add a calculation to derive the cost per single unit. In this example, $120/24 = $5 per bottle. This “cost per unit” metric is incredibly useful for comparing prices over time and is necessary if we want to link this data to recipe costs later. The RS system, for instance, captures the unit price of each product during this step as part of the extraction process.
Assign categories: Each item gets tagged with an expense category that makes sense for your restaurant’s financial tracking. A case of chicken might be labeled Food – Poultry, milk would be Food – Dairy, and a box of garbage bags might go under Operating Supplies. By assigning a P&L category to every line, we ensure that when we later aggregate the data, every dollar falls into the right bucket (food cost, beverage cost, supplies, etc.) No more guessing whether “Brand X Bacon” is counted in Meat or Dairy – it’s defined upfront to match your chart of accounts.
Prep for Power BI: Finally, we structure the cleaned data for easy loading into our business intelligence tool. This means we end up with a nice flat table where each row is a purchase record and each column is a specific attribute (date, vendor, product, quantity, unit cost, total cost, category, etc.). It’s now in a format that a tool like Power BI can ingest directly. In fact, “we transform the raw dataset by standardizing fields, aligning formats, and prepping it for direct upload into Power BI” – exactly what we’ve done here.
After these transformations, our once-messy invoice data is looking clean and consistent. We’ve essentially turned a stack of purchase receipts into a well-organized dataset. Even better, this cleaning process can run automatically whenever new invoice data comes in (so each week when you get new US Foods invoices, you just drop them in and let the pre-set Power Query steps do the work). At this point, we’ve got what we bought and how much we paid, and each purchase is categorized properly.
But on its own, purchase data only tells us half the story. To get true insight (not just a bunch of numbers), we need to blend this with other data from the restaurant. How do we know if the amount we spent on beef this month is reasonable? We’d have to compare it to how much beef we actually sold, right? That means it’s time to bring in sales data from the POS, and maybe even labor data, to complete the picture. Our costs are prepped and even uploaded into the BI system – as RS puts it, “from invoices to visuals…
Next up: connecting the POS to complete the picture.” In Episode 3, we’ll mix our polished purchase data with sales and labor info, integrating all the ingredients of our business into one stew of insight.
