Build Four Dashboards in Power BI With OData Feeds

Install Power BI Desktop

Power BI Desktop is a free Windows application from Microsoft. You do not need a Microsoft 365 subscription to build and view reports locally — a free Microsoft account is enough (and sign-in is only required to publish to the Power BI Service, not to build reports).

Option A — Microsoft Store (recommended)

1. Press Win + S and type Microsoft Store, then open it.
2. Search for Power BI Desktop.
3. Click Get or Install. The Store version updates automatically in the background.
4. Once installed, launch it from the Start menu.

Option B — Direct download from Microsoft

1. Go to Microsoft's Power BI page and download the Power BI Desktop installer (search "download Power BI Desktop" to find the current download page).
2. Run the .exe installer and follow the setup wizard.
3. Launch Power BI Desktop from the Start menu or desktop shortcut.

Generate an API Key in Standard Time®

Every OData request to Standard Time® must include your API key. You generate it inside the application, copy it once, and paste it into every Power BI query. The key authenticates the request — think of it as a password for your data feed.

Step 1 — Open the OData API Key dialog

In Standard Time®, click the View tab in the ribbon. Look for the OData API Key button in the ribbon (it shows a globe icon with "Generate or manage API keys for OData / PowerBI integrations" as its tooltip).

Step 2 — Generate and copy your key

The OData API Key dialog lists your active keys. To create a new one:

1. Type a descriptive Label (for example, Power BI) in the Label field, then click Change to save the label.
2. Click Generate New Key. A new entry appears in the Active keys list showing the key's prefix (e.g., cN5YC4ZZ…) and its last-used date.
3. Copy the full key immediately — the complete key is displayed only at the moment of generation and cannot be retrieved again. Paste it into a secure text file or password manager right now.
4. Click Close.

Find your Company ID (CID)

Every OData query also includes your company ID (CID), which appears in your Standard Time® cloud URL. CIDs are typically long alphanumeric strings — for example, if your Standard Time® web address is https://stcloud67.com/?cid=1000, then your CID is 1000, though real CIDs are usually much longer. Replace YOUR_CID_HERE in the queries below with your actual CID value.

How to Load Any OData Query

Each dashboard section below lists one or more M language query blocks. Use this same procedure for every query block you encounter.

Steps to paste a query into Power BI

