A live-build companion for turning water-testing and waste-stream data into a Power BI reporting tool. It reshapes a wide historian export into one row per reading, puts the source behind a parameter so the go-live is a single value change, flags the four kinds of bad process reading rather than deleting them, builds a star schema with a marked date table, writes measures for the mean, a rolling average and three-sigma control limits that draw flat, then reads the resulting control chart and dose-against-pH scatter to answer the chemical dosing question, closing on the gateway and the go-live checklist.
Subject: Business Analytics · 63 slides · applied lesson
Open the interactive version of this deck · Homework for this lesson
Title
Building the reporting tool
From a historian export to a live report, with the swap planned in from the start
Objectives
The plan we agreed: you walk me through how the data comes out of the historian and which tools you use, and we build the whole report against mock data that has the same shape. When it goes live, one value changes and nothing else does.
Microsoft Learn, Understand star schema and the importance for Power BI why the model shape comes first
Section
Framing
Concept
There is a real difference between a chart that answers today's question and a tool that answers next month's without being rebuilt, and almost all the difference is decided in the first twenty minutes.
Everything in this deck is a choice in favour of the second one, and each choice costs a few extra minutes today.
Prediction
You have built the report on a sample export. A month later the plant adds a second pH probe on another stream.
Predict first
In a report built the quick way, what breaks first?
Correct: The visuals, because a new column appeared that nothing knows about
Why: A wide export gives every tag its own column, so a new probe is a new column. Every measure, axis and legend was written against the columns that existed on build day, so none of them see it. Reshaping to one row per reading is what makes a new tag invisible to everything downstream.
Concept
You suggested building on mock data and plugging the live link in afterwards. That is exactly right, and it is worth being precise about the condition that makes it work.
The mock data has to match the live data in shape, not in values. Same column names, same data types, same tag naming, same timestamp format. Get those four right and the swap is genuinely a one-line change. Get any of them wrong and you will rebuild the queries at go-live, which is the thing we are trying to avoid.
Microsoft Learn, Power Query parameters parameters as the swap point
Picture it
Six stages. Note how few of them the go-live actually touches.
Figure (svg): A left-to-right pipeline from historian through export, Power Query, model, DAX and report, with only the first box outlined as the part that changes at go-live
That is the target: everything to the right of the export is written against the shape, so it survives the swap untouched.
Socratic
Before we build anything, say the condition out loud. It governs every later decision.
Discussion prompt
What must be identical between the mock file and the live historian export for the swap to require no rework?
Hint: Which of Power Query's steps refer to something by name?
Answer:
The column names, so every step in Power Query still finds what it referenced.
The data types, so no step silently changes behaviour — a timestamp read as text will break every time calculation without erroring.
The tag naming, so the tag dimension still joins, and the timestamp format, so parsing does not need a different locale.
Notice what does not have to match: the number of rows, the date range, or any of the values. Shape, not content.
Section
Reshaping
Concept
Almost every historian thinks in the same three things, whatever the vendor. A tag names the thing being measured, a timestamp says when, and a value says what it read.
tag — the historian's name for one measured point, such as the pH probe on the acid waste line
Your export may present that as a grid with one column per tag, which is convenient to read and useless to model. The fix is a single Power Query step, and it is the most important step in the whole build.
Microsoft Learn, Unpivot columns in Power Query unpivot columns
Picture it
The same three readings, twice. The difference decides whether the report survives a new probe.
Figure (svg): A wide table with one column per tag beside a long table with one row per reading, joined by an arrow labelled unpivot
In the long shape a new tag is more rows. Rows are free — visuals, measures and filters all handle them without being touched. Columns are expensive, because everything downstream names them.
Pattern
This is the single rule that separates a model that ages well from one that does not, and it generalises far past Power BI.
| if this is in a column heading | it should be | because |
|---|---|---|
| a tag name such as pH_01 | a value in a Tag column | new tags then arrive as rows |
| a month such as Aug-26 | a date in the Timestamp column | time intelligence needs real dates |
| a stream name | a value in a Stream column | you can slice by it, and add streams freely |
| a unit such as mg/L | an attribute of the tag | units belong to the tag, not the reading |
Ask of every column heading: is this a name or a measurement? Names belong in rows. Only measurements deserve their own column.
Microsoft Learn, Understand star schema and the importance for Power BI dimensional modelling
Worked example
One step in the Power Query editor, and the M it writes for you.
Select the columns that stay
Why: Timestamp is the only column that is not a tag, so select it, right-click, and choose Unpivot Other Columns.
let
Source = Csv.Document(File.Contents(SourcePath), [Delimiter=",", Encoding=65001]),
Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
Unpivoted = Table.UnpivotOtherColumns(Promoted, {"Timestamp"}, "Tag", "Value")
in
UnpivotedRead what it produced
Why: Two new columns, Tag and Value, and one row per tag per timestamp.
| Timestamp | Tag | Value |
|---|---|---|
| 2026-08-28 08:00 | pH_01 | 6.1 |
| 2026-08-28 08:00 | Dose_01 | 12.4 |
| 2026-08-28 08:15 | pH_01 | 6.4 |
| 2026-08-28 08:15 | Dose_01 | 12.4 |
Figure (svg): A result panel showing the row count going from four wide rows to eight long rows after the unpivot
Verify: the row count
Why: Unpivoting a grid of four timestamps and two tags must give eight rows. If you get four, you unpivoted the wrong direction; if you get more than eight, a column you meant to keep was unpivoted too. Counting rows catches both instantly.
Microsoft Learn, Unpivot columns in Power Query Unpivot Other Columns
Check
The distinction that costs people twenty minutes the first time.
Check your understanding
You select the Timestamp column and choose Unpivot Columns rather than Unpivot Other Columns. What happens?
Answer: B
Why: Unpivot Columns acts on what you selected; Unpivot Other Columns acts on everything you did not select. Selecting Timestamp and choosing the first collapses the one column you wanted to keep and leaves the tags spread across columns — exactly backwards.
Discrimination
Sort the columns of a typical historian export by whether they belong as columns in the long shape.
Sort into buckets
Column, or a value inside a column?
Section
The go-live plan
Concept
This is worth being clear about, because it is what makes the whole mock-first plan possible. Power Query does not import your data and keep it. It records the steps you performed, and replays them from scratch on every refresh.
So the queries you write today against a mock file are a recipe. Point the recipe at a different file with the same shape and it runs unchanged. That is not a workaround; it is how the tool is designed.
Microsoft Learn, Power Query parameters queries as repeatable steps
Worked example
Three minutes now, and the go-live becomes a single edit.
Create the parameter
Why: Home, then Manage Parameters, then New. Name it SourcePath, type Text, current value the mock file path.
Point the query at it
Why: In the source step, replace the literal path with the parameter name. This is the only edit.
// before — the path is welded into the query
Source = Csv.Document(File.Contents("C:/mock/readings.csv"))
// after — the path is a value you can change from the UI
Source = Csv.Document(File.Contents(SourcePath))Test the swap now, not at go-live
Why: Point SourcePath at a second mock file with the same columns and refresh. If everything still works, the swap is proven.
Verify: that nothing downstream referenced the path
Why: Search the applied steps for the original path string. If it appears anywhere else — a merge, an append, a second query — that copy will not follow the parameter, and go-live will half-work in a way that is genuinely hard to diagnose.
Microsoft Learn, Power Query parameters creating and using parameters
Picture it
The same report, before and after go-live.
Figure (svg): Two panels showing the SourcePath parameter set to a mock file and then to the historian export, with a note that everything downstream is untouched
This is the concrete answer to what you proposed by message: build on mock data, then plug in the link. The parameter is the plug.
Fill the middle
Complete the two halves of the go-live edit.
Fill in the blanks
At go-live I change the value of the SourcePath parameter and I change zero other things.
Why: If the answer to the second blank is anything but zero, the build was not parameterised properly and something downstream is still naming the file directly. That is worth checking today rather than discovering it on the morning you go live.
Trap
The report needs the readings and a small lookup of tag descriptions, so there are two queries.
Parameterise the readings query
Why: SourcePath is used correctly there, and the swap is tested and works.
Leave the lookup query pointing at the mock folder
Why: At go-live the readings are live and the tag names are still the mock ones. Nothing errors — the report simply shows the wrong labels against the right numbers.
The report needs the readings and a small lookup of tag descriptions, so there are two queries.
Parameterise a folder, not a file
Why: Make the parameter the folder, and build each file path from it, so every query in the report inherits the swap.
Prove it by searching the steps
Why: Search every query's applied steps for the literal mock path. Zero hits means the swap really is complete, and the silent-wrong-labels failure cannot happen.
Section
The four bad reads
Concept
Set every column's data type as the first thing after the source step, and set it explicitly rather than letting Power Query guess.
The reason is specific to what you are building. A timestamp that arrives as text will not error — it will simply sit there, and every time calculation you write later will silently return blank. Diagnosing that from the far end of the build takes an hour; setting the type takes ten seconds.
Microsoft Learn, Set and use date tables in Power BI Desktop why date columns must be real dates
Picture it
Process data is not survey data. It contains these four in every export, and each needs a different decision.
Figure (svg): Four labelled rows naming a sensor flatline, an out-of-range spike, a null, and an exact zero, each with its likely cause
You know your process and I do not, so this is a slide where you tell me the rule. Which of these is a genuine reading on your acid streams, and which is always an artefact?
Definition probe
There are only three defensible answers, and blanket-deleting is rarely one of them.
Sort into buckets
What should the query do with this row?
Worked example
A conditional column costs nothing and keeps the decision reversible, which matters when the rule turns out to be wrong.
// Add Column > Conditional Column, or paste this step
AddFlag = Table.AddColumn(Unpivoted, "IsSuspect", each
if [Value] = null then true
else if [Tag] = "pH_01" and ([Value] < 0 or [Value] > 14) then true
else false, type logical)Figure (svg): A result panel listing four readings with their IsSuspect flag set true or false
Filter in the measure, not in the query
Why: Leaving the row in the table means you can still count how many suspect readings there were, which is itself a useful number about the instrument.
Give the flag a home in the report
Why: A card showing the suspect count tells you when a probe needs calibration before it tells you anything about the process.
Verify: that the flag is doing something
Why: Filter the table to IsSuspect and look. If it is empty on data you know contains bad reads, the condition is wrong; if it catches half the rows, the range is too tight. Either way, look at the rows before trusting the rule.
NIST/SEMATECH e-Handbook of Statistical Methods, chapter 6 — process monitoring and control charts chapter 6.1, what a process signal is
Two truths and a lie
Two of these are safe habits. One quietly destroys the thing you are trying to measure.
Eliminate the wrong options
Cross out the bad advice.
Survives elimination: t3
Why: On process data the outliers are frequently the entire point. An excursion outside the control limits is the event you are monitoring for, and a report that quietly removes it in order to look tidy is worse than no report at all. Make the axis handle the range instead.
Section
Star schema and a date table
Concept
The reshaped readings table could be the whole model, and for a first chart it would work. It stops working for two specific reasons, both of which you will hit within a week.
Microsoft Learn, Understand star schema and the importance for Power BI star schema guidance
Picture it
One fact table in the middle, three small dimensions around it.
Figure (svg): A star schema with a central Readings fact table joined to Date, Tag and Stream dimension tables
Readings holds one row per measurement and nothing else. Everything you slice by lives in a dimension. This shape is what makes the report fast and the DAX short.
Worked example
Written in DAX as a calculated table, so it rebuilds itself from the data range on every refresh.
Date =
ADDCOLUMNS (
CALENDAR ( MIN ( Readings[Timestamp] ), MAX ( Readings[Timestamp] ) ),
"Year", YEAR ( [Date] ),
"MonthNo", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "MMM" ),
"Day", DAY ( [Date] )
)Figure (svg): A result panel showing the first rows of the generated calendar table with year, month number and month name
Mark it as the date table
Why: Table tools, then Mark as Date Table, choosing the Date column. Without this step the time intelligence functions have no reliable calendar to walk.
Relate it to the readings
Why: One-to-many from Date to Readings on the date part of the timestamp.
Sort the month name by its number
Why: Otherwise every chart puts April first, because the labels sort alphabetically.
Verify: that the calendar has no gaps
Why: Put a count of rows against the date and look for missing days. Time intelligence silently misbehaves on a calendar with holes, and CALENDAR only guarantees contiguity if the min and max are genuine dates rather than text.
Microsoft Learn, Set and use date tables in Power BI Desktop mark as date table
Check
Solve it on paper before you click.
Check your understanding
You built a date table but skipped Mark as Date Table. What is the practical consequence?
Answer: B
Why: Marking the table tells the engine which column is the contiguous calendar, and functions like DATESINPERIOD rely on that to walk dates correctly. Unmarked, they may fall back on inferred behaviour and return results that are wrong without being obviously wrong.
Matching
If you can say what each table is for, you can tell immediately where a new field belongs.
Match the pairs
Why: The test for a new field is simple: if you would ever put it on a slicer, an axis or a legend, it belongs in a dimension. If it is a number you would aggregate, it belongs in the fact table. Units go on Tag, not on Readings, because units describe the probe rather than the individual reading.
Section
Measures, rolling averages and limits
Concept
There are two ways to compute something and they are not interchangeable, though they look it from the ribbon.
A calculated column is evaluated once per row when the data refreshes, and the result is stored in the file. A measure is evaluated when a visual asks for it, against whatever filters that visual has. Almost everything you want here is a measure.
Microsoft Learn, Tutorial: Create your own measures in Power BI Desktop creating measures
Picture it
The single distinction that explains every surprising DAX result you will get in the first month.
Figure (svg): Two panels comparing a calculated column, computed at refresh and stored per row, with a measure, computed at query time and reacting to slicers
The line that matters is the fourth: a calculated column cannot react to a slicer, because it was computed long before the slicer existed. If the number should change when the user filters, it must be a measure.
Worked example
Enough to put a real chart on the page, and written so they read like sentences.
Mean pH =
CALCULATE ( AVERAGE ( Readings[Value] ), Readings[Tag] = "pH_01", Readings[IsSuspect] = FALSE )
Sigma pH =
CALCULATE ( STDEV.P ( Readings[Value] ), Readings[Tag] = "pH_01", Readings[IsSuspect] = FALSE )
Suspect Readings =
CALCULATE ( COUNTROWS ( Readings ), Readings[IsSuspect] = TRUE )Figure (svg): A result panel showing the three measures returning a mean, a standard deviation and a suspect count
Read what CALCULATE is doing
Why: It evaluates the aggregation with the filters you name added to whatever the visual already applied. That is how one measure serves every chart on the page.
Note where the suspect rows go
Why: They are excluded from the mean and the sigma, and counted separately. That is the payoff for flagging rather than deleting.
Verify: the mean against a filtered table
Why: Drop the readings into a table visual, filter to the pH tag by hand, and compare the eyeball average to the measure. They should agree. If the measure is noticeably lower, the suspect filter is not being applied and nulls are dragging it down.
Microsoft Learn, Tutorial: Create your own measures in Power BI Desktop CALCULATE and filter context
Comparison
Work down the column and the rule becomes obvious rather than memorised.
Comparison matrix
| you want | measure | calculated column |
|---|---|---|
| the average pH for whatever is on screen | yes, it must follow the filters | no, it would be frozen at refresh time |
| a shift label to slice by, such as day or night | no, a measure cannot go on a slicer | yes, this is exactly what columns are for |
| the count of suspect readings | yes | no |
| a flag marking each row as suspect | no, it is a property of the row | yes, or better, do it in Power Query |
| the difference from the target pH | yes, when reported as an aggregate | yes, when needed per reading |
The rule underneath: if you want to slice or group by it, it is a column. If you want to aggregate it, it is a measure. The last row is genuinely both, which is why the question is worth asking each time.
Worked example
The single most useful measure on process data, because it separates a trend from the noise of individual readings.
pH 7-day avg =
CALCULATE (
[Mean pH],
DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -7, DAY )
)Figure (svg): A chart of individual pH readings as grey dots with a yellow seven-day rolling average line running through them
Read it as an instruction
Why: For each point on the chart, take the latest date visible, walk back seven days, and average over that window.
Note why the date table was needed
Why: DATESINPERIOD walks the calendar. Without a marked, contiguous date table it has nothing reliable to walk.
Verify: at the left-hand edge of the chart
Why: The first six days have less than a full window behind them, so their averages are computed over fewer days and will look unstable. That is expected, not a bug — but decide now whether to show them or start the axis a week in.
Microsoft Learn, Set and use date tables in Power BI Desktop time intelligence requirements
Fill the middle
Same measure, a thirty-day window instead of seven.
Fill in the blanks
DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -30, DAY )
Why: The third argument is the number of intervals and the fourth is the interval. It must be negative to look backwards; a positive thirty would average the next thirty days, which on a live report means averaging over data that does not exist yet and quietly returning a shorter window.
Concept
This is the piece that turns a chart of pH into a tool for dosing decisions. The limits are computed from the process itself, not chosen by anyone.
Take the mean of the readings and the standard deviation, and draw lines three standard deviations either side. A process in control stays inside those lines almost all the time. A point outside is a signal that something changed.
They are not specification limits. Specification limits say what the permit or the customer requires; control limits say what your process actually does. They are different numbers and confusing them is the classic error — a process can sit comfortably inside its control limits and still be out of specification, which is the most important thing a chart can tell you.
NIST/SEMATECH e-Handbook of Statistical Methods, chapter 6 — process monitoring and control charts chapter 6.3, control charts
Worked example
Two measures, written so the limits draw as flat lines rather than recomputing at every point.
UCL pH =
CALCULATE ( [Mean pH] + 3 * [Sigma pH], ALLSELECTED ( 'Date'[Date] ) )
LCL pH =
CALCULATE ( [Mean pH] - 3 * [Sigma pH], ALLSELECTED ( 'Date'[Date] ) )Understand what ALLSELECTED is for
Why: It removes the date filter coming from the axis while respecting the slicers the user set. Without it, each point on the line computes its limits from that one day, and you get a wobbling band instead of a limit.
Put all four on one chart
Why: The readings, the mean, and the two limits.
Verify: that the limits are flat
Why: Look at the chart. If the limit lines follow the data up and down, ALLSELECTED is missing or is naming the wrong column, and the chart is showing you nothing at all.
NIST/SEMATECH e-Handbook of Statistical Methods, chapter 6 — process monitoring and control charts chapter 6.3.2
Picture it
What all of that produces, and the one thing it lets you say that a plain line chart does not.
Figure (svg): A control chart of pH readings over time with a green mean line and dashed red upper and lower control limits, with one point above the upper limit highlighted
One point sits above the upper limit. Everything else is ordinary variation. Without the limits drawn you would have argued about three or four of those dips; with them, there is exactly one thing to investigate.
Check
Solve it on paper before you click.
Check your understanding
Your pH control chart shows every point inside the limits, but half of them sit below the pH your discharge permit requires. What does the chart tell you?
Answer: B
Why: Control limits describe what the process does; the permit describes what it must do. Staying inside the limits means the process is predictable and consistent — and it is consistently in the wrong place. That is a dosing set-point problem, not a stability problem, and it is fixed by shifting the target rather than by chasing individual readings.
Edge cases
The limits assume something. Worth knowing what, before you rely on them.
Discussion prompt
You compute control limits over a period during which the dosing set-point was changed twice. What is wrong with the limits?
Hint: What does the standard deviation include if the mean moved partway through?
Answer:
They are computed across three different processes as though they were one, so the standard deviation includes the jumps between set-points as if that were ordinary variation.
The limits come out far too wide. A genuine excursion within any one period will sit comfortably inside them, and the chart will report that everything is fine.
The fix is to compute the limits over a period when the process was stable, and hold them fixed afterwards — which is why real control charts fix their limits from a baseline period rather than recomputing them from all data on every refresh.
Section
Answering the dosing question
Concept
The report exists to answer a small number of questions. Write them down first, and let each one choose its own visual.
| the question | the visual | why that one |
|---|---|---|
| is the process stable right now | control chart over time | limits make a signal visible against noise |
| how much chemical for a target pH | scatter of dose against resulting pH | shows the relationship, not the totals |
| is the trend moving | rolling average line | smooths out reading-to-reading noise |
| which stream is worst | bar chart by stream | comparison across a few categories |
| can I trust today's data | card showing suspect readings | one number, checked before the others |
Notice that not one of these is a pie chart, and only one of them is a total. Process reporting is almost entirely about variation over time and relationships between variables.
Picture it
This is the chart the whole build exists for.
Figure (svg): A scatter plot of chemical dose against resulting pH with a shaded target band between pH six and seven
The band is your target. The scatter tells you which doses land inside it and how tightly. That is a directly actionable answer, and it is one your current spreadsheet cannot give you.
Real world
Worth naming the operational outcome, because it decides what goes on page one.
Discussion prompt
Once this report exists, what should an operator be able to do in ten seconds that they cannot do today?
Hint: What decision gets made most often, and what does it currently rely on?
Answer:
See whether the last shift's pH stayed inside the control limits, without reading a list of numbers.
See the dose that has historically landed the stream inside the target band, rather than adjusting from memory.
See whether today's data is trustworthy at all, from the suspect-readings count, before drawing any conclusion from the rest of the page.
If page one does not answer those three in ten seconds, it is the wrong page one — and everything else belongs on a second tab.
Ranking
Doing these out of order is what turns a two-hour build into a two-day one.
Put in order
Why: Shape before model, model before measures, measures before visuals. Every one of those steps is cheap when the one before it is right and expensive when it is not — building visuals against a flat table and then fixing the model means rebuilding every visual. The swap is last precisely because everything before it was written not to care.
Trap
You drag Value onto a chart to see pH over the day.
Power BI sums it
Why: Sum is the default for a numeric column, so the chart shows the total of every pH reading in each bucket.
The line climbs all day
Why: It looks like a dramatic upward trend. It is the count of readings in disguise, and it is meaningless — pH does not add.
You drag Value onto a chart to see pH over the day.
Use a measure instead of the raw column
Why: Mean pH already says AVERAGE, and it already excludes the suspect rows, so there is no default to get wrong.
Make it a habit
Why: Never put a raw numeric column on a visual in this report. Everything numeric goes through a measure, which is also why the measures were written before the visuals.
Section
Go-live
Concept
The report file is published to the Power BI service. The data is not — the service re-runs your queries against the source on a schedule, and that is where the plant network becomes relevant.
If the source is a file on your machine or a server inside the plant, the service cannot reach it. An on-premises data gateway is a small service that runs inside the network and acts as the only component able to see both sides.
Microsoft Learn, What is an on-premises data gateway? on-premises data gateway
Picture it
Three boxes, and one of them is the reason scheduled refresh either works or does not.
Figure (svg): A flow from the on-site historian through a gateway to the Power BI service, with a panel explaining why the gateway is required
Worth finding out early whether your IT group already runs a gateway. If they do, scheduled refresh is a form to fill in. If they do not, that is a conversation to start now rather than the week you want to share the report.
Worked example
The whole procedure, assuming everything above was done.
Confirm the live export matches the mock shape
Why: Column names, data types, tag naming, timestamp format. This is the only thing that can go wrong.
Figure (svg): A result panel showing the row count jumping from the mock file size to the live historian size after the swap
Change the parameter
Why: One value. In Desktop it is Transform Data, then Manage Parameters; in the service it is the dataset settings.
Refresh and watch the row count
Why: It should jump from the mock size to the real size. If it does not move, the parameter is not being read.
Check the suspect count before anything else
Why: Real data contains bad reads that mock data does not. If the flag catches nothing on live data, the rule was tuned to the mock file and needs revisiting.
Recompute the control limits from a stable baseline period
Why: The mock limits mean nothing. Pick a period when the process was steady and fix the limits from it.
Verify: one number by hand
Why: Pick a single day, filter the live table to it, and average the pH yourself. If it matches the card, the whole chain from source to measure is intact and you can trust the rest of the page.
Microsoft Learn, What is an on-premises data gateway? dataset settings and scheduled refresh
Check
Solve it on paper before you click.
Check your understanding
You change the parameter, refresh, and the row count does not move. What is the most likely cause?
Answer: B
Why: An unchanged row count means the same file was read again. The usual reason is that the source step was edited to use the parameter in one query but a second query, or a merge step, still holds the literal path. Searching every applied step for the old path string finds it in seconds.
Missing information
Genuinely open questions, because these are decisions about your process and not about the tool.
Discussion prompt
Before this becomes a real report rather than a demonstration, what four things do I need from you?
Hint: Everything the report asserts about good and bad has to come from somewhere.
Answer:
The tag list: which points matter, what each is called in the historian, and its units.
The valid range for each tag, so the suspect rule is yours rather than my guess.
The target band for pH on each stream, and whether it comes from a permit or from practice.
A baseline period when the process was known to be stable, to fix the control limits from.
The spreadsheet you are assembling has the first of those. The other three are quicker to answer out loud than to write down, which is a good use of the back half of this session.
Section
What you can now build alone
Pattern
Strip away the clicking and the whole build is seven decisions, each of which you can now make deliberately.
Every one of those is a choice against the fastest path today and for the report still being right in six months.
Microsoft Learn, Understand star schema and the importance for Power BI the guidance underneath most of these
Connect it up
Before the next session, on one page, with your tags rather than mine.
Draw it
Draw your star: the Readings table in the middle, and around it the dimensions you actually need. Write your real tag names into the Tag table and your real stream names into the Stream table. Then list the four questions you want page one to answer, and beside each the visual you would choose.
Bring that page. It is faster to correct a sketch than to rebuild a report, and it will tell me exactly which measures to write with you next time.
Warm-up
Close the deck. This is the check on whether today stuck.
Discussion prompt
From memory: which shape does the readings table need; what makes the go-live a one-line change; why flag rather than delete; why a separate date table; when a calculated column beats a measure; and what control limits describe.
Hint: Shape, parameter, flag, calendar, slicer, baseline.
Answer:
Long — one row per reading, with the tag name as a value rather than a column heading.
Every source path lives in a parameter, and nothing downstream names a file directly.
Because the decision stays reversible and the count of bad readings is itself useful information about the instrument.
Because time intelligence needs a contiguous, marked calendar to walk, and a fact table's timestamps are not one.
When you want to slice, group or filter by the result — a slicer cannot be built on a measure.
What the process actually does, not what the permit requires. Those are different numbers.
Exit ticket
This decides what I prepare, so answer with what would help most rather than what sounds most impressive.
Predict first
Where would another hour go furthest?
Correct: Whichever you pick is what I will prepare for next time.
Why: The last option is worth flagging as a real possibility rather than a joke: your data mining and machine learning course starts shortly, and rebuilding this same pipeline in pandas would serve both the report and the coursework at once. That is a genuinely efficient use of the overlap.
Recap
You came in with an export and a question about dosing. This is what is now standing between the two.
| the piece | what to write | what it gives you |
|---|---|---|
| reshape | Table.UnpivotOtherColumns on Timestamp | a model that survives new tags |
| swap point | SourcePath as a text parameter | a one-line go-live |
| calendar | CALENDAR from min to max, then mark it | working time intelligence |
| base measure | CALCULATE with AVERAGE and the suspect filter | one number that follows every slicer |
| trend | DATESINPERIOD looking back seven days | signal instead of reading-to-reading noise |
| limits | mean plus and minus three sigma, under ALLSELECTED | flat limits, and a real excursion made visible |
Microsoft Learn, Understand star schema and the importance for Power BI — with the Microsoft Learn pages above for each individual step
Want this taught 1-on-1? Alexander tutors Business Analytics — $55/session, free consultation.