1. Open Power BI Desktop. Start with a new blank report.
2. In the Home ribbon, click Get Data → Blank Query. The Power Query Editor window opens.
3. In the Power Query Editor ribbon, click Home → Advanced Editor. A text editor opens with a simple placeholder query.
4. Select all text in the editor (Ctrl+A), then paste the query block from this guide.
5. Replace YOUR_CID_HERE with your actual CID — a long alphanumeric value found after cid= in your Standard Time® cloud URL (e.g., https://stcloud67.com/?cid=1000, though real CIDs are typically longer).
6. Replace YOUR_APIKEY with your full API key string.
7. Click Done. Power BI connects to Standard Time® and previews the data.
8. In the left panel (Queries pane), right-click the query → Rename and type the name shown above each query block (e.g., TimeLogs).
9. Repeat from step 2 for each additional query in the dashboard section.
10. When all queries for a dashboard are loaded, click Close & Apply in the Power Query ribbon to import the data into Power BI.

Dashboard 1 — Shop Floor Time & Utilization Command Center

Every hour logged on the shop floor — who worked, on which job, in which category, and whether that time is billable. At a glance managers see total hours, billable vs. non-billable split, daily output trend, which employees are most utilized, and which projects are consuming the most time. Designed to be left on a monitor in the production office and refreshed daily.

Step 1 — Load the data

Load all three queries using the procedure above. Name each query exactly as shown.

Query: TimeLogs

let
    DaysBack = 90,

    Cutoff = DateTimeZone.ToText(
        DateTimeZone.LocalNow() - #duration(DaysBack, 0, 0, 0),
        "yyyy-MM-ddTHH:mm:sszzz"
    ),

    Source = OData.Feed(
        "https://stcloud67.com/odata/TimeLogs?cid=YOUR_CID_HERE&$filter=Created gt " & Cutoff,
        null,
        [Headers = [#"X-Api-Key" = "YOUR_APIKEY"]]
    )
in
    Source

Query: Users

let
    Source = OData.Feed(
        "https://stcloud67.com/odata/Users?cid=YOUR_CID_HERE",
        null,
        [Headers = [#"X-Api-Key" = "YOUR_APIKEY"]]
    )
in
    Source

Query: Projects

let
    DaysBack = 9999,

    Cutoff = DateTimeZone.ToText(
        DateTimeZone.LocalNow() - #duration(DaysBack, 0, 0, 0),
        "yyyy-MM-ddTHH:mm:sszzz"
    ),

    Source = OData.Feed(
        "https://stcloud67.com/odata/Projects?cid=YOUR_CID_HERE&$filter=Created gt " & Cutoff,
        null,
        [Headers = [#"X-Api-Key" = "YOUR_APIKEY"]]
    )
in
    Source

Step 2 — Create calculated columns and measures

In Power BI Desktop, click the TimeLogs table in the Data/Fields pane on the right. Use Table Tools → New Column for calculated columns and Home → New Measure for measures.

Create the WeekOf calculated column first (groups logs by calendar week):

New Column on TimeLogs — WeekOf

WeekOf = DATE(YEAR(TimeLogs[Date]), MONTH(TimeLogs[Date]),
    DAY(TimeLogs[Date]) - WEEKDAY(TimeLogs[Date], 2) + 1)

Then add these measures:

New Measure — Total Hours

Total Hours = SUM(TimeLogs[TimeLogDurationHours])

New Measure — Billable Hours

Billable Hours = CALCULATE(SUM(TimeLogs[TimeLogDurationHours]), TimeLogs[Billable] = TRUE())

New Measure — Non-Billable Hours

Non-Billable Hours = [Total Hours] - [Billable Hours]

New Measure — Billable Amount

Billable Amount = SUM(TimeLogs[TimeLogCostClient])

New Measure — Utilization %

Utilization % = DIVIDE([Billable Hours], [Total Hours], 0) * 100

Step 3 — Set up table relationships

Click the Model icon in the left rail (the branching-diagram icon, third from the top). You should see three boxes on the canvas for TimeLogs, Users, and Projects.

1. In the TimeLogs box, click and drag the UserID column onto the UserID column in the Users box. An Edit Relationship dialog opens — verify Many-to-One (*:1), Single direction, Active. Click OK.
2. In the TimeLogs box, drag ProjectID onto ProjectID in the Projects box. Verify Many-to-One, Single, Active. Click OK.
3. Both connections show as solid lines with a * on the TimeLogs side and a 1 on the Users/Projects side.

Step 4 — Build the report page

Click the Report icon (bar chart, top of the left rail) to return to Report view. Build the following seven visuals. For each one: click an empty area of the canvas, click the visual type in the Visualizations pane, then drag fields from the Data pane into the correct wells.

Step 5 — Finish and save

1. Add a Text Box at the top: "Shop Floor Time & Utilization" — 20pt bold, color #2e7d32.
2. Set page background: View → Page background → white (#FFFFFF).
3. File → Save as ST Dashboard 1 - Time & Utilization.pbix

Dashboard 2 — Project Budget vs. Actuals

For every active project: how many hours were estimated, how many have been used, how much is remaining, and whether the project is tracking under or over its quoted cost. A scatter plot reveals which projects are burning hours faster than they're progressing. Built for project managers who need to answer "are we on budget?" before a client call.

Step 1 — Load the data

Query: Projects

let
    DaysBack = 9999,

    Cutoff = DateTimeZone.ToText(
        DateTimeZone.LocalNow() - #duration(DaysBack, 0, 0, 0),
        "yyyy-MM-ddTHH:mm:sszzz"
    ),

    Source = OData.Feed(
        "https://stcloud67.com/odata/Projects?cid=YOUR_CID_HERE&$filter=Created gt " & Cutoff,
        null,
        [Headers = [#"X-Api-Key" = "YOUR_APIKEY"]]
    )
in
    Source

Query: ProjectTasks

let
    DaysBack = 9999,

    Cutoff = DateTimeZone.ToText(
        DateTimeZone.LocalNow() - #duration(DaysBack, 0, 0, 0),
        "yyyy-MM-ddTHH:mm:sszzz"
    ),

    Source = OData.Feed(
        "https://stcloud67.com/odata/ProjectTasks?cid=YOUR_CID_HERE&$filter=Created gt " & Cutoff,
        null,
        [Headers = [#"X-Api-Key" = "YOUR_APIKEY"]]
    )
in
    Source

Query: Clients

let
    Source = OData.Feed(
        "https://stcloud67.com/odata/Clients?cid=YOUR_CID_HERE",
        null,
        [Headers = [#"X-Api-Key" = "YOUR_APIKEY"]]
    )
in
    Source

Step 2 — Create measures and columns

Select the Projects table in the Data pane, then create the following measures and column.

New Measure — Avg % Complete

Avg % Complete = AVERAGEX(ProjectTasks, ProjectTasks[PercentComplete])

New Measure — Total Quoted Cost

Total Quoted Cost = SUM(Projects[ProjectQuotedCost])

New Measure — Total Actual Billable

Total Actual Billable = SUM(ProjectTasks[TaskCostClientActual])

New Measure — Budget Variance

Budget Variance = [Total Quoted Cost] - [Total Actual Billable]

New Measure — Budget Used %

Budget Used % = DIVIDE([Total Actual Billable], [Total Quoted Cost], 0) * 100

New Measure — Total Actual Hours

Total Actual Hours = SUM(ProjectTasks[ActualHours])

New Measure — Total Estimated Hours

Total Estimated Hours = SUM(ProjectTasks[TaskDurationHours])

New Measure — Total Remaining Hours

Total Remaining Hours = SUM(ProjectTasks[RemainingHours])

Add this calculated column to the Projects table (Table Tools → New Column):

New Column on Projects — Health Status

Health Status =
VAR pct = DIVIDE(
    CALCULATE(SUM(ProjectTasks[TaskCostClientActual]),
              ProjectTasks[ProjectID] = Projects[ProjectID]),
    Projects[ProjectQuotedCost], 0) * 100
RETURN
    IF(pct > 100, "Over Budget",
    IF(pct > 80,  "At Risk",
                  "On Track"))

Step 3 — Set up relationships

In Model view, create these connections (all Many-to-One, Single direction, Active):

• Drag ProjectTasks[ProjectID] → Projects[ProjectID]
• Drag Projects[ClientID] → Clients[ClientID]

Step 4 — Build the report page

Step 5 — Finish and save

Page background: very light green #f1f8e9. Title text box: "Project Budget vs. Actuals" — 20pt bold #1b5e20. Save as ST Dashboard 2 - Project Budget.pbix.

Dashboard 3 — Full Job Cost Command Center (Labor + Materials)

The complete picture of what a job actually costs: hours × rate for labor, plus every material and expense charged against the project. Standard Time®'s Expenses table captures every inventory scan and manual expense entry, so this dashboard shows true job cost (labor + materials) in one view. Managers can answer "how much did job #47 really cost us?" including parts consumed and mileage.

Step 1 — Load the data

Query: TimeLogs

let
    DaysBack = 365,

    Cutoff = DateTimeZone.ToText(
        DateTimeZone.LocalNow() - #duration(DaysBack, 0, 0, 0),
        "yyyy-MM-ddTHH:mm:sszzz"
    ),

    Source = OData.Feed(
        "https://stcloud67.com/odata/TimeLogs?cid=YOUR_CID_HERE&$filter=Created gt " & Cutoff,
        null,
        [Headers = [#"X-Api-Key" = "YOUR_APIKEY"]]
    )
in
    Source

Query: Expenses

let
    DaysBack = 365,

    Cutoff = DateTimeZone.ToText(
        DateTimeZone.LocalNow() - #duration(DaysBack, 0, 0, 0),
        "yyyy-MM-ddTHH:mm:sszzz"
    ),

    Source = OData.Feed(
        "https://stcloud67.com/odata/Expenses?cid=YOUR_CID_HERE&$filter=Created gt " & Cutoff,
        null,
        [Headers = [#"X-Api-Key" = "YOUR_APIKEY"]]
    )
in
    Source

Query: Projects

let
    DaysBack = 9999,

    Cutoff = DateTimeZone.ToText(
        DateTimeZone.LocalNow() - #duration(DaysBack, 0, 0, 0),
        "yyyy-MM-ddTHH:mm:sszzz"
    ),

    Source = OData.Feed(
        "https://stcloud67.com/odata/Projects?cid=YOUR_CID_HERE&$filter=Created gt " & Cutoff,
        null,
        [Headers = [#"X-Api-Key" = "YOUR_APIKEY"]]
    )
in
    Source

Query: Clients

let
    Source = OData.Feed(
        "https://stcloud67.com/odata/Clients?cid=YOUR_CID_HERE",
        null,
        [Headers = [#"X-Api-Key" = "YOUR_APIKEY"]]
    )
in
    Source

Step 2 — Build the unified CostLines table

This is the key step that makes Dashboard 3 unique: merging TimeLogs and Expenses into a single CostLines table so every visual can show combined labor + material cost in one view.

1. In Power BI Desktop, click Home → Transform data to open Power Query Editor.
2. In the ribbon, click Home → Append Queries → Append Queries as New.
3. Choose Two tables: Primary = TimeLogs, Secondary = Expenses. Click OK.
4. In the Queries pane on the left, right-click the new query and rename it CostLines.
5. With CostLines selected, click Add Column → Custom Column. Name it CostType and enter this formula:

if [TimeLogDurationHours] = null then "Material" else "Labor"

6. Add a second custom column. Name it CostAmount:

if [CostType] = "Labor" then [TimeLogCostClient] else [ExpenseAmount]

7. Click Close & Apply.

Step 3 — Create measures

Select the CostLines table:

New Measure — Total Labor Cost

Total Labor Cost =
CALCULATE(SUM(CostLines[CostAmount]), CostLines[CostType] = "Labor")

New Measure — Total Material Cost

Total Material Cost =
CALCULATE(SUM(CostLines[CostAmount]), CostLines[CostType] = "Material")

New Measure — Total Job Cost

Total Job Cost = [Total Labor Cost] + [Total Material Cost]

New Measure — Labor %

Labor % = DIVIDE([Total Labor Cost], [Total Job Cost], 0) * 100

New Measure — Material %

Material % = DIVIDE([Total Material Cost], [Total Job Cost], 0) * 100

Select the Expenses table:

New Measure — Unbilled Expenses

Unbilled Expenses =
CALCULATE(SUM(Expenses[ExpenseAmount]),
    Expenses[Billable] = TRUE(),
    Expenses[Billed] = FALSE())

New Measure — Pending Reimbursements

Pending Reimbursements =
CALCULATE(SUM(Expenses[ExpenseAmount]),
    Expenses[Reimbursable] = TRUE(),
    Expenses[Reimbursed] = FALSE())

Add a MonthYear calculated column to CostLines for the monthly trend chart (Table Tools → New Column):

New Column on CostLines — MonthYear

MonthYear = FORMAT(
    IF(NOT(ISBLANK(CostLines[Date])), CostLines[Date], CostLines[ExpenseDate]),
    "YYYY-MM"
)

Step 4 — Set up relationships

In Model view (all Many-to-One, Single, Active):

• Drag TimeLogs[ProjectID] → Projects[ProjectID]
• Drag Expenses[ProjectID] → Projects[ProjectID]
• Drag Expenses[ClientID] → Clients[ClientID]
• Drag Projects[ClientID] → Clients[ClientID]

Step 5 — Build the report page

Step 6 — Finish and save

Page background: near-white warm #FFFFF8. Title text box: "Full Job Cost — Labor + Materials" — 20pt bold #1b5e20. Use amber (#ff8f00) consistently for all material visuals to distinguish from labor green. Save as ST Dashboard 3 - Job Cost.pbix.

Dashboard 4 — Inventory Health & Consumption Radar

A live view of shop floor inventory: what's in stock, what's falling below the reorder threshold, how much each item costs, and which materials are being consumed fastest (tracked via Expenses records created by barcode scans). Items in red need ordering before production stalls.

Step 1 — Load the data

Query: Inventory

let
    Source = OData.Feed(
        "https://stcloud67.com/odata/Inventory?cid=YOUR_CID_HERE",
        null,
        [Headers = [#"X-Api-Key" = "YOUR_APIKEY"]]
    )
in
    Source

Query: MaterialScans

Fetches the last 90 days of Expense records where InventoryName is not blank — these are material consumption events created by inventory barcode scans.

let
    DaysBack = 90,

    Cutoff = DateTimeZone.ToText(
        DateTimeZone.LocalNow() - #duration(DaysBack, 0, 0, 0),
        "yyyy-MM-ddTHH:mm:sszzz"
    ),

    Source = OData.Feed(
        "https://stcloud67.com/odata/Expenses?cid=YOUR_CID_HERE&$filter=Created gt " & Cutoff,
        null,
        [Headers = [#"X-Api-Key" = "YOUR_APIKEY"]]
    ),

    MaterialScans = Table.SelectRows(Source, each [InventoryName] <> null and [InventoryName] <> "")
in
    MaterialScans

Query: Projects

let
    DaysBack = 9999,

    Cutoff = DateTimeZone.ToText(
        DateTimeZone.LocalNow() - #duration(DaysBack, 0, 0, 0),
        "yyyy-MM-ddTHH:mm:sszzz"
    ),

    Source = OData.Feed(
        "https://stcloud67.com/odata/Projects?cid=YOUR_CID_HERE&$filter=Created gt " & Cutoff,
        null,
        [Headers = [#"X-Api-Key" = "YOUR_APIKEY"]]
    )
in
    Source

Step 2 — Create calculated columns

Select the Inventory table (Table Tools → New Column for each):

New Column — Stock Status

Stock Status =
IF(Inventory[QtyInStock] <= 0, "Out of Stock",
IF(Inventory[QtyInStock] <= Inventory[QtyWhenToReorder], "Reorder Now",
IF(Inventory[QtyInStock] <= Inventory[QtyWhenToReorder] * 1.25, "Low",
   "OK")))

New Column — Inventory Value

Inventory Value = Inventory[QtyInStock] * Inventory[CostUnit]

New Column — Margin Per Unit

Margin Per Unit = Inventory[CostClient] - Inventory[CostUnit]

New Column — Stock Gap

Stock Gap =
IF(Inventory[QtyInStock] < Inventory[QtyWhenToReorder],
   Inventory[QtyWhenToReorder] - Inventory[QtyInStock],
   0)

Step 3 — Create measures

Select the Inventory table:

New Measure — Total Inventory Value

Total Inventory Value = SUMX(Inventory, Inventory[QtyInStock] * Inventory[CostUnit])

New Measure — Items Below Reorder

Items Below Reorder =
CALCULATE(
    COUNTROWS(Inventory),
    Inventory[QtyInStock] <= Inventory[QtyWhenToReorder],
    Inventory[Active] = TRUE()
)

New Measure — Items Out of Stock

Items Out of Stock =
CALCULATE(
    COUNTROWS(Inventory),
    Inventory[QtyInStock] <= 0,
    Inventory[Active] = TRUE()
)

Select the MaterialScans table:

New Measure — Total Units Consumed (90 days)

Total Units Consumed = SUM(MaterialScans[ExpenseQty])

New Measure — Total Material Cost (90 days)

Total Material Cost 90d = SUM(MaterialScans[ExpenseAmount])

Step 4 — Set up relationships

In Model view (all Many-to-One, Single, Active):

• Drag MaterialScans[InventoryName] → Inventory[Name]
• Drag MaterialScans[ProjectID] → Projects[ProjectID]

Step 5 — Build the report page

Step 6 — Finish and save

Page background: warm light yellow #FFFDE7. Title text box: "Inventory Health & Consumption Radar" — 20pt bold. Use red/amber/green consistently: green = healthy, amber = watch, red = act now. Save as ST Dashboard 4 - Inventory Radar.pbix.

OData Column Quick Reference

Key columns available from each Standard Time® OData endpoint. Use these to add additional fields to visuals or build custom measures.

Tips & Troubleshooting