1
00:00:00,000 --> 00:00:02,160
You clicked create app from Excel,
2
00:00:02,160 --> 00:00:06,120
felt clever and congratulations migrated your chaos at scale.
3
00:00:06,120 --> 00:00:07,280
Excel isn't a database,
4
00:00:07,280 --> 00:00:09,720
it's a calculator with delusions of grandeur.
5
00:00:09,720 --> 00:00:12,320
Dataverse enforces identity types and relationships
6
00:00:12,320 --> 00:00:15,040
and it will punish spreadsheet habits with integrity errors.
7
00:00:15,040 --> 00:00:16,520
The truth everyone gets this wrong.
8
00:00:16,520 --> 00:00:18,000
In the next minutes, I'll tear down
9
00:00:18,000 --> 00:00:21,160
the five failure patterns that quietly destroy your power apps,
10
00:00:21,160 --> 00:00:22,880
starting with the one that breaks everything,
11
00:00:22,880 --> 00:00:24,440
no primary keys.
12
00:00:24,440 --> 00:00:26,000
Then I'll rebuild your model,
13
00:00:26,000 --> 00:00:27,360
keys relationships, types,
14
00:00:27,360 --> 00:00:28,560
so import store employed,
15
00:00:28,560 --> 00:00:31,040
upsurbs behave and look ups actually link.
16
00:00:31,040 --> 00:00:33,360
You'll leave with a minimal remediation path
17
00:00:33,360 --> 00:00:35,120
that scales without surprises.
18
00:00:35,120 --> 00:00:38,320
Failure pattern one, no primary keys.
19
00:00:38,320 --> 00:00:40,800
Okay, so basically rows without unique identities
20
00:00:40,800 --> 00:00:42,240
are strangers at a reunion.
21
00:00:42,240 --> 00:00:44,280
Everyone smiles, nobody knows who's who
22
00:00:44,280 --> 00:00:46,600
and bad mergers marry the wrong cousins.
23
00:00:46,600 --> 00:00:49,680
In Excel, you get away with pretending product name is unique.
24
00:00:49,680 --> 00:00:52,360
In Dataverse, pretending gets you duplicate records,
25
00:00:52,360 --> 00:00:55,160
overwritten fields and upsurbs that behave like a blender.
26
00:00:55,160 --> 00:00:56,040
Why this matters?
27
00:00:56,040 --> 00:00:57,000
Without primary keys,
28
00:00:57,000 --> 00:00:59,360
Dataverse can't guarantee row level integrity.
29
00:00:59,360 --> 00:01:00,920
It can't tell whether an incoming row
30
00:01:00,920 --> 00:01:02,320
should insert or update.
31
00:01:02,320 --> 00:01:05,360
Your merge becomes roulette, the cost, corrupted history,
32
00:01:05,360 --> 00:01:08,560
phantom duplicates and automations firing on the wrong records.
33
00:01:08,560 --> 00:01:10,400
The aha moment you'll get today.
34
00:01:10,400 --> 00:01:12,840
Identity is not a column, it's a contract.
35
00:01:12,840 --> 00:01:15,360
The simple version is use surrogate keys for truth,
36
00:01:15,360 --> 00:01:17,360
alternate keys for convenience.
37
00:01:17,360 --> 00:01:19,920
Surrogate key, a guide that never changes
38
00:01:19,920 --> 00:01:21,840
and means nothing to humans.
39
00:01:21,840 --> 00:01:24,280
Alternate key, a human friendly uniqueness rule
40
00:01:24,280 --> 00:01:27,000
like supplier name plus country or product code
41
00:01:27,000 --> 00:01:29,440
if marketing swears it's unique, spoiler alert,
42
00:01:29,440 --> 00:01:31,440
they'll try to reuse it next quarter.
43
00:01:31,440 --> 00:01:32,520
Here's what most people miss.
44
00:01:32,520 --> 00:01:34,800
Excel's implicit uniqueness is fake.
45
00:01:34,800 --> 00:01:37,720
Hidden spaces in consistent case and copy paste debris
46
00:01:37,720 --> 00:01:40,960
mean acme tools and acme tools look the same to you
47
00:01:40,960 --> 00:01:41,920
different to the engine.
48
00:01:41,920 --> 00:01:44,320
In other words, your identity column is lying.
49
00:01:44,320 --> 00:01:45,640
What to model in Dataverse?
50
00:01:45,640 --> 00:01:48,560
Primary column, a surrogate guide, don't argue.
51
00:01:48,560 --> 00:01:51,080
It's stable under renames, mergers, rebrands
52
00:01:51,080 --> 00:01:53,880
and we border competitor and kept both lines.
53
00:01:53,880 --> 00:01:56,640
Alternate keys define the actual business uniqueness
54
00:01:56,640 --> 00:01:58,640
constraints your imports will use.
55
00:01:58,640 --> 00:02:01,720
Product code, fine, product name plus category.
56
00:02:01,720 --> 00:02:03,440
Only if you accept the future failure
57
00:02:03,440 --> 00:02:06,440
when someone recycles drill in a new category variant.
58
00:02:06,440 --> 00:02:08,240
Natural versus surrogate debate.
59
00:02:08,240 --> 00:02:09,560
This isn't a philosophy class.
60
00:02:09,560 --> 00:02:11,960
Natural keys drift, surrogates don't.
61
00:02:11,960 --> 00:02:14,200
How to fix before your next import?
62
00:02:14,200 --> 00:02:16,000
Add a key column in Excel now.
63
00:02:16,000 --> 00:02:19,200
If you lack a reliable code, generate a guide per row.
64
00:02:19,200 --> 00:02:22,600
Yes, in Excel, there are power query functions, use them.
65
00:02:22,600 --> 00:02:25,080
In Dataverse, keep the system GUID as primary.
66
00:02:25,080 --> 00:02:27,480
Then define alternate keys on the human fields
67
00:02:27,480 --> 00:02:28,720
you'll map from Excel.
68
00:02:28,720 --> 00:02:31,440
Supply a name, location name, category name.
69
00:02:31,440 --> 00:02:34,680
So data flows can resolve lookups by something other than vibes.
70
00:02:34,680 --> 00:02:36,320
Pre-de-dupe in power query.
71
00:02:36,320 --> 00:02:38,840
Trim, case normalized and collapse white space
72
00:02:38,840 --> 00:02:40,120
on your would-be keys.
73
00:02:40,120 --> 00:02:42,600
Reject ambiguous duplicates instead of hoping imports
74
00:02:42,600 --> 00:02:43,680
pick the right one.
75
00:02:43,680 --> 00:02:45,120
Hope is not a data strategy.
76
00:02:45,120 --> 00:02:47,840
In DataFloes, choose a pen for first loads.
77
00:02:47,840 --> 00:02:50,760
Use absurd only when your alternate keys are active and clean.
78
00:02:50,760 --> 00:02:53,560
Upset without keys is how you override last week's truth
79
00:02:53,560 --> 00:02:54,560
with this week's typo.
80
00:02:54,560 --> 00:02:55,480
Here's the weird part.
81
00:02:55,480 --> 00:02:58,640
The primary column in Dataverse, the so-called name column,
82
00:02:58,640 --> 00:02:59,760
confuses people.
83
00:02:59,760 --> 00:03:01,560
It's a display field, not your identity.
84
00:03:01,560 --> 00:03:03,480
You can rename it to product name for sanity,
85
00:03:03,480 --> 00:03:04,680
but it's not the key.
86
00:03:04,680 --> 00:03:06,480
The actual identity is the hidden guide.
87
00:03:06,480 --> 00:03:08,400
Treat the display field like a label on a box.
88
00:03:08,400 --> 00:03:11,200
The barcode is the GUID, a quick micro story.
89
00:03:11,200 --> 00:03:13,320
A team used product name as their merge key
90
00:03:13,320 --> 00:03:15,720
because we're small, it's fine.
91
00:03:15,720 --> 00:03:18,680
Then they launched widget pro, replacing widget.
92
00:03:18,680 --> 00:03:20,080
They bulk change names.
93
00:03:20,080 --> 00:03:22,960
Every historical order now pointed at the new product
94
00:03:22,960 --> 00:03:23,880
in reports.
95
00:03:23,880 --> 00:03:25,480
And finance thought revenue tripled.
96
00:03:25,480 --> 00:03:26,080
It hadn't.
97
00:03:26,080 --> 00:03:28,560
They had just erased their past with a find and replace.
98
00:03:28,560 --> 00:03:30,040
Keys would have prevented that.
99
00:03:30,040 --> 00:03:32,680
Practical mapping example, products, suppliers, locations.
100
00:03:32,680 --> 00:03:34,440
In Excel, add supplier key and location key
101
00:03:34,440 --> 00:03:35,960
by mapping names to stable codes.
102
00:03:35,960 --> 00:03:38,840
In Dataverse, create supplier table with a surrogate guide,
103
00:03:38,840 --> 00:03:41,960
alternate key on supplier name, same for location on location
104
00:03:41,960 --> 00:03:42,600
name.
105
00:03:42,600 --> 00:03:45,000
In your product's data flow, map supplier name
106
00:03:45,000 --> 00:03:47,480
from the source to the supplier alternate key.
107
00:03:47,480 --> 00:03:49,200
Now when Agme Tools arrives, Dataverse
108
00:03:49,200 --> 00:03:51,240
resolves it to the correct supplier row.
109
00:03:51,240 --> 00:03:54,760
If someone types Agme Tools, the alternate key normalization,
110
00:03:54,760 --> 00:03:56,920
either matches or your validation rejects it.
111
00:03:56,920 --> 00:04:00,280
Both outcomes are better than silently creating Agme V2.
112
00:04:00,280 --> 00:04:01,760
Append versus absurd?
113
00:04:01,760 --> 00:04:02,440
Clarified.
114
00:04:02,440 --> 00:04:04,280
Append says always insert.
115
00:04:04,280 --> 00:04:05,440
Great for first loads.
116
00:04:05,440 --> 00:04:08,480
Absurd says insert or update based on keys.
117
00:04:08,480 --> 00:04:10,400
Only safe when your alternate keys are enforced
118
00:04:10,400 --> 00:04:12,040
and your source is de-duplicated.
119
00:04:12,040 --> 00:04:14,840
If you absurd on product name, and there are two drill rows
120
00:04:14,840 --> 00:04:17,040
with slight differences, Dataverse
121
00:04:17,040 --> 00:04:18,760
doesn't know which one is canonical.
122
00:04:18,760 --> 00:04:20,320
You taught it ambiguity.
123
00:04:20,320 --> 00:04:23,200
Everything clicked when I realized identity precedes data.
124
00:04:23,200 --> 00:04:27,120
Without a non-negotiable ID, your rows are anecdotes, not records.
125
00:04:27,120 --> 00:04:29,680
Establish surrogate guides for permanence, alternate keys
126
00:04:29,680 --> 00:04:33,360
for usability, and enforce them before a single row crosses the border.
127
00:04:33,360 --> 00:04:37,480
Then and only then do types and relationships standard chance.
128
00:04:37,480 --> 00:04:40,040
Failure pattern two, mixed data types.
129
00:04:40,040 --> 00:04:43,120
With identity sorted, your types still betray you.
130
00:04:43,120 --> 00:04:48,280
Excel lets a single column host a family reunion, numbers, text, dates,
131
00:04:48,280 --> 00:04:50,280
and whatever someone pasted from outlook,
132
00:04:50,280 --> 00:04:52,200
sitting together like it's normal.
133
00:04:52,200 --> 00:04:54,280
Dataverse is not that tolerant roommate.
134
00:04:54,280 --> 00:04:57,600
It enforces types and it will throw your stuff out the window
135
00:04:57,600 --> 00:04:59,320
the second you violate the lease.
136
00:04:59,320 --> 00:05:00,200
Why this matters?
137
00:05:00,200 --> 00:05:02,480
Type confusion corrupts logic quietly.
138
00:05:02,480 --> 00:05:05,080
Calculations round wrong, comparisons fail, look ups miss,
139
00:05:05,080 --> 00:05:08,160
and your yes, no turns into yes, no, maybe blank,
140
00:05:08,160 --> 00:05:09,280
which is not a boolean.
141
00:05:09,280 --> 00:05:10,640
It's a cry for help.
142
00:05:10,640 --> 00:05:11,680
The cost is subtle.
143
00:05:11,680 --> 00:05:13,680
Flaky formulas, inconsistent views,
144
00:05:13,680 --> 00:05:16,920
and automations that branch on string values that look true but aren't.
145
00:05:16,920 --> 00:05:18,760
The truth formatting is not typing.
146
00:05:18,760 --> 00:05:20,320
Excel formatting is make up.
147
00:05:20,320 --> 00:05:21,920
Dataverse types are bone structure.
148
00:05:21,920 --> 00:05:26,520
You can highlight 1334.50 with a currency format all day.
149
00:05:26,520 --> 00:05:30,040
If it's stored as text, dataverse reads string not money,
150
00:05:30,040 --> 00:05:32,680
and then your roll ups, aggregations, and formula columns
151
00:05:32,680 --> 00:05:34,560
politely refuse to cooperate.
152
00:05:34,560 --> 00:05:38,160
What to model in dataverse, the simple version, text for text,
153
00:05:38,160 --> 00:05:39,760
not text that looks like a number.
154
00:05:39,760 --> 00:05:41,920
Whole number for counts you never fraction.
155
00:05:41,920 --> 00:05:43,720
Stock quantity is an integer.
156
00:05:43,720 --> 00:05:45,080
Stop feeding it.
157
00:05:45,080 --> 00:05:46,040
12.
158
00:05:46,040 --> 00:05:48,440
Decimal for measured values where precision matters.
159
00:05:48,440 --> 00:05:49,600
Waits dimensions.
160
00:05:49,600 --> 00:05:51,200
Choose the scale, live with it.
161
00:05:51,200 --> 00:05:52,520
Currency for monetary amounts.
162
00:05:52,520 --> 00:05:55,600
It stores value plus currency and respects rounding rules.
163
00:05:55,600 --> 00:05:57,040
Use it or suffer.
164
00:05:57,040 --> 00:05:59,160
Date only for dates without time.
165
00:05:59,160 --> 00:06:01,600
Date time for events with time zones.
166
00:06:01,600 --> 00:06:04,160
If you don't care about time, don't store it.
167
00:06:04,160 --> 00:06:05,920
Choice when the set is finite and governed,
168
00:06:05,920 --> 00:06:08,560
you get consistency, labels, and easy filtering.
169
00:06:08,560 --> 00:06:10,560
Look up when the value comes from another table.
170
00:06:10,560 --> 00:06:13,200
If it has metadata, it's a table, not a choice.
171
00:06:13,200 --> 00:06:14,720
Boolean for binary facts.
172
00:06:14,720 --> 00:06:15,760
Yes, no.
173
00:06:15,760 --> 00:06:17,720
Not yes, ish.
174
00:06:17,720 --> 00:06:19,200
Here's what most people miss.
175
00:06:19,200 --> 00:06:21,600
Excel's blank is not one thing.
176
00:06:21,600 --> 00:06:23,960
You'll see empty string, null, and a space character
177
00:06:23,960 --> 00:06:26,120
that rode in on a copy-paste safari.
178
00:06:26,120 --> 00:06:27,640
To you, they're all empty.
179
00:06:27,640 --> 00:06:29,800
To the engine, they're three distinct states.
180
00:06:29,800 --> 00:06:31,480
That's how required columns slip through
181
00:06:31,480 --> 00:06:34,600
and how active becomes active and ruins your choice mapping.
182
00:06:34,600 --> 00:06:36,120
How to fix this before you import?
183
00:06:36,120 --> 00:06:37,800
Prevalidate in Power Query.
184
00:06:37,800 --> 00:06:39,480
Coerc types explicitly.
185
00:06:39,480 --> 00:06:40,880
Don't hope the wizard guesses.
186
00:06:40,880 --> 00:06:43,720
Use text, trim, text, upper, and clean invisible characters
187
00:06:43,720 --> 00:06:44,480
with text.
188
00:06:44,480 --> 00:06:47,040
Select or by replacing charts 60 and friends.
189
00:06:47,040 --> 00:06:49,000
Normalize case for keys and choices.
190
00:06:49,000 --> 00:06:52,880
Collapse double spaces to single, reject ambiguous rows.
191
00:06:52,880 --> 00:06:55,800
If a date column contains Q1, FI25, that's not a date,
192
00:06:55,800 --> 00:06:57,040
that's a human note.
193
00:06:57,040 --> 00:06:58,120
Split it or drop it.
194
00:06:58,120 --> 00:07:00,240
You are not obliged to import garbage.
195
00:07:00,240 --> 00:07:01,320
Normalize Booleans.
196
00:07:01,320 --> 00:07:03,360
Map. Yes, no YN1, true false.
197
00:07:03,360 --> 00:07:04,560
To true false ones.
198
00:07:04,560 --> 00:07:05,440
Then lock it.
199
00:07:05,440 --> 00:07:06,680
No maybe.
200
00:07:06,680 --> 00:07:08,760
For currency, split the amount in the currency code
201
00:07:08,760 --> 00:07:09,560
if it's mixed.
202
00:07:09,560 --> 00:07:13,160
Load to a currency column with a known transaction currency.
203
00:07:13,160 --> 00:07:15,880
Don't play mix and match with symbols embedded in strings.
204
00:07:15,880 --> 00:07:17,160
Handle dates, say, inly.
205
00:07:17,160 --> 00:07:20,320
If time zones matter, convert to UTC before import.
206
00:07:20,320 --> 00:07:22,080
If they don't, use date only.
207
00:07:22,080 --> 00:07:25,240
Storing midnight local as UTC is how do on the first
208
00:07:25,240 --> 00:07:27,960
becomes do on the 31st for your colleagues elsewhere
209
00:07:27,960 --> 00:07:30,640
for numbers trapped as text use value.
210
00:07:30,640 --> 00:07:32,760
From text with culture settings or Power Queries
211
00:07:32,760 --> 00:07:34,520
local aware conversion.
212
00:07:34,520 --> 00:07:37,480
Then set precision, whole number, decimal or currency.
213
00:07:37,480 --> 00:07:38,080
Decide.
214
00:07:38,080 --> 00:07:39,520
Amiguity is the bug.
215
00:07:39,520 --> 00:07:41,120
Example you'll thank me for.
216
00:07:41,120 --> 00:07:42,400
Inventory.
217
00:07:42,400 --> 00:07:44,120
Value as a formula column.
218
00:07:44,120 --> 00:07:47,360
Stock quantity times unit price.
219
00:07:47,360 --> 00:07:48,520
Unit price is currency.
220
00:07:48,520 --> 00:07:50,240
Formula columns operate in decimal.
221
00:07:50,240 --> 00:07:52,400
Cast unit price to decimal in the formula.
222
00:07:52,400 --> 00:07:54,480
Now rounding is controlled, not accidental.
223
00:07:54,480 --> 00:07:56,560
Compare that to leaving unit price as text
224
00:07:56,560 --> 00:07:58,160
because it displays fine.
225
00:07:58,160 --> 00:08:01,800
Your formula will refuse or worse, co-earth, unpredictably.
226
00:08:01,800 --> 00:08:04,280
Choice versus lookup with types in mind.
227
00:08:04,280 --> 00:08:06,560
Category with five stable values.
228
00:08:06,560 --> 00:08:07,240
Choice.
229
00:08:07,240 --> 00:08:09,920
Supply with address, rating and compliance status.
230
00:08:09,920 --> 00:08:10,920
Lookup to suppliers.
231
00:08:10,920 --> 00:08:13,840
If you start with text, Acme, you'll spend eternity cleaning,
232
00:08:13,840 --> 00:08:16,320
Acme, Acme Inc. and Acme Inc.
233
00:08:16,320 --> 00:08:19,400
Converted to a lookup now and let data verse enforce
234
00:08:19,400 --> 00:08:22,160
one row per supplier, period, micro story.
235
00:08:22,160 --> 00:08:24,040
A team stored order date as text
236
00:08:24,040 --> 00:08:26,120
because the export looked like a date.
237
00:08:26,120 --> 00:08:27,640
Then they sorted descending and wondered
238
00:08:27,640 --> 00:08:29,840
why 930 came before 1001.
239
00:08:29,840 --> 00:08:31,040
Lexigraphic sort.
240
00:08:31,040 --> 00:08:32,200
They built KPIs on it.
241
00:08:32,200 --> 00:08:33,640
The quarter ended early in reports
242
00:08:33,640 --> 00:08:35,920
and sales missed their bonus on paper.
243
00:08:35,920 --> 00:08:37,600
One type change and their timeline
244
00:08:37,600 --> 00:08:39,600
snapped back to reality.
245
00:08:39,600 --> 00:08:42,480
Everything clicked when I realized types are contracts for meaning.
246
00:08:42,480 --> 00:08:45,040
Data verse can optimize, secure and calculate only
247
00:08:45,040 --> 00:08:47,720
when you declare what the data is, not how it looks.
248
00:08:47,720 --> 00:08:50,960
Declare text, number, decimal, currency, date only, choice,
249
00:08:50,960 --> 00:08:53,120
lookup, boolean, within 10th, co-earth, upstream,
250
00:08:53,120 --> 00:08:54,480
and reject the misfits.
251
00:08:54,480 --> 00:08:57,120
Then your formulas behave, your lookups resolve,
252
00:08:57,120 --> 00:08:59,360
and your app stops gaslighting you.
253
00:08:59,360 --> 00:09:01,720
Failure, pattern three, lookup as joints.
254
00:09:01,720 --> 00:09:03,120
Now we get to the training wheels.
255
00:09:03,120 --> 00:09:05,320
We look up, X lookup, index match,
256
00:09:05,320 --> 00:09:08,040
cute and a spreadsheet, dangerous in a data platform.
257
00:09:08,040 --> 00:09:12,040
In Excel, you emulate relationships by copying values across tables.
258
00:09:12,040 --> 00:09:14,000
In data verse, you define relationships
259
00:09:14,000 --> 00:09:16,640
so values never need to be copied in the first place.
260
00:09:16,640 --> 00:09:17,480
The truth?
261
00:09:17,480 --> 00:09:20,040
Your lookups are text duplication with extra steps.
262
00:09:20,040 --> 00:09:21,040
Why this matters?
263
00:09:21,040 --> 00:09:23,560
Spreadsheets hide referential drift.
264
00:09:23,560 --> 00:09:25,760
Your supplier name repeats in every product row,
265
00:09:25,760 --> 00:09:28,440
so when Agme tools becomes Agme tools-lutted,
266
00:09:28,440 --> 00:09:30,160
you miss one sheet, one tab, one row,
267
00:09:30,160 --> 00:09:33,400
and suddenly your filters split a single supplier into two ghosts.
268
00:09:33,400 --> 00:09:35,560
Data verse requires joints that carry identity,
269
00:09:35,560 --> 00:09:36,760
not fragile labels,
270
00:09:36,760 --> 00:09:39,680
so one rename doesn't shatter 100 dependent rows.
271
00:09:39,680 --> 00:09:40,960
Here's what most people miss.
272
00:09:40,960 --> 00:09:42,960
A lookup column in data verse stores a reference
273
00:09:42,960 --> 00:09:46,240
to the parent rows ID plus the display name for convenience.
274
00:09:46,240 --> 00:09:47,760
That ID is the relationship.
275
00:09:47,760 --> 00:09:49,080
Change the parent's display name
276
00:09:49,080 --> 00:09:51,240
and every child still points to the same record,
277
00:09:51,240 --> 00:09:53,400
no mass update, no prayer circle.
278
00:09:53,400 --> 00:09:55,920
Lookup stores text, lookup stores truth.
279
00:09:55,920 --> 00:09:59,080
Okay, so basically if a column repeats supplier name
280
00:09:59,080 --> 00:10:01,720
across thousands of product rows, that's not a column.
281
00:10:01,720 --> 00:10:04,200
That's a relationship screaming to become a lookup.
282
00:10:04,200 --> 00:10:06,160
Choice versus lookup is simple.
283
00:10:06,160 --> 00:10:09,200
If the list is finite and has no metadata, use a choice.
284
00:10:09,200 --> 00:10:12,160
If it has properties, history, rules, or compliance flags,
285
00:10:12,160 --> 00:10:14,080
it's a table, therefore a lookup.
286
00:10:14,080 --> 00:10:16,640
Suppliers, locations, categories with descriptions
287
00:10:16,640 --> 00:10:20,080
and hierarchies, lookups, status with five governed values,
288
00:10:20,080 --> 00:10:22,520
choice, how to model it right.
289
00:10:22,520 --> 00:10:26,040
Create dimension tables, supplier, location, category.
290
00:10:26,040 --> 00:10:28,080
Each gets a surrogate guide primary key
291
00:10:28,080 --> 00:10:29,920
and an alternate key on the human label
292
00:10:29,920 --> 00:10:32,720
you'll use during import, supplier name, location name,
293
00:10:32,720 --> 00:10:33,600
category code.
294
00:10:33,600 --> 00:10:36,080
In product, replace text columns with lookup columns
295
00:10:36,080 --> 00:10:36,920
to those tables.
296
00:10:36,920 --> 00:10:40,440
Do not store AgMe tools as text, store a reference to supplier.
297
00:10:40,440 --> 00:10:44,360
Define the relationship, cardinality, products, many to one suppliers,
298
00:10:44,360 --> 00:10:47,760
products, many to one locations, products, many to one categories.
299
00:10:47,760 --> 00:10:49,360
If you genuinely need many to many,
300
00:10:49,360 --> 00:10:51,720
engage products sold by multiple suppliers,
301
00:10:51,720 --> 00:10:54,960
model and intersect table, product supplier with two lookups.
302
00:10:54,960 --> 00:10:57,720
Don't fake it with comma separated text ever.
303
00:10:57,720 --> 00:10:59,800
How to fix the mess you already have.
304
00:10:59,800 --> 00:11:02,840
Preload the parents, import supplier, location,
305
00:11:02,840 --> 00:11:05,800
category first with alternate keys active,
306
00:11:05,800 --> 00:11:09,080
verify no duplicates, trim, uppercase,
307
00:11:09,080 --> 00:11:11,560
and normalize punctuation in power query.
308
00:11:11,560 --> 00:11:14,480
Reject collisions instead of inventing AgMe too.
309
00:11:14,480 --> 00:11:17,600
In your product data flow, map text to lookups by keys.
310
00:11:17,600 --> 00:11:20,240
In the column mapping choose supplier, lookup,
311
00:11:20,240 --> 00:11:22,760
map using supplier name, alternate key.
312
00:11:22,760 --> 00:11:24,280
Same for location and category.
313
00:11:24,280 --> 00:11:27,040
Now a string becomes a reference, not a duplicated label.
314
00:11:27,040 --> 00:11:29,040
After import, kill the obsolete text columns,
315
00:11:29,040 --> 00:11:30,760
leave no runway for future drift.
316
00:11:30,760 --> 00:11:32,400
The counter-intuitive part is this.
317
00:11:32,400 --> 00:11:35,400
Lookup felt safer because you could see the text.
318
00:11:35,400 --> 00:11:37,400
Lookups look abstract, but lookups
319
00:11:37,400 --> 00:11:39,640
are how you get integrity features for free.
320
00:11:39,640 --> 00:11:42,240
Cascade behavior, uniqueness checks, rollups,
321
00:11:42,240 --> 00:11:44,160
and security that can filter by parent.
322
00:11:44,160 --> 00:11:47,200
You can't roll up on text, you can roll up on relationships.
323
00:11:47,200 --> 00:11:50,600
Practically example, you build a report, inventory value by supplier.
324
00:11:50,600 --> 00:11:53,800
In Excel that means a pivot relying on consistent text.
325
00:11:53,800 --> 00:11:56,400
One string AgMe tools breaks aggregation.
326
00:11:56,400 --> 00:11:59,320
Then data verse the rollup counts by supplier ID.
327
00:11:59,320 --> 00:12:01,560
Tense spelling still resolve to one supplier
328
00:12:01,560 --> 00:12:04,000
because the relationship does the heavy lifting.
329
00:12:04,000 --> 00:12:06,200
You change suppliers display name once,
330
00:12:06,200 --> 00:12:09,400
the app, the views and power BI all show the new label
331
00:12:09,400 --> 00:12:10,680
without touching product rows.
332
00:12:10,680 --> 00:12:14,040
MicroStory, a team chained five V-lookups across tabs
333
00:12:14,040 --> 00:12:16,080
to pull categories, subcategories, supplier rating,
334
00:12:16,080 --> 00:12:18,560
and region into a flat product master.
335
00:12:18,560 --> 00:12:20,320
It worked until someone resorted a tab
336
00:12:20,320 --> 00:12:22,520
and pasted new rows with leading spaces.
337
00:12:22,520 --> 00:12:25,120
Overnight, 8% of products lost their supplier.
338
00:12:25,120 --> 00:12:27,600
Order still processed analytics quietly lied.
339
00:12:27,600 --> 00:12:30,400
They switched to lookups, enforced required relationships,
340
00:12:30,400 --> 00:12:33,880
and the missing supplier error surfaced at import where it belongs.
341
00:12:33,880 --> 00:12:36,320
Everything clicked when I realized lookup copies values,
342
00:12:36,320 --> 00:12:38,160
relationships bind identities.
343
00:12:38,160 --> 00:12:40,080
If it has metadata, give it a table.
344
00:12:40,080 --> 00:12:41,560
If it repeats, give it a lookup.
345
00:12:41,560 --> 00:12:44,680
Define alternate keys, so imports resolve text once,
346
00:12:44,680 --> 00:12:47,280
then let data verse carry the join forever.
347
00:12:47,280 --> 00:12:49,840
Failure pattern four, multipurpose columns.
348
00:12:49,840 --> 00:12:52,360
Here's where your spreadsheet instincts go full performance art.
349
00:12:52,360 --> 00:12:55,680
One column doing three jobs like a clown juggling chain source.
350
00:12:55,680 --> 00:12:57,720
Status that holds active, inactive,
351
00:12:57,720 --> 00:13:02,000
on hold discontinued and chef's kiss blank location.
352
00:13:02,000 --> 00:13:04,720
That sometimes means a warehouse, sometimes an address,
353
00:13:04,720 --> 00:13:07,360
sometimes left behind the receptionist desk.
354
00:13:07,360 --> 00:13:09,480
Excel tolerates this because it doesn't care.
355
00:13:09,480 --> 00:13:11,480
Data verse cares it enforces semantics
356
00:13:11,480 --> 00:13:14,000
and overloaded fields are how you sabotage validation,
357
00:13:14,000 --> 00:13:17,000
reporting and automation in one elegant move.
358
00:13:17,000 --> 00:13:17,800
Why this matters?
359
00:13:17,800 --> 00:13:19,560
Automation can't branch on mush.
360
00:13:19,560 --> 00:13:22,320
You can't build a rule that says if status is active,
361
00:13:22,320 --> 00:13:25,400
then reorder when status also contains on hold for audit
362
00:13:25,400 --> 00:13:28,440
because someone decided notes belong in the same cell.
363
00:13:28,440 --> 00:13:30,560
Reporting can't group on a field that mixes states
364
00:13:30,560 --> 00:13:32,640
with commentary and data quality rules
365
00:13:32,640 --> 00:13:35,200
collapse when a single column is asked to represent state,
366
00:13:35,200 --> 00:13:36,960
reference, and narrative all at once.
367
00:13:36,960 --> 00:13:37,800
The truth?
368
00:13:37,800 --> 00:13:40,280
It worked in my pivot is not proof of model quality.
369
00:13:40,280 --> 00:13:41,520
Pivots are magic tricks.
370
00:13:41,520 --> 00:13:43,880
They can summarize nonsense without complaining.
371
00:13:43,880 --> 00:13:45,080
Apps are not magicians.
372
00:13:45,080 --> 00:13:46,560
They need structure.
373
00:13:46,560 --> 00:13:48,240
What to model instead?
374
00:13:48,240 --> 00:13:50,360
Status is a choice, governed values,
375
00:13:50,360 --> 00:13:52,800
active inactive, on hold discontinued.
376
00:13:52,800 --> 00:13:55,200
Labels are human, stored values are clean.
377
00:13:55,200 --> 00:13:57,720
You get validation filtering and no accidental act.
378
00:13:57,720 --> 00:13:59,880
Location is a look up to a location table.
379
00:13:59,880 --> 00:14:02,920
If it has metadata, country address, capacity, time zone,
380
00:14:02,920 --> 00:14:04,040
it's a table.
381
00:14:04,040 --> 00:14:05,400
You want a single rope or warehouse,
382
00:14:05,400 --> 00:14:08,360
not 47 spellings of warehouse A. Notes are notes.
383
00:14:08,360 --> 00:14:12,000
Use a dedicated multi-line text or the built-in notes timeline.
384
00:14:12,000 --> 00:14:13,560
Commentary is separate from state.
385
00:14:13,560 --> 00:14:16,480
That way your audit trail doesn't contaminate your logic.
386
00:14:16,480 --> 00:14:17,800
Here's what most people miss.
387
00:14:17,800 --> 00:14:19,840
A catch all column looks efficient
388
00:14:19,840 --> 00:14:22,000
but creates silent divergence.
389
00:14:22,000 --> 00:14:24,080
The moment someone types, active, awaiting QA,
390
00:14:24,080 --> 00:14:26,080
you've added a brand new undocumented status
391
00:14:26,080 --> 00:14:28,520
that your flows and dashboards will misinterpret.
392
00:14:28,520 --> 00:14:30,240
Choice fields stop that at the door.
393
00:14:30,240 --> 00:14:31,960
You either add a new option deliberately
394
00:14:31,960 --> 00:14:33,600
or the import fails loudly.
395
00:14:33,600 --> 00:14:35,800
Failure at import beats failure in production.
396
00:14:35,800 --> 00:14:37,560
How to fix the mess you already have?
397
00:14:37,560 --> 00:14:39,800
Split columns with a mapping table.
398
00:14:39,800 --> 00:14:42,360
In Power Query, create a status map
399
00:14:42,360 --> 00:14:46,560
that converts freeform status text to a governed set.
400
00:14:46,560 --> 00:14:50,560
Active, ACT, in service, active.
401
00:14:50,560 --> 00:14:54,040
Disabled, inactive, retired, inactive, on hold.
402
00:14:54,200 --> 00:14:56,080
Pending QA, on hold.
403
00:14:56,080 --> 00:14:58,520
Store the original status in a new column called
404
00:14:58,520 --> 00:15:00,920
Status Roar for Forensic Nostalgia.
405
00:15:00,920 --> 00:15:03,120
Load the mapped value into a choice column.
406
00:15:03,120 --> 00:15:04,760
Extract references from text.
407
00:15:04,760 --> 00:15:06,880
For location strip commentary into a notes column
408
00:15:06,880 --> 00:15:08,800
and keep only the location name or code
409
00:15:08,800 --> 00:15:10,760
in a clean location name column.
410
00:15:10,760 --> 00:15:13,400
Preload the location table with distinct names,
411
00:15:13,400 --> 00:15:15,240
define an alternate key on name,
412
00:15:15,240 --> 00:15:17,360
then convert the location column in products
413
00:15:17,360 --> 00:15:19,440
to a lookup that resolves by that key.
414
00:15:19,440 --> 00:15:21,000
In force rules going forward,
415
00:15:21,000 --> 00:15:24,520
mark status as required and restrict edits to the choice.
416
00:15:24,520 --> 00:15:26,560
Make location required with a lookup?
417
00:15:26,560 --> 00:15:28,600
No free text locations.
418
00:15:28,600 --> 00:15:29,800
Add a business rule.
419
00:15:29,800 --> 00:15:31,280
If status equals discontinued,
420
00:15:31,280 --> 00:15:33,520
then this allows stock quantity updates.
421
00:15:33,520 --> 00:15:34,840
Your spreadsheet can't do that.
422
00:15:34,840 --> 00:15:37,200
Dataverse can, move math out of cells,
423
00:15:37,200 --> 00:15:39,600
replace inventory value calculated in Excel
424
00:15:39,600 --> 00:15:42,200
with a formula or calculated column in Dataverse
425
00:15:42,200 --> 00:15:44,880
where types are respected and casting is explicit.
426
00:15:44,880 --> 00:15:46,360
Spreadsheet math in a text field
427
00:15:46,360 --> 00:15:48,200
is how you end up summing strings.
428
00:15:48,200 --> 00:15:50,800
Microstory, a team used one state column
429
00:15:50,800 --> 00:15:53,200
for a lifecycle plus supply chain notes.
430
00:15:53,200 --> 00:15:57,120
Active, active, formal, active vendor-late, discount.
431
00:15:57,120 --> 00:15:59,800
Their active inventory value report was off by 18%
432
00:15:59,800 --> 00:16:02,920
because half the active rows didn't equal active.
433
00:16:02,920 --> 00:16:06,120
They migrated, split state into a choice status
434
00:16:06,120 --> 00:16:08,560
and a node column, backfilled with a mapping table
435
00:16:08,560 --> 00:16:10,920
and suddenly procurement stopped overordering,
436
00:16:10,920 --> 00:16:13,840
not because demand changed because the data stopped lying.
437
00:16:13,840 --> 00:16:15,400
Practical migration steps.
438
00:16:15,400 --> 00:16:17,960
In Power Query.
439
00:16:17,960 --> 00:16:20,280
Duplicate the overloaded column.
440
00:16:20,280 --> 00:16:21,840
One becomes the governed value.
441
00:16:21,840 --> 00:16:23,600
The other becomes raw notes.
442
00:16:23,600 --> 00:16:26,600
Use custom logic to pass out markers like parentheses, dashes
443
00:16:26,600 --> 00:16:28,400
and commas to isolate the state token.
444
00:16:28,400 --> 00:16:32,560
In Dataverse, create status, choice, location, lookup,
445
00:16:32,560 --> 00:16:34,880
and notes, multi-line text.
446
00:16:34,880 --> 00:16:38,520
Add an alternate key on location, name, load locations first.
447
00:16:38,520 --> 00:16:40,280
Enable required fields and prevent
448
00:16:40,280 --> 00:16:42,360
blank values via business rules.
449
00:16:42,360 --> 00:16:46,080
Post import, cleanup, delete or hide the old free text columns.
450
00:16:46,080 --> 00:16:47,280
Do not leave escape hatches.
451
00:16:47,280 --> 00:16:49,080
Future you deserve less chaos.
452
00:16:49,080 --> 00:16:52,240
Everything clicked when I realized columns should carry one meaning.
453
00:16:52,240 --> 00:16:55,480
Choices capture state, lookup's capture identity,
454
00:16:55,480 --> 00:16:56,960
node's capture narrative.
455
00:16:56,960 --> 00:16:58,760
Keep them separate and everything else.
456
00:16:58,760 --> 00:17:01,520
Validation, views, flows, stops tripping
457
00:17:01,520 --> 00:17:03,720
over your clever consolidation.
458
00:17:03,720 --> 00:17:05,000
Failure pattern five.
459
00:17:05,000 --> 00:17:06,120
Often rows.
460
00:17:06,120 --> 00:17:08,600
Now for the cardinal sin, children without parents.
461
00:17:08,600 --> 00:17:10,680
In spreadsheets, you delete a supplier row
462
00:17:10,680 --> 00:17:13,240
and forget that one 200 products still reference
463
00:17:13,240 --> 00:17:15,680
ag me tools by name, nothing complains.
464
00:17:15,680 --> 00:17:17,360
Your pivot just silently excludes them
465
00:17:17,360 --> 00:17:20,480
or worse shows a new category called blank.
466
00:17:20,480 --> 00:17:22,000
In Dataverse, that's an often,
467
00:17:22,000 --> 00:17:24,520
products referencing a non-existent supplier.
468
00:17:24,520 --> 00:17:28,920
An unlike Excel Dataverse has opinions about often, strong ones.
469
00:17:28,920 --> 00:17:29,920
Why this matters?
470
00:17:29,920 --> 00:17:33,080
Without enforced relationships, your data becomes fiction.
471
00:17:33,080 --> 00:17:35,360
Reports show totals that don't tie out,
472
00:17:35,360 --> 00:17:37,480
automations fire on products with no supplier
473
00:17:37,480 --> 00:17:39,000
and then fail downstream.
474
00:17:39,000 --> 00:17:40,960
Security rules that rely on hierarchies
475
00:17:40,960 --> 00:17:43,440
can't filter correctly because the parent is missing.
476
00:17:43,440 --> 00:17:46,600
Offends are integrity errors wearing invisibility cloaks.
477
00:17:46,600 --> 00:17:49,080
They pass casual inspection and break under stress.
478
00:17:49,080 --> 00:17:49,920
The truth?
479
00:17:49,920 --> 00:17:52,640
Referential integrity isn't optional housekeeping.
480
00:17:52,640 --> 00:17:54,080
It's the foundation.
481
00:17:54,080 --> 00:17:56,400
If a child requires a parent to make sense,
482
00:17:56,400 --> 00:17:59,080
make the relationship required and define cascade behavior.
483
00:17:59,080 --> 00:18:00,240
You don't get credit for,
484
00:18:00,240 --> 00:18:02,800
we usually remember to create the supplier first.
485
00:18:02,800 --> 00:18:05,320
Systems enforce what people forget, what to model,
486
00:18:05,320 --> 00:18:07,800
required lookups for essential relationships.
487
00:18:07,800 --> 00:18:11,040
Product must have a supplier, product must have a category.
488
00:18:11,040 --> 00:18:13,480
If the business truly allows unknown supplier,
489
00:18:13,480 --> 00:18:15,720
model a specific supplier row named unknown
490
00:18:15,720 --> 00:18:18,720
with a proper ID, not a null, ambiguity gets one,
491
00:18:18,720 --> 00:18:21,200
explicit placeholder, governed and auditable.
492
00:18:21,200 --> 00:18:23,520
Cascade behaviors for delete, pick, restrict,
493
00:18:23,520 --> 00:18:25,480
if deleting a parent would be catastrophic.
494
00:18:25,480 --> 00:18:27,880
Pick cascade if deletion should remove all children,
495
00:18:27,880 --> 00:18:30,240
rare intransactional domains for repairing
496
00:18:30,240 --> 00:18:32,080
or name changes cascade.
497
00:18:32,080 --> 00:18:33,680
All ensures the display name updates
498
00:18:33,680 --> 00:18:35,840
everywhere without creating text drift.
499
00:18:35,840 --> 00:18:37,600
Intersect tables for many to many.
500
00:18:37,600 --> 00:18:39,640
Don't fake multiple suppliers per product
501
00:18:39,640 --> 00:18:41,520
with comma separated text.
502
00:18:41,520 --> 00:18:44,120
Create product supplier with two lookups, each required.
503
00:18:44,120 --> 00:18:45,560
That table becomes the relationship
504
00:18:45,560 --> 00:18:47,360
with its own audit and life cycle.
505
00:18:47,360 --> 00:18:49,000
How to fix before you import?
506
00:18:49,000 --> 00:18:49,960
Preload parents.
507
00:18:49,960 --> 00:18:52,360
Import suppliers, locations, categories first.
508
00:18:52,360 --> 00:18:55,360
Activate alternate keys on the labels you'll resolve against.
509
00:18:55,360 --> 00:18:57,320
Validate distinctness aggressively trim,
510
00:18:57,320 --> 00:18:59,400
normalize case and strip punctuation.
511
00:18:59,400 --> 00:19:02,600
Reject duplicates instead of spawning supplier two.
512
00:19:02,600 --> 00:19:04,160
Sequence your data flows.
513
00:19:04,160 --> 00:19:06,560
Children after parents in the product data flow
514
00:19:06,560 --> 00:19:08,400
map supplier by alternate key.
515
00:19:08,400 --> 00:19:10,840
If a supplier doesn't resolve, fail the row and log it.
516
00:19:10,840 --> 00:19:12,840
Do not auto create parents from child text
517
00:19:12,840 --> 00:19:14,920
unless you enjoy polluted masters.
518
00:19:14,920 --> 00:19:16,960
Enforced at the schema, mark the lookup
519
00:19:16,960 --> 00:19:19,760
as required in data verse so someone can't sneak a null
520
00:19:19,760 --> 00:19:22,120
through the form, the API or a rogue import.
521
00:19:22,120 --> 00:19:24,000
Required means required everywhere.
522
00:19:24,000 --> 00:19:25,480
How to fix after the fact?
523
00:19:25,480 --> 00:19:26,920
Detect office with views.
524
00:19:26,920 --> 00:19:29,160
Create a view in products where supplier is blank.
525
00:19:29,160 --> 00:19:31,320
If you've made it required, you shouldn't see any.
526
00:19:31,320 --> 00:19:33,280
If you do, they came from legacy imports
527
00:19:33,280 --> 00:19:34,840
or a misconfigured integration,
528
00:19:34,840 --> 00:19:36,840
fix the pipeline, then fix the data.
529
00:19:36,840 --> 00:19:38,480
Backfill often intentionally.
530
00:19:38,480 --> 00:19:40,600
Create or identify the correct parent rows,
531
00:19:40,600 --> 00:19:42,600
then update children to point to them.
532
00:19:42,600 --> 00:19:44,520
If unknown, link to the unknown parent
533
00:19:44,520 --> 00:19:46,800
and add a note explaining the provenance,
534
00:19:46,800 --> 00:19:49,040
then assign an owner to resolve within an SLA.
535
00:19:49,040 --> 00:19:50,040
Lockdown deletion.
536
00:19:50,040 --> 00:19:52,000
Set delete behavior on parents to restrict,
537
00:19:52,000 --> 00:19:53,680
add a business rule or custom plug-in
538
00:19:53,680 --> 00:19:56,080
to block deletion if active children exist
539
00:19:56,080 --> 00:19:58,960
and provide a friendly error with a link to related records.
540
00:19:58,960 --> 00:20:01,240
Friendly meaning precise and smugly unambiguous.
541
00:20:01,240 --> 00:20:02,240
Micro story.
542
00:20:02,240 --> 00:20:04,000
Finance complained that supplier spend
543
00:20:04,000 --> 00:20:05,840
didn't match inventory value.
544
00:20:05,840 --> 00:20:08,760
Investigation found 9% of products had null suppliers
545
00:20:08,760 --> 00:20:10,040
from an old import.
546
00:20:10,040 --> 00:20:11,840
Reports grouped them under unassigned,
547
00:20:11,840 --> 00:20:14,240
which executives dutifully ignored.
548
00:20:14,240 --> 00:20:16,360
Required lookups, preload of suppliers
549
00:20:16,360 --> 00:20:19,800
and restrict on delete eliminated orphans in one release cycle.
550
00:20:19,800 --> 00:20:21,520
The next quarter spent matched inventory,
551
00:20:21,520 --> 00:20:23,000
not because the business improved
552
00:20:23,000 --> 00:20:25,840
because relationships existed, concurrency twist.
553
00:20:25,840 --> 00:20:28,120
In Excel, two people fixed the same parent
554
00:20:28,120 --> 00:20:30,720
concurrently and override each other with no trace.
555
00:20:30,720 --> 00:20:32,640
In dataverse, optimistic concurrency blocks
556
00:20:32,640 --> 00:20:34,880
stale updates and preserves integrity,
557
00:20:34,880 --> 00:20:36,480
unless you've left children dangling,
558
00:20:36,480 --> 00:20:38,400
then you get a lot of retries and nonsense,
559
00:20:38,400 --> 00:20:41,040
model-required relationships and the platform's transaction scope
560
00:20:41,040 --> 00:20:42,560
gives you atomic sanity.
561
00:20:42,560 --> 00:20:45,120
Either the parent and children updates succeed together
562
00:20:45,120 --> 00:20:46,280
or nothing changes.
563
00:20:46,280 --> 00:20:47,520
Practical checklist.
564
00:20:47,520 --> 00:20:49,360
Parents first, always.
565
00:20:49,360 --> 00:20:52,160
Data flows ordered, suppliers, locations, categories,
566
00:20:52,160 --> 00:20:54,720
then products, alternate keys on parent labels
567
00:20:54,720 --> 00:20:56,560
to resolve lookups during import,
568
00:20:56,560 --> 00:21:00,560
required lookups on critical child tables, no nulls.
569
00:21:00,560 --> 00:21:04,000
Use explicit unknown rows only if the business demands it
570
00:21:04,000 --> 00:21:06,920
and report on them relentlessly until they go to zero.
571
00:21:06,920 --> 00:21:08,680
Delete behavior set to restrict on parents
572
00:21:08,680 --> 00:21:11,640
with active children, no casual amputation.
573
00:21:11,640 --> 00:21:14,920
Monitoring, scheduled view or power BI alert for often candidates
574
00:21:14,920 --> 00:21:17,360
plus audit enabled on parent and intersect tables
575
00:21:17,360 --> 00:21:19,600
to trace who tried to create orphans and when.
576
00:21:19,600 --> 00:21:21,480
Everything clicked when I realized
577
00:21:21,480 --> 00:21:25,080
referential integrity is not nice to have, its gravity.
578
00:21:25,080 --> 00:21:27,480
Ignore it and data floats off into space.
579
00:21:27,480 --> 00:21:29,760
Enforced required lookups, preload parents,
580
00:21:29,760 --> 00:21:31,640
configure cascades like an adult
581
00:21:31,640 --> 00:21:33,080
and orphans stop existing,
582
00:21:33,080 --> 00:21:34,600
not by luck by design.
583
00:21:34,600 --> 00:21:37,200
We're proving it, Excel versus Dataverse.
584
00:21:37,200 --> 00:21:39,400
Stress benchmarks, let's stop theorizing
585
00:21:39,400 --> 00:21:40,520
and step on the gas.
586
00:21:40,520 --> 00:21:41,440
Row limits first.
587
00:21:41,440 --> 00:21:44,000
Excel gets sluggish past a few hundred thousand rows,
588
00:21:44,000 --> 00:21:46,480
formulas recount like they're waiting through syrup.
589
00:21:46,480 --> 00:21:49,080
Dataverse indexes columns, query server side
590
00:21:49,080 --> 00:21:50,120
and streams results.
591
00:21:50,120 --> 00:21:51,800
The truth, 10 filters on a million rows
592
00:21:51,800 --> 00:21:54,520
is still a blink when the engine owns the index.
593
00:21:54,520 --> 00:21:55,600
Concurrency.
594
00:21:55,600 --> 00:21:58,400
Shared file on a network drive, one added wins,
595
00:21:58,400 --> 00:22:01,640
the other user silently loses and no one knows which.
596
00:22:01,640 --> 00:22:05,280
Dataverse uses optimistic concurrency and transactions.
597
00:22:05,280 --> 00:22:07,200
Two users save conflicting edits.
598
00:22:07,200 --> 00:22:09,280
The stale one is blocked with a version error.
599
00:22:09,280 --> 00:22:11,080
Annoying? Yes.
600
00:22:11,080 --> 00:22:13,680
Also how you avoid ghost edits, audit.
601
00:22:13,680 --> 00:22:17,240
Excel has last modified by whoever saved last.
602
00:22:17,240 --> 00:22:19,840
Dataverse has auditing and change tracking.
603
00:22:19,840 --> 00:22:22,320
Who, what, when, before, after?
604
00:22:22,320 --> 00:22:24,320
You can replay history or drive delta loads
605
00:22:24,320 --> 00:22:25,920
compared that to manual versioning
606
00:22:25,920 --> 00:22:30,640
with final v7 real Excel asks painful security.
607
00:22:30,640 --> 00:22:32,640
Everyone on the share is not a model.
608
00:22:32,640 --> 00:22:33,800
It's a shrug.
609
00:22:33,800 --> 00:22:36,760
Dataverse offers role-based and field-level security.
610
00:22:36,760 --> 00:22:38,880
A junior user can see price but not margin.
611
00:22:38,880 --> 00:22:40,560
Your spreadsheet cannot keep a secret.
612
00:22:40,560 --> 00:22:41,560
Don't pretend it can.
613
00:22:41,560 --> 00:22:42,560
API throughput.
614
00:22:42,560 --> 00:22:44,680
Copy paste is not an integration strategy.
615
00:22:44,680 --> 00:22:46,080
Dataverse has data flows,
616
00:22:46,080 --> 00:22:48,320
APIs, power automate and bulk operations.
617
00:22:48,320 --> 00:22:50,080
You move thousands of rows predictably,
618
00:22:50,080 --> 00:22:51,880
identitently and on schedule.
619
00:22:51,880 --> 00:22:53,560
Reliability beats heroics.
620
00:22:53,560 --> 00:22:55,080
Referential integrity.
621
00:22:55,080 --> 00:22:57,040
Lookup illusions fracture under rename,
622
00:22:57,040 --> 00:22:59,520
resort or oops, I deleted the tab.
623
00:22:59,520 --> 00:23:02,600
Dataverse enforces relationships and cascades.
624
00:23:02,600 --> 00:23:04,800
Parents first children follow rules applied.
625
00:23:04,800 --> 00:23:06,960
It's civilization not chaos.
626
00:23:06,960 --> 00:23:08,600
Minimal remediation path.
627
00:23:08,600 --> 00:23:09,960
The fix that sticks.
628
00:23:09,960 --> 00:23:12,240
Here's the fix you actually follow tomorrow.
629
00:23:12,240 --> 00:23:14,360
Step one, model core entities,
630
00:23:14,360 --> 00:23:16,760
products, suppliers, locations, categories,
631
00:23:16,760 --> 00:23:18,960
one table per concept, no casuals.
632
00:23:18,960 --> 00:23:20,440
Step two, keys.
633
00:23:20,440 --> 00:23:22,320
Keep data versus GUID primary.
634
00:23:22,320 --> 00:23:23,840
Add alternate keys on supplier.
635
00:23:23,840 --> 00:23:28,120
Name, location, name, category.code, product, bond, code,
636
00:23:28,120 --> 00:23:29,880
step three, normalize.
637
00:23:29,880 --> 00:23:33,760
Choice for status, lookup for supplier, location, category.
638
00:23:33,760 --> 00:23:35,600
Split any multipurpose columns.
639
00:23:35,600 --> 00:23:37,680
Step four, type discipline.
640
00:23:37,680 --> 00:23:40,760
Whole number for counts, decimal for measurements,
641
00:23:40,760 --> 00:23:43,000
currency for money, date only for dates,
642
00:23:43,000 --> 00:23:44,360
Boolean for binary facts.
643
00:23:44,360 --> 00:23:47,160
Step five, pre-process with power query.
644
00:23:47,160 --> 00:23:49,040
Trim uppercase keys collapse wide space,
645
00:23:49,040 --> 00:23:51,680
dedupe on alternate keys reject ambiguous rows.
646
00:23:51,680 --> 00:23:54,080
Map yes, no variance to true false ones.
647
00:23:54,080 --> 00:23:55,800
Step six, import order.
648
00:23:55,800 --> 00:23:58,200
Parents first, activate alternate keys,
649
00:23:58,200 --> 00:24:01,200
then products mapping text to lookups by those keys.
650
00:24:01,200 --> 00:24:04,440
First load X append, updates it will absurd only
651
00:24:04,440 --> 00:24:06,040
with clean keys.
652
00:24:06,040 --> 00:24:08,480
Step seven, integrity enforcement.
653
00:24:08,480 --> 00:24:11,960
Make lookups required, configure delete X-holes restrict
654
00:24:11,960 --> 00:24:15,240
on parents, add business rules for lifecycle constraints.
655
00:24:15,240 --> 00:24:16,960
Step eight, computation policy.
656
00:24:16,960 --> 00:24:19,840
Move math to calculated formula columns, cast currency
657
00:24:19,840 --> 00:24:21,240
to decimal where needed.
658
00:24:21,240 --> 00:24:22,840
No spreadsheet math in text.
659
00:24:22,840 --> 00:24:24,120
Step nine, governance.
660
00:24:24,120 --> 00:24:26,240
Roads, field security for sensitive columns,
661
00:24:26,240 --> 00:24:28,920
DLP policies, auditing on parents and intersects,
662
00:24:28,920 --> 00:24:31,120
manage environment optional IP firewall.
663
00:24:31,120 --> 00:24:34,040
Step 10, iterate, build validation views for orphans
664
00:24:34,040 --> 00:24:34,880
and duplicates.
665
00:24:34,880 --> 00:24:37,480
Schedule delta loads, monitor failures, fix pipelines,
666
00:24:37,480 --> 00:24:38,480
not just records.
667
00:24:38,480 --> 00:24:40,600
Do this and your import stop gambling.
668
00:24:40,600 --> 00:24:43,200
Your app stops lying, your data grows up.
669
00:24:43,200 --> 00:24:45,760
Power apps doesn't fix spreadsheet thinking.
670
00:24:45,760 --> 00:24:47,200
Clear data modeling does.
671
00:24:47,200 --> 00:24:50,400
Keys, types and relationships turn chaos into a system.
672
00:24:50,400 --> 00:24:52,160
If this finally snapped things into focus,
673
00:24:52,160 --> 00:24:53,400
do the efficient thing.
674
00:24:53,400 --> 00:24:56,000
Lock in your upgrade path, subscribe, turn on alerts
675
00:24:56,000 --> 00:24:58,280
and let the next episode deploy automatically.
676
00:24:58,280 --> 00:25:00,760
We're diving into dataverse governance next.
677
00:25:00,760 --> 00:25:04,080
Managed environments, role field security, auditing,
678
00:25:04,080 --> 00:25:07,080
DLP, the IP firewall, and why citizen developer
679
00:25:07,080 --> 00:25:09,040
shouldn't mean unmanaged risk.
680
00:25:09,040 --> 00:25:10,840
Press follow, enable notifications,
681
00:25:10,840 --> 00:25:13,920
and convert curiosity into a reliable signal.
682
00:25:13,920 --> 00:25:15,920
Entropy wins unless you choose structure.
00:00:00,000 --> 00:00:02,160
You clicked create app from Excel,
2
00:00:02,160 --> 00:00:06,120
felt clever and congratulations migrated your chaos at scale.
3
00:00:06,120 --> 00:00:07,280
Excel isn't a database,
4
00:00:07,280 --> 00:00:09,720
it's a calculator with delusions of grandeur.
5
00:00:09,720 --> 00:00:12,320
Dataverse enforces identity types and relationships
6
00:00:12,320 --> 00:00:15,040
and it will punish spreadsheet habits with integrity errors.
7
00:00:15,040 --> 00:00:16,520
The truth everyone gets this wrong.
8
00:00:16,520 --> 00:00:18,000
In the next minutes, I'll tear down
9
00:00:18,000 --> 00:00:21,160
the five failure patterns that quietly destroy your power apps,
10
00:00:21,160 --> 00:00:22,880
starting with the one that breaks everything,
11
00:00:22,880 --> 00:00:24,440
no primary keys.
12
00:00:24,440 --> 00:00:26,000
Then I'll rebuild your model,
13
00:00:26,000 --> 00:00:27,360
keys relationships, types,
14
00:00:27,360 --> 00:00:28,560
so import store employed,
15
00:00:28,560 --> 00:00:31,040
upsurbs behave and look ups actually link.
16
00:00:31,040 --> 00:00:33,360
You'll leave with a minimal remediation path
17
00:00:33,360 --> 00:00:35,120
that scales without surprises.
18
00:00:35,120 --> 00:00:38,320
Failure pattern one, no primary keys.
19
00:00:38,320 --> 00:00:40,800
Okay, so basically rows without unique identities
20
00:00:40,800 --> 00:00:42,240
are strangers at a reunion.
21
00:00:42,240 --> 00:00:44,280
Everyone smiles, nobody knows who's who
22
00:00:44,280 --> 00:00:46,600
and bad mergers marry the wrong cousins.
23
00:00:46,600 --> 00:00:49,680
In Excel, you get away with pretending product name is unique.
24
00:00:49,680 --> 00:00:52,360
In Dataverse, pretending gets you duplicate records,
25
00:00:52,360 --> 00:00:55,160
overwritten fields and upsurbs that behave like a blender.
26
00:00:55,160 --> 00:00:56,040
Why this matters?
27
00:00:56,040 --> 00:00:57,000
Without primary keys,
28
00:00:57,000 --> 00:00:59,360
Dataverse can't guarantee row level integrity.
29
00:00:59,360 --> 00:01:00,920
It can't tell whether an incoming row
30
00:01:00,920 --> 00:01:02,320
should insert or update.
31
00:01:02,320 --> 00:01:05,360
Your merge becomes roulette, the cost, corrupted history,
32
00:01:05,360 --> 00:01:08,560
phantom duplicates and automations firing on the wrong records.
33
00:01:08,560 --> 00:01:10,400
The aha moment you'll get today.
34
00:01:10,400 --> 00:01:12,840
Identity is not a column, it's a contract.
35
00:01:12,840 --> 00:01:15,360
The simple version is use surrogate keys for truth,
36
00:01:15,360 --> 00:01:17,360
alternate keys for convenience.
37
00:01:17,360 --> 00:01:19,920
Surrogate key, a guide that never changes
38
00:01:19,920 --> 00:01:21,840
and means nothing to humans.
39
00:01:21,840 --> 00:01:24,280
Alternate key, a human friendly uniqueness rule
40
00:01:24,280 --> 00:01:27,000
like supplier name plus country or product code
41
00:01:27,000 --> 00:01:29,440
if marketing swears it's unique, spoiler alert,
42
00:01:29,440 --> 00:01:31,440
they'll try to reuse it next quarter.
43
00:01:31,440 --> 00:01:32,520
Here's what most people miss.
44
00:01:32,520 --> 00:01:34,800
Excel's implicit uniqueness is fake.
45
00:01:34,800 --> 00:01:37,720
Hidden spaces in consistent case and copy paste debris
46
00:01:37,720 --> 00:01:40,960
mean acme tools and acme tools look the same to you
47
00:01:40,960 --> 00:01:41,920
different to the engine.
48
00:01:41,920 --> 00:01:44,320
In other words, your identity column is lying.
49
00:01:44,320 --> 00:01:45,640
What to model in Dataverse?
50
00:01:45,640 --> 00:01:48,560
Primary column, a surrogate guide, don't argue.
51
00:01:48,560 --> 00:01:51,080
It's stable under renames, mergers, rebrands
52
00:01:51,080 --> 00:01:53,880
and we border competitor and kept both lines.
53
00:01:53,880 --> 00:01:56,640
Alternate keys define the actual business uniqueness
54
00:01:56,640 --> 00:01:58,640
constraints your imports will use.
55
00:01:58,640 --> 00:02:01,720
Product code, fine, product name plus category.
56
00:02:01,720 --> 00:02:03,440
Only if you accept the future failure
57
00:02:03,440 --> 00:02:06,440
when someone recycles drill in a new category variant.
58
00:02:06,440 --> 00:02:08,240
Natural versus surrogate debate.
59
00:02:08,240 --> 00:02:09,560
This isn't a philosophy class.
60
00:02:09,560 --> 00:02:11,960
Natural keys drift, surrogates don't.
61
00:02:11,960 --> 00:02:14,200
How to fix before your next import?
62
00:02:14,200 --> 00:02:16,000
Add a key column in Excel now.
63
00:02:16,000 --> 00:02:19,200
If you lack a reliable code, generate a guide per row.
64
00:02:19,200 --> 00:02:22,600
Yes, in Excel, there are power query functions, use them.
65
00:02:22,600 --> 00:02:25,080
In Dataverse, keep the system GUID as primary.
66
00:02:25,080 --> 00:02:27,480
Then define alternate keys on the human fields
67
00:02:27,480 --> 00:02:28,720
you'll map from Excel.
68
00:02:28,720 --> 00:02:31,440
Supply a name, location name, category name.
69
00:02:31,440 --> 00:02:34,680
So data flows can resolve lookups by something other than vibes.
70
00:02:34,680 --> 00:02:36,320
Pre-de-dupe in power query.
71
00:02:36,320 --> 00:02:38,840
Trim, case normalized and collapse white space
72
00:02:38,840 --> 00:02:40,120
on your would-be keys.
73
00:02:40,120 --> 00:02:42,600
Reject ambiguous duplicates instead of hoping imports
74
00:02:42,600 --> 00:02:43,680
pick the right one.
75
00:02:43,680 --> 00:02:45,120
Hope is not a data strategy.
76
00:02:45,120 --> 00:02:47,840
In DataFloes, choose a pen for first loads.
77
00:02:47,840 --> 00:02:50,760
Use absurd only when your alternate keys are active and clean.
78
00:02:50,760 --> 00:02:53,560
Upset without keys is how you override last week's truth
79
00:02:53,560 --> 00:02:54,560
with this week's typo.
80
00:02:54,560 --> 00:02:55,480
Here's the weird part.
81
00:02:55,480 --> 00:02:58,640
The primary column in Dataverse, the so-called name column,
82
00:02:58,640 --> 00:02:59,760
confuses people.
83
00:02:59,760 --> 00:03:01,560
It's a display field, not your identity.
84
00:03:01,560 --> 00:03:03,480
You can rename it to product name for sanity,
85
00:03:03,480 --> 00:03:04,680
but it's not the key.
86
00:03:04,680 --> 00:03:06,480
The actual identity is the hidden guide.
87
00:03:06,480 --> 00:03:08,400
Treat the display field like a label on a box.
88
00:03:08,400 --> 00:03:11,200
The barcode is the GUID, a quick micro story.
89
00:03:11,200 --> 00:03:13,320
A team used product name as their merge key
90
00:03:13,320 --> 00:03:15,720
because we're small, it's fine.
91
00:03:15,720 --> 00:03:18,680
Then they launched widget pro, replacing widget.
92
00:03:18,680 --> 00:03:20,080
They bulk change names.
93
00:03:20,080 --> 00:03:22,960
Every historical order now pointed at the new product
94
00:03:22,960 --> 00:03:23,880
in reports.
95
00:03:23,880 --> 00:03:25,480
And finance thought revenue tripled.
96
00:03:25,480 --> 00:03:26,080
It hadn't.
97
00:03:26,080 --> 00:03:28,560
They had just erased their past with a find and replace.
98
00:03:28,560 --> 00:03:30,040
Keys would have prevented that.
99
00:03:30,040 --> 00:03:32,680
Practical mapping example, products, suppliers, locations.
100
00:03:32,680 --> 00:03:34,440
In Excel, add supplier key and location key
101
00:03:34,440 --> 00:03:35,960
by mapping names to stable codes.
102
00:03:35,960 --> 00:03:38,840
In Dataverse, create supplier table with a surrogate guide,
103
00:03:38,840 --> 00:03:41,960
alternate key on supplier name, same for location on location
104
00:03:41,960 --> 00:03:42,600
name.
105
00:03:42,600 --> 00:03:45,000
In your product's data flow, map supplier name
106
00:03:45,000 --> 00:03:47,480
from the source to the supplier alternate key.
107
00:03:47,480 --> 00:03:49,200
Now when Agme Tools arrives, Dataverse
108
00:03:49,200 --> 00:03:51,240
resolves it to the correct supplier row.
109
00:03:51,240 --> 00:03:54,760
If someone types Agme Tools, the alternate key normalization,
110
00:03:54,760 --> 00:03:56,920
either matches or your validation rejects it.
111
00:03:56,920 --> 00:04:00,280
Both outcomes are better than silently creating Agme V2.
112
00:04:00,280 --> 00:04:01,760
Append versus absurd?
113
00:04:01,760 --> 00:04:02,440
Clarified.
114
00:04:02,440 --> 00:04:04,280
Append says always insert.
115
00:04:04,280 --> 00:04:05,440
Great for first loads.
116
00:04:05,440 --> 00:04:08,480
Absurd says insert or update based on keys.
117
00:04:08,480 --> 00:04:10,400
Only safe when your alternate keys are enforced
118
00:04:10,400 --> 00:04:12,040
and your source is de-duplicated.
119
00:04:12,040 --> 00:04:14,840
If you absurd on product name, and there are two drill rows
120
00:04:14,840 --> 00:04:17,040
with slight differences, Dataverse
121
00:04:17,040 --> 00:04:18,760
doesn't know which one is canonical.
122
00:04:18,760 --> 00:04:20,320
You taught it ambiguity.
123
00:04:20,320 --> 00:04:23,200
Everything clicked when I realized identity precedes data.
124
00:04:23,200 --> 00:04:27,120
Without a non-negotiable ID, your rows are anecdotes, not records.
125
00:04:27,120 --> 00:04:29,680
Establish surrogate guides for permanence, alternate keys
126
00:04:29,680 --> 00:04:33,360
for usability, and enforce them before a single row crosses the border.
127
00:04:33,360 --> 00:04:37,480
Then and only then do types and relationships standard chance.
128
00:04:37,480 --> 00:04:40,040
Failure pattern two, mixed data types.
129
00:04:40,040 --> 00:04:43,120
With identity sorted, your types still betray you.
130
00:04:43,120 --> 00:04:48,280
Excel lets a single column host a family reunion, numbers, text, dates,
131
00:04:48,280 --> 00:04:50,280
and whatever someone pasted from outlook,
132
00:04:50,280 --> 00:04:52,200
sitting together like it's normal.
133
00:04:52,200 --> 00:04:54,280
Dataverse is not that tolerant roommate.
134
00:04:54,280 --> 00:04:57,600
It enforces types and it will throw your stuff out the window
135
00:04:57,600 --> 00:04:59,320
the second you violate the lease.
136
00:04:59,320 --> 00:05:00,200
Why this matters?
137
00:05:00,200 --> 00:05:02,480
Type confusion corrupts logic quietly.
138
00:05:02,480 --> 00:05:05,080
Calculations round wrong, comparisons fail, look ups miss,
139
00:05:05,080 --> 00:05:08,160
and your yes, no turns into yes, no, maybe blank,
140
00:05:08,160 --> 00:05:09,280
which is not a boolean.
141
00:05:09,280 --> 00:05:10,640
It's a cry for help.
142
00:05:10,640 --> 00:05:11,680
The cost is subtle.
143
00:05:11,680 --> 00:05:13,680
Flaky formulas, inconsistent views,
144
00:05:13,680 --> 00:05:16,920
and automations that branch on string values that look true but aren't.
145
00:05:16,920 --> 00:05:18,760
The truth formatting is not typing.
146
00:05:18,760 --> 00:05:20,320
Excel formatting is make up.
147
00:05:20,320 --> 00:05:21,920
Dataverse types are bone structure.
148
00:05:21,920 --> 00:05:26,520
You can highlight 1334.50 with a currency format all day.
149
00:05:26,520 --> 00:05:30,040
If it's stored as text, dataverse reads string not money,
150
00:05:30,040 --> 00:05:32,680
and then your roll ups, aggregations, and formula columns
151
00:05:32,680 --> 00:05:34,560
politely refuse to cooperate.
152
00:05:34,560 --> 00:05:38,160
What to model in dataverse, the simple version, text for text,
153
00:05:38,160 --> 00:05:39,760
not text that looks like a number.
154
00:05:39,760 --> 00:05:41,920
Whole number for counts you never fraction.
155
00:05:41,920 --> 00:05:43,720
Stock quantity is an integer.
156
00:05:43,720 --> 00:05:45,080
Stop feeding it.
157
00:05:45,080 --> 00:05:46,040
12.
158
00:05:46,040 --> 00:05:48,440
Decimal for measured values where precision matters.
159
00:05:48,440 --> 00:05:49,600
Waits dimensions.
160
00:05:49,600 --> 00:05:51,200
Choose the scale, live with it.
161
00:05:51,200 --> 00:05:52,520
Currency for monetary amounts.
162
00:05:52,520 --> 00:05:55,600
It stores value plus currency and respects rounding rules.
163
00:05:55,600 --> 00:05:57,040
Use it or suffer.
164
00:05:57,040 --> 00:05:59,160
Date only for dates without time.
165
00:05:59,160 --> 00:06:01,600
Date time for events with time zones.
166
00:06:01,600 --> 00:06:04,160
If you don't care about time, don't store it.
167
00:06:04,160 --> 00:06:05,920
Choice when the set is finite and governed,
168
00:06:05,920 --> 00:06:08,560
you get consistency, labels, and easy filtering.
169
00:06:08,560 --> 00:06:10,560
Look up when the value comes from another table.
170
00:06:10,560 --> 00:06:13,200
If it has metadata, it's a table, not a choice.
171
00:06:13,200 --> 00:06:14,720
Boolean for binary facts.
172
00:06:14,720 --> 00:06:15,760
Yes, no.
173
00:06:15,760 --> 00:06:17,720
Not yes, ish.
174
00:06:17,720 --> 00:06:19,200
Here's what most people miss.
175
00:06:19,200 --> 00:06:21,600
Excel's blank is not one thing.
176
00:06:21,600 --> 00:06:23,960
You'll see empty string, null, and a space character
177
00:06:23,960 --> 00:06:26,120
that rode in on a copy-paste safari.
178
00:06:26,120 --> 00:06:27,640
To you, they're all empty.
179
00:06:27,640 --> 00:06:29,800
To the engine, they're three distinct states.
180
00:06:29,800 --> 00:06:31,480
That's how required columns slip through
181
00:06:31,480 --> 00:06:34,600
and how active becomes active and ruins your choice mapping.
182
00:06:34,600 --> 00:06:36,120
How to fix this before you import?
183
00:06:36,120 --> 00:06:37,800
Prevalidate in Power Query.
184
00:06:37,800 --> 00:06:39,480
Coerc types explicitly.
185
00:06:39,480 --> 00:06:40,880
Don't hope the wizard guesses.
186
00:06:40,880 --> 00:06:43,720
Use text, trim, text, upper, and clean invisible characters
187
00:06:43,720 --> 00:06:44,480
with text.
188
00:06:44,480 --> 00:06:47,040
Select or by replacing charts 60 and friends.
189
00:06:47,040 --> 00:06:49,000
Normalize case for keys and choices.
190
00:06:49,000 --> 00:06:52,880
Collapse double spaces to single, reject ambiguous rows.
191
00:06:52,880 --> 00:06:55,800
If a date column contains Q1, FI25, that's not a date,
192
00:06:55,800 --> 00:06:57,040
that's a human note.
193
00:06:57,040 --> 00:06:58,120
Split it or drop it.
194
00:06:58,120 --> 00:07:00,240
You are not obliged to import garbage.
195
00:07:00,240 --> 00:07:01,320
Normalize Booleans.
196
00:07:01,320 --> 00:07:03,360
Map. Yes, no YN1, true false.
197
00:07:03,360 --> 00:07:04,560
To true false ones.
198
00:07:04,560 --> 00:07:05,440
Then lock it.
199
00:07:05,440 --> 00:07:06,680
No maybe.
200
00:07:06,680 --> 00:07:08,760
For currency, split the amount in the currency code
201
00:07:08,760 --> 00:07:09,560
if it's mixed.
202
00:07:09,560 --> 00:07:13,160
Load to a currency column with a known transaction currency.
203
00:07:13,160 --> 00:07:15,880
Don't play mix and match with symbols embedded in strings.
204
00:07:15,880 --> 00:07:17,160
Handle dates, say, inly.
205
00:07:17,160 --> 00:07:20,320
If time zones matter, convert to UTC before import.
206
00:07:20,320 --> 00:07:22,080
If they don't, use date only.
207
00:07:22,080 --> 00:07:25,240
Storing midnight local as UTC is how do on the first
208
00:07:25,240 --> 00:07:27,960
becomes do on the 31st for your colleagues elsewhere
209
00:07:27,960 --> 00:07:30,640
for numbers trapped as text use value.
210
00:07:30,640 --> 00:07:32,760
From text with culture settings or Power Queries
211
00:07:32,760 --> 00:07:34,520
local aware conversion.
212
00:07:34,520 --> 00:07:37,480
Then set precision, whole number, decimal or currency.
213
00:07:37,480 --> 00:07:38,080
Decide.
214
00:07:38,080 --> 00:07:39,520
Amiguity is the bug.
215
00:07:39,520 --> 00:07:41,120
Example you'll thank me for.
216
00:07:41,120 --> 00:07:42,400
Inventory.
217
00:07:42,400 --> 00:07:44,120
Value as a formula column.
218
00:07:44,120 --> 00:07:47,360
Stock quantity times unit price.
219
00:07:47,360 --> 00:07:48,520
Unit price is currency.
220
00:07:48,520 --> 00:07:50,240
Formula columns operate in decimal.
221
00:07:50,240 --> 00:07:52,400
Cast unit price to decimal in the formula.
222
00:07:52,400 --> 00:07:54,480
Now rounding is controlled, not accidental.
223
00:07:54,480 --> 00:07:56,560
Compare that to leaving unit price as text
224
00:07:56,560 --> 00:07:58,160
because it displays fine.
225
00:07:58,160 --> 00:08:01,800
Your formula will refuse or worse, co-earth, unpredictably.
226
00:08:01,800 --> 00:08:04,280
Choice versus lookup with types in mind.
227
00:08:04,280 --> 00:08:06,560
Category with five stable values.
228
00:08:06,560 --> 00:08:07,240
Choice.
229
00:08:07,240 --> 00:08:09,920
Supply with address, rating and compliance status.
230
00:08:09,920 --> 00:08:10,920
Lookup to suppliers.
231
00:08:10,920 --> 00:08:13,840
If you start with text, Acme, you'll spend eternity cleaning,
232
00:08:13,840 --> 00:08:16,320
Acme, Acme Inc. and Acme Inc.
233
00:08:16,320 --> 00:08:19,400
Converted to a lookup now and let data verse enforce
234
00:08:19,400 --> 00:08:22,160
one row per supplier, period, micro story.
235
00:08:22,160 --> 00:08:24,040
A team stored order date as text
236
00:08:24,040 --> 00:08:26,120
because the export looked like a date.
237
00:08:26,120 --> 00:08:27,640
Then they sorted descending and wondered
238
00:08:27,640 --> 00:08:29,840
why 930 came before 1001.
239
00:08:29,840 --> 00:08:31,040
Lexigraphic sort.
240
00:08:31,040 --> 00:08:32,200
They built KPIs on it.
241
00:08:32,200 --> 00:08:33,640
The quarter ended early in reports
242
00:08:33,640 --> 00:08:35,920
and sales missed their bonus on paper.
243
00:08:35,920 --> 00:08:37,600
One type change and their timeline
244
00:08:37,600 --> 00:08:39,600
snapped back to reality.
245
00:08:39,600 --> 00:08:42,480
Everything clicked when I realized types are contracts for meaning.
246
00:08:42,480 --> 00:08:45,040
Data verse can optimize, secure and calculate only
247
00:08:45,040 --> 00:08:47,720
when you declare what the data is, not how it looks.
248
00:08:47,720 --> 00:08:50,960
Declare text, number, decimal, currency, date only, choice,
249
00:08:50,960 --> 00:08:53,120
lookup, boolean, within 10th, co-earth, upstream,
250
00:08:53,120 --> 00:08:54,480
and reject the misfits.
251
00:08:54,480 --> 00:08:57,120
Then your formulas behave, your lookups resolve,
252
00:08:57,120 --> 00:08:59,360
and your app stops gaslighting you.
253
00:08:59,360 --> 00:09:01,720
Failure, pattern three, lookup as joints.
254
00:09:01,720 --> 00:09:03,120
Now we get to the training wheels.
255
00:09:03,120 --> 00:09:05,320
We look up, X lookup, index match,
256
00:09:05,320 --> 00:09:08,040
cute and a spreadsheet, dangerous in a data platform.
257
00:09:08,040 --> 00:09:12,040
In Excel, you emulate relationships by copying values across tables.
258
00:09:12,040 --> 00:09:14,000
In data verse, you define relationships
259
00:09:14,000 --> 00:09:16,640
so values never need to be copied in the first place.
260
00:09:16,640 --> 00:09:17,480
The truth?
261
00:09:17,480 --> 00:09:20,040
Your lookups are text duplication with extra steps.
262
00:09:20,040 --> 00:09:21,040
Why this matters?
263
00:09:21,040 --> 00:09:23,560
Spreadsheets hide referential drift.
264
00:09:23,560 --> 00:09:25,760
Your supplier name repeats in every product row,
265
00:09:25,760 --> 00:09:28,440
so when Agme tools becomes Agme tools-lutted,
266
00:09:28,440 --> 00:09:30,160
you miss one sheet, one tab, one row,
267
00:09:30,160 --> 00:09:33,400
and suddenly your filters split a single supplier into two ghosts.
268
00:09:33,400 --> 00:09:35,560
Data verse requires joints that carry identity,
269
00:09:35,560 --> 00:09:36,760
not fragile labels,
270
00:09:36,760 --> 00:09:39,680
so one rename doesn't shatter 100 dependent rows.
271
00:09:39,680 --> 00:09:40,960
Here's what most people miss.
272
00:09:40,960 --> 00:09:42,960
A lookup column in data verse stores a reference
273
00:09:42,960 --> 00:09:46,240
to the parent rows ID plus the display name for convenience.
274
00:09:46,240 --> 00:09:47,760
That ID is the relationship.
275
00:09:47,760 --> 00:09:49,080
Change the parent's display name
276
00:09:49,080 --> 00:09:51,240
and every child still points to the same record,
277
00:09:51,240 --> 00:09:53,400
no mass update, no prayer circle.
278
00:09:53,400 --> 00:09:55,920
Lookup stores text, lookup stores truth.
279
00:09:55,920 --> 00:09:59,080
Okay, so basically if a column repeats supplier name
280
00:09:59,080 --> 00:10:01,720
across thousands of product rows, that's not a column.
281
00:10:01,720 --> 00:10:04,200
That's a relationship screaming to become a lookup.
282
00:10:04,200 --> 00:10:06,160
Choice versus lookup is simple.
283
00:10:06,160 --> 00:10:09,200
If the list is finite and has no metadata, use a choice.
284
00:10:09,200 --> 00:10:12,160
If it has properties, history, rules, or compliance flags,
285
00:10:12,160 --> 00:10:14,080
it's a table, therefore a lookup.
286
00:10:14,080 --> 00:10:16,640
Suppliers, locations, categories with descriptions
287
00:10:16,640 --> 00:10:20,080
and hierarchies, lookups, status with five governed values,
288
00:10:20,080 --> 00:10:22,520
choice, how to model it right.
289
00:10:22,520 --> 00:10:26,040
Create dimension tables, supplier, location, category.
290
00:10:26,040 --> 00:10:28,080
Each gets a surrogate guide primary key
291
00:10:28,080 --> 00:10:29,920
and an alternate key on the human label
292
00:10:29,920 --> 00:10:32,720
you'll use during import, supplier name, location name,
293
00:10:32,720 --> 00:10:33,600
category code.
294
00:10:33,600 --> 00:10:36,080
In product, replace text columns with lookup columns
295
00:10:36,080 --> 00:10:36,920
to those tables.
296
00:10:36,920 --> 00:10:40,440
Do not store AgMe tools as text, store a reference to supplier.
297
00:10:40,440 --> 00:10:44,360
Define the relationship, cardinality, products, many to one suppliers,
298
00:10:44,360 --> 00:10:47,760
products, many to one locations, products, many to one categories.
299
00:10:47,760 --> 00:10:49,360
If you genuinely need many to many,
300
00:10:49,360 --> 00:10:51,720
engage products sold by multiple suppliers,
301
00:10:51,720 --> 00:10:54,960
model and intersect table, product supplier with two lookups.
302
00:10:54,960 --> 00:10:57,720
Don't fake it with comma separated text ever.
303
00:10:57,720 --> 00:10:59,800
How to fix the mess you already have.
304
00:10:59,800 --> 00:11:02,840
Preload the parents, import supplier, location,
305
00:11:02,840 --> 00:11:05,800
category first with alternate keys active,
306
00:11:05,800 --> 00:11:09,080
verify no duplicates, trim, uppercase,
307
00:11:09,080 --> 00:11:11,560
and normalize punctuation in power query.
308
00:11:11,560 --> 00:11:14,480
Reject collisions instead of inventing AgMe too.
309
00:11:14,480 --> 00:11:17,600
In your product data flow, map text to lookups by keys.
310
00:11:17,600 --> 00:11:20,240
In the column mapping choose supplier, lookup,
311
00:11:20,240 --> 00:11:22,760
map using supplier name, alternate key.
312
00:11:22,760 --> 00:11:24,280
Same for location and category.
313
00:11:24,280 --> 00:11:27,040
Now a string becomes a reference, not a duplicated label.
314
00:11:27,040 --> 00:11:29,040
After import, kill the obsolete text columns,
315
00:11:29,040 --> 00:11:30,760
leave no runway for future drift.
316
00:11:30,760 --> 00:11:32,400
The counter-intuitive part is this.
317
00:11:32,400 --> 00:11:35,400
Lookup felt safer because you could see the text.
318
00:11:35,400 --> 00:11:37,400
Lookups look abstract, but lookups
319
00:11:37,400 --> 00:11:39,640
are how you get integrity features for free.
320
00:11:39,640 --> 00:11:42,240
Cascade behavior, uniqueness checks, rollups,
321
00:11:42,240 --> 00:11:44,160
and security that can filter by parent.
322
00:11:44,160 --> 00:11:47,200
You can't roll up on text, you can roll up on relationships.
323
00:11:47,200 --> 00:11:50,600
Practically example, you build a report, inventory value by supplier.
324
00:11:50,600 --> 00:11:53,800
In Excel that means a pivot relying on consistent text.
325
00:11:53,800 --> 00:11:56,400
One string AgMe tools breaks aggregation.
326
00:11:56,400 --> 00:11:59,320
Then data verse the rollup counts by supplier ID.
327
00:11:59,320 --> 00:12:01,560
Tense spelling still resolve to one supplier
328
00:12:01,560 --> 00:12:04,000
because the relationship does the heavy lifting.
329
00:12:04,000 --> 00:12:06,200
You change suppliers display name once,
330
00:12:06,200 --> 00:12:09,400
the app, the views and power BI all show the new label
331
00:12:09,400 --> 00:12:10,680
without touching product rows.
332
00:12:10,680 --> 00:12:14,040
MicroStory, a team chained five V-lookups across tabs
333
00:12:14,040 --> 00:12:16,080
to pull categories, subcategories, supplier rating,
334
00:12:16,080 --> 00:12:18,560
and region into a flat product master.
335
00:12:18,560 --> 00:12:20,320
It worked until someone resorted a tab
336
00:12:20,320 --> 00:12:22,520
and pasted new rows with leading spaces.
337
00:12:22,520 --> 00:12:25,120
Overnight, 8% of products lost their supplier.
338
00:12:25,120 --> 00:12:27,600
Order still processed analytics quietly lied.
339
00:12:27,600 --> 00:12:30,400
They switched to lookups, enforced required relationships,
340
00:12:30,400 --> 00:12:33,880
and the missing supplier error surfaced at import where it belongs.
341
00:12:33,880 --> 00:12:36,320
Everything clicked when I realized lookup copies values,
342
00:12:36,320 --> 00:12:38,160
relationships bind identities.
343
00:12:38,160 --> 00:12:40,080
If it has metadata, give it a table.
344
00:12:40,080 --> 00:12:41,560
If it repeats, give it a lookup.
345
00:12:41,560 --> 00:12:44,680
Define alternate keys, so imports resolve text once,
346
00:12:44,680 --> 00:12:47,280
then let data verse carry the join forever.
347
00:12:47,280 --> 00:12:49,840
Failure pattern four, multipurpose columns.
348
00:12:49,840 --> 00:12:52,360
Here's where your spreadsheet instincts go full performance art.
349
00:12:52,360 --> 00:12:55,680
One column doing three jobs like a clown juggling chain source.
350
00:12:55,680 --> 00:12:57,720
Status that holds active, inactive,
351
00:12:57,720 --> 00:13:02,000
on hold discontinued and chef's kiss blank location.
352
00:13:02,000 --> 00:13:04,720
That sometimes means a warehouse, sometimes an address,
353
00:13:04,720 --> 00:13:07,360
sometimes left behind the receptionist desk.
354
00:13:07,360 --> 00:13:09,480
Excel tolerates this because it doesn't care.
355
00:13:09,480 --> 00:13:11,480
Data verse cares it enforces semantics
356
00:13:11,480 --> 00:13:14,000
and overloaded fields are how you sabotage validation,
357
00:13:14,000 --> 00:13:17,000
reporting and automation in one elegant move.
358
00:13:17,000 --> 00:13:17,800
Why this matters?
359
00:13:17,800 --> 00:13:19,560
Automation can't branch on mush.
360
00:13:19,560 --> 00:13:22,320
You can't build a rule that says if status is active,
361
00:13:22,320 --> 00:13:25,400
then reorder when status also contains on hold for audit
362
00:13:25,400 --> 00:13:28,440
because someone decided notes belong in the same cell.
363
00:13:28,440 --> 00:13:30,560
Reporting can't group on a field that mixes states
364
00:13:30,560 --> 00:13:32,640
with commentary and data quality rules
365
00:13:32,640 --> 00:13:35,200
collapse when a single column is asked to represent state,
366
00:13:35,200 --> 00:13:36,960
reference, and narrative all at once.
367
00:13:36,960 --> 00:13:37,800
The truth?
368
00:13:37,800 --> 00:13:40,280
It worked in my pivot is not proof of model quality.
369
00:13:40,280 --> 00:13:41,520
Pivots are magic tricks.
370
00:13:41,520 --> 00:13:43,880
They can summarize nonsense without complaining.
371
00:13:43,880 --> 00:13:45,080
Apps are not magicians.
372
00:13:45,080 --> 00:13:46,560
They need structure.
373
00:13:46,560 --> 00:13:48,240
What to model instead?
374
00:13:48,240 --> 00:13:50,360
Status is a choice, governed values,
375
00:13:50,360 --> 00:13:52,800
active inactive, on hold discontinued.
376
00:13:52,800 --> 00:13:55,200
Labels are human, stored values are clean.
377
00:13:55,200 --> 00:13:57,720
You get validation filtering and no accidental act.
378
00:13:57,720 --> 00:13:59,880
Location is a look up to a location table.
379
00:13:59,880 --> 00:14:02,920
If it has metadata, country address, capacity, time zone,
380
00:14:02,920 --> 00:14:04,040
it's a table.
381
00:14:04,040 --> 00:14:05,400
You want a single rope or warehouse,
382
00:14:05,400 --> 00:14:08,360
not 47 spellings of warehouse A. Notes are notes.
383
00:14:08,360 --> 00:14:12,000
Use a dedicated multi-line text or the built-in notes timeline.
384
00:14:12,000 --> 00:14:13,560
Commentary is separate from state.
385
00:14:13,560 --> 00:14:16,480
That way your audit trail doesn't contaminate your logic.
386
00:14:16,480 --> 00:14:17,800
Here's what most people miss.
387
00:14:17,800 --> 00:14:19,840
A catch all column looks efficient
388
00:14:19,840 --> 00:14:22,000
but creates silent divergence.
389
00:14:22,000 --> 00:14:24,080
The moment someone types, active, awaiting QA,
390
00:14:24,080 --> 00:14:26,080
you've added a brand new undocumented status
391
00:14:26,080 --> 00:14:28,520
that your flows and dashboards will misinterpret.
392
00:14:28,520 --> 00:14:30,240
Choice fields stop that at the door.
393
00:14:30,240 --> 00:14:31,960
You either add a new option deliberately
394
00:14:31,960 --> 00:14:33,600
or the import fails loudly.
395
00:14:33,600 --> 00:14:35,800
Failure at import beats failure in production.
396
00:14:35,800 --> 00:14:37,560
How to fix the mess you already have?
397
00:14:37,560 --> 00:14:39,800
Split columns with a mapping table.
398
00:14:39,800 --> 00:14:42,360
In Power Query, create a status map
399
00:14:42,360 --> 00:14:46,560
that converts freeform status text to a governed set.
400
00:14:46,560 --> 00:14:50,560
Active, ACT, in service, active.
401
00:14:50,560 --> 00:14:54,040
Disabled, inactive, retired, inactive, on hold.
402
00:14:54,200 --> 00:14:56,080
Pending QA, on hold.
403
00:14:56,080 --> 00:14:58,520
Store the original status in a new column called
404
00:14:58,520 --> 00:15:00,920
Status Roar for Forensic Nostalgia.
405
00:15:00,920 --> 00:15:03,120
Load the mapped value into a choice column.
406
00:15:03,120 --> 00:15:04,760
Extract references from text.
407
00:15:04,760 --> 00:15:06,880
For location strip commentary into a notes column
408
00:15:06,880 --> 00:15:08,800
and keep only the location name or code
409
00:15:08,800 --> 00:15:10,760
in a clean location name column.
410
00:15:10,760 --> 00:15:13,400
Preload the location table with distinct names,
411
00:15:13,400 --> 00:15:15,240
define an alternate key on name,
412
00:15:15,240 --> 00:15:17,360
then convert the location column in products
413
00:15:17,360 --> 00:15:19,440
to a lookup that resolves by that key.
414
00:15:19,440 --> 00:15:21,000
In force rules going forward,
415
00:15:21,000 --> 00:15:24,520
mark status as required and restrict edits to the choice.
416
00:15:24,520 --> 00:15:26,560
Make location required with a lookup?
417
00:15:26,560 --> 00:15:28,600
No free text locations.
418
00:15:28,600 --> 00:15:29,800
Add a business rule.
419
00:15:29,800 --> 00:15:31,280
If status equals discontinued,
420
00:15:31,280 --> 00:15:33,520
then this allows stock quantity updates.
421
00:15:33,520 --> 00:15:34,840
Your spreadsheet can't do that.
422
00:15:34,840 --> 00:15:37,200
Dataverse can, move math out of cells,
423
00:15:37,200 --> 00:15:39,600
replace inventory value calculated in Excel
424
00:15:39,600 --> 00:15:42,200
with a formula or calculated column in Dataverse
425
00:15:42,200 --> 00:15:44,880
where types are respected and casting is explicit.
426
00:15:44,880 --> 00:15:46,360
Spreadsheet math in a text field
427
00:15:46,360 --> 00:15:48,200
is how you end up summing strings.
428
00:15:48,200 --> 00:15:50,800
Microstory, a team used one state column
429
00:15:50,800 --> 00:15:53,200
for a lifecycle plus supply chain notes.
430
00:15:53,200 --> 00:15:57,120
Active, active, formal, active vendor-late, discount.
431
00:15:57,120 --> 00:15:59,800
Their active inventory value report was off by 18%
432
00:15:59,800 --> 00:16:02,920
because half the active rows didn't equal active.
433
00:16:02,920 --> 00:16:06,120
They migrated, split state into a choice status
434
00:16:06,120 --> 00:16:08,560
and a node column, backfilled with a mapping table
435
00:16:08,560 --> 00:16:10,920
and suddenly procurement stopped overordering,
436
00:16:10,920 --> 00:16:13,840
not because demand changed because the data stopped lying.
437
00:16:13,840 --> 00:16:15,400
Practical migration steps.
438
00:16:15,400 --> 00:16:17,960
In Power Query.
439
00:16:17,960 --> 00:16:20,280
Duplicate the overloaded column.
440
00:16:20,280 --> 00:16:21,840
One becomes the governed value.
441
00:16:21,840 --> 00:16:23,600
The other becomes raw notes.
442
00:16:23,600 --> 00:16:26,600
Use custom logic to pass out markers like parentheses, dashes
443
00:16:26,600 --> 00:16:28,400
and commas to isolate the state token.
444
00:16:28,400 --> 00:16:32,560
In Dataverse, create status, choice, location, lookup,
445
00:16:32,560 --> 00:16:34,880
and notes, multi-line text.
446
00:16:34,880 --> 00:16:38,520
Add an alternate key on location, name, load locations first.
447
00:16:38,520 --> 00:16:40,280
Enable required fields and prevent
448
00:16:40,280 --> 00:16:42,360
blank values via business rules.
449
00:16:42,360 --> 00:16:46,080
Post import, cleanup, delete or hide the old free text columns.
450
00:16:46,080 --> 00:16:47,280
Do not leave escape hatches.
451
00:16:47,280 --> 00:16:49,080
Future you deserve less chaos.
452
00:16:49,080 --> 00:16:52,240
Everything clicked when I realized columns should carry one meaning.
453
00:16:52,240 --> 00:16:55,480
Choices capture state, lookup's capture identity,
454
00:16:55,480 --> 00:16:56,960
node's capture narrative.
455
00:16:56,960 --> 00:16:58,760
Keep them separate and everything else.
456
00:16:58,760 --> 00:17:01,520
Validation, views, flows, stops tripping
457
00:17:01,520 --> 00:17:03,720
over your clever consolidation.
458
00:17:03,720 --> 00:17:05,000
Failure pattern five.
459
00:17:05,000 --> 00:17:06,120
Often rows.
460
00:17:06,120 --> 00:17:08,600
Now for the cardinal sin, children without parents.
461
00:17:08,600 --> 00:17:10,680
In spreadsheets, you delete a supplier row
462
00:17:10,680 --> 00:17:13,240
and forget that one 200 products still reference
463
00:17:13,240 --> 00:17:15,680
ag me tools by name, nothing complains.
464
00:17:15,680 --> 00:17:17,360
Your pivot just silently excludes them
465
00:17:17,360 --> 00:17:20,480
or worse shows a new category called blank.
466
00:17:20,480 --> 00:17:22,000
In Dataverse, that's an often,
467
00:17:22,000 --> 00:17:24,520
products referencing a non-existent supplier.
468
00:17:24,520 --> 00:17:28,920
An unlike Excel Dataverse has opinions about often, strong ones.
469
00:17:28,920 --> 00:17:29,920
Why this matters?
470
00:17:29,920 --> 00:17:33,080
Without enforced relationships, your data becomes fiction.
471
00:17:33,080 --> 00:17:35,360
Reports show totals that don't tie out,
472
00:17:35,360 --> 00:17:37,480
automations fire on products with no supplier
473
00:17:37,480 --> 00:17:39,000
and then fail downstream.
474
00:17:39,000 --> 00:17:40,960
Security rules that rely on hierarchies
475
00:17:40,960 --> 00:17:43,440
can't filter correctly because the parent is missing.
476
00:17:43,440 --> 00:17:46,600
Offends are integrity errors wearing invisibility cloaks.
477
00:17:46,600 --> 00:17:49,080
They pass casual inspection and break under stress.
478
00:17:49,080 --> 00:17:49,920
The truth?
479
00:17:49,920 --> 00:17:52,640
Referential integrity isn't optional housekeeping.
480
00:17:52,640 --> 00:17:54,080
It's the foundation.
481
00:17:54,080 --> 00:17:56,400
If a child requires a parent to make sense,
482
00:17:56,400 --> 00:17:59,080
make the relationship required and define cascade behavior.
483
00:17:59,080 --> 00:18:00,240
You don't get credit for,
484
00:18:00,240 --> 00:18:02,800
we usually remember to create the supplier first.
485
00:18:02,800 --> 00:18:05,320
Systems enforce what people forget, what to model,
486
00:18:05,320 --> 00:18:07,800
required lookups for essential relationships.
487
00:18:07,800 --> 00:18:11,040
Product must have a supplier, product must have a category.
488
00:18:11,040 --> 00:18:13,480
If the business truly allows unknown supplier,
489
00:18:13,480 --> 00:18:15,720
model a specific supplier row named unknown
490
00:18:15,720 --> 00:18:18,720
with a proper ID, not a null, ambiguity gets one,
491
00:18:18,720 --> 00:18:21,200
explicit placeholder, governed and auditable.
492
00:18:21,200 --> 00:18:23,520
Cascade behaviors for delete, pick, restrict,
493
00:18:23,520 --> 00:18:25,480
if deleting a parent would be catastrophic.
494
00:18:25,480 --> 00:18:27,880
Pick cascade if deletion should remove all children,
495
00:18:27,880 --> 00:18:30,240
rare intransactional domains for repairing
496
00:18:30,240 --> 00:18:32,080
or name changes cascade.
497
00:18:32,080 --> 00:18:33,680
All ensures the display name updates
498
00:18:33,680 --> 00:18:35,840
everywhere without creating text drift.
499
00:18:35,840 --> 00:18:37,600
Intersect tables for many to many.
500
00:18:37,600 --> 00:18:39,640
Don't fake multiple suppliers per product
501
00:18:39,640 --> 00:18:41,520
with comma separated text.
502
00:18:41,520 --> 00:18:44,120
Create product supplier with two lookups, each required.
503
00:18:44,120 --> 00:18:45,560
That table becomes the relationship
504
00:18:45,560 --> 00:18:47,360
with its own audit and life cycle.
505
00:18:47,360 --> 00:18:49,000
How to fix before you import?
506
00:18:49,000 --> 00:18:49,960
Preload parents.
507
00:18:49,960 --> 00:18:52,360
Import suppliers, locations, categories first.
508
00:18:52,360 --> 00:18:55,360
Activate alternate keys on the labels you'll resolve against.
509
00:18:55,360 --> 00:18:57,320
Validate distinctness aggressively trim,
510
00:18:57,320 --> 00:18:59,400
normalize case and strip punctuation.
511
00:18:59,400 --> 00:19:02,600
Reject duplicates instead of spawning supplier two.
512
00:19:02,600 --> 00:19:04,160
Sequence your data flows.
513
00:19:04,160 --> 00:19:06,560
Children after parents in the product data flow
514
00:19:06,560 --> 00:19:08,400
map supplier by alternate key.
515
00:19:08,400 --> 00:19:10,840
If a supplier doesn't resolve, fail the row and log it.
516
00:19:10,840 --> 00:19:12,840
Do not auto create parents from child text
517
00:19:12,840 --> 00:19:14,920
unless you enjoy polluted masters.
518
00:19:14,920 --> 00:19:16,960
Enforced at the schema, mark the lookup
519
00:19:16,960 --> 00:19:19,760
as required in data verse so someone can't sneak a null
520
00:19:19,760 --> 00:19:22,120
through the form, the API or a rogue import.
521
00:19:22,120 --> 00:19:24,000
Required means required everywhere.
522
00:19:24,000 --> 00:19:25,480
How to fix after the fact?
523
00:19:25,480 --> 00:19:26,920
Detect office with views.
524
00:19:26,920 --> 00:19:29,160
Create a view in products where supplier is blank.
525
00:19:29,160 --> 00:19:31,320
If you've made it required, you shouldn't see any.
526
00:19:31,320 --> 00:19:33,280
If you do, they came from legacy imports
527
00:19:33,280 --> 00:19:34,840
or a misconfigured integration,
528
00:19:34,840 --> 00:19:36,840
fix the pipeline, then fix the data.
529
00:19:36,840 --> 00:19:38,480
Backfill often intentionally.
530
00:19:38,480 --> 00:19:40,600
Create or identify the correct parent rows,
531
00:19:40,600 --> 00:19:42,600
then update children to point to them.
532
00:19:42,600 --> 00:19:44,520
If unknown, link to the unknown parent
533
00:19:44,520 --> 00:19:46,800
and add a note explaining the provenance,
534
00:19:46,800 --> 00:19:49,040
then assign an owner to resolve within an SLA.
535
00:19:49,040 --> 00:19:50,040
Lockdown deletion.
536
00:19:50,040 --> 00:19:52,000
Set delete behavior on parents to restrict,
537
00:19:52,000 --> 00:19:53,680
add a business rule or custom plug-in
538
00:19:53,680 --> 00:19:56,080
to block deletion if active children exist
539
00:19:56,080 --> 00:19:58,960
and provide a friendly error with a link to related records.
540
00:19:58,960 --> 00:20:01,240
Friendly meaning precise and smugly unambiguous.
541
00:20:01,240 --> 00:20:02,240
Micro story.
542
00:20:02,240 --> 00:20:04,000
Finance complained that supplier spend
543
00:20:04,000 --> 00:20:05,840
didn't match inventory value.
544
00:20:05,840 --> 00:20:08,760
Investigation found 9% of products had null suppliers
545
00:20:08,760 --> 00:20:10,040
from an old import.
546
00:20:10,040 --> 00:20:11,840
Reports grouped them under unassigned,
547
00:20:11,840 --> 00:20:14,240
which executives dutifully ignored.
548
00:20:14,240 --> 00:20:16,360
Required lookups, preload of suppliers
549
00:20:16,360 --> 00:20:19,800
and restrict on delete eliminated orphans in one release cycle.
550
00:20:19,800 --> 00:20:21,520
The next quarter spent matched inventory,
551
00:20:21,520 --> 00:20:23,000
not because the business improved
552
00:20:23,000 --> 00:20:25,840
because relationships existed, concurrency twist.
553
00:20:25,840 --> 00:20:28,120
In Excel, two people fixed the same parent
554
00:20:28,120 --> 00:20:30,720
concurrently and override each other with no trace.
555
00:20:30,720 --> 00:20:32,640
In dataverse, optimistic concurrency blocks
556
00:20:32,640 --> 00:20:34,880
stale updates and preserves integrity,
557
00:20:34,880 --> 00:20:36,480
unless you've left children dangling,
558
00:20:36,480 --> 00:20:38,400
then you get a lot of retries and nonsense,
559
00:20:38,400 --> 00:20:41,040
model-required relationships and the platform's transaction scope
560
00:20:41,040 --> 00:20:42,560
gives you atomic sanity.
561
00:20:42,560 --> 00:20:45,120
Either the parent and children updates succeed together
562
00:20:45,120 --> 00:20:46,280
or nothing changes.
563
00:20:46,280 --> 00:20:47,520
Practical checklist.
564
00:20:47,520 --> 00:20:49,360
Parents first, always.
565
00:20:49,360 --> 00:20:52,160
Data flows ordered, suppliers, locations, categories,
566
00:20:52,160 --> 00:20:54,720
then products, alternate keys on parent labels
567
00:20:54,720 --> 00:20:56,560
to resolve lookups during import,
568
00:20:56,560 --> 00:21:00,560
required lookups on critical child tables, no nulls.
569
00:21:00,560 --> 00:21:04,000
Use explicit unknown rows only if the business demands it
570
00:21:04,000 --> 00:21:06,920
and report on them relentlessly until they go to zero.
571
00:21:06,920 --> 00:21:08,680
Delete behavior set to restrict on parents
572
00:21:08,680 --> 00:21:11,640
with active children, no casual amputation.
573
00:21:11,640 --> 00:21:14,920
Monitoring, scheduled view or power BI alert for often candidates
574
00:21:14,920 --> 00:21:17,360
plus audit enabled on parent and intersect tables
575
00:21:17,360 --> 00:21:19,600
to trace who tried to create orphans and when.
576
00:21:19,600 --> 00:21:21,480
Everything clicked when I realized
577
00:21:21,480 --> 00:21:25,080
referential integrity is not nice to have, its gravity.
578
00:21:25,080 --> 00:21:27,480
Ignore it and data floats off into space.
579
00:21:27,480 --> 00:21:29,760
Enforced required lookups, preload parents,
580
00:21:29,760 --> 00:21:31,640
configure cascades like an adult
581
00:21:31,640 --> 00:21:33,080
and orphans stop existing,
582
00:21:33,080 --> 00:21:34,600
not by luck by design.
583
00:21:34,600 --> 00:21:37,200
We're proving it, Excel versus Dataverse.
584
00:21:37,200 --> 00:21:39,400
Stress benchmarks, let's stop theorizing
585
00:21:39,400 --> 00:21:40,520
and step on the gas.
586
00:21:40,520 --> 00:21:41,440
Row limits first.
587
00:21:41,440 --> 00:21:44,000
Excel gets sluggish past a few hundred thousand rows,
588
00:21:44,000 --> 00:21:46,480
formulas recount like they're waiting through syrup.
589
00:21:46,480 --> 00:21:49,080
Dataverse indexes columns, query server side
590
00:21:49,080 --> 00:21:50,120
and streams results.
591
00:21:50,120 --> 00:21:51,800
The truth, 10 filters on a million rows
592
00:21:51,800 --> 00:21:54,520
is still a blink when the engine owns the index.
593
00:21:54,520 --> 00:21:55,600
Concurrency.
594
00:21:55,600 --> 00:21:58,400
Shared file on a network drive, one added wins,
595
00:21:58,400 --> 00:22:01,640
the other user silently loses and no one knows which.
596
00:22:01,640 --> 00:22:05,280
Dataverse uses optimistic concurrency and transactions.
597
00:22:05,280 --> 00:22:07,200
Two users save conflicting edits.
598
00:22:07,200 --> 00:22:09,280
The stale one is blocked with a version error.
599
00:22:09,280 --> 00:22:11,080
Annoying? Yes.
600
00:22:11,080 --> 00:22:13,680
Also how you avoid ghost edits, audit.
601
00:22:13,680 --> 00:22:17,240
Excel has last modified by whoever saved last.
602
00:22:17,240 --> 00:22:19,840
Dataverse has auditing and change tracking.
603
00:22:19,840 --> 00:22:22,320
Who, what, when, before, after?
604
00:22:22,320 --> 00:22:24,320
You can replay history or drive delta loads
605
00:22:24,320 --> 00:22:25,920
compared that to manual versioning
606
00:22:25,920 --> 00:22:30,640
with final v7 real Excel asks painful security.
607
00:22:30,640 --> 00:22:32,640
Everyone on the share is not a model.
608
00:22:32,640 --> 00:22:33,800
It's a shrug.
609
00:22:33,800 --> 00:22:36,760
Dataverse offers role-based and field-level security.
610
00:22:36,760 --> 00:22:38,880
A junior user can see price but not margin.
611
00:22:38,880 --> 00:22:40,560
Your spreadsheet cannot keep a secret.
612
00:22:40,560 --> 00:22:41,560
Don't pretend it can.
613
00:22:41,560 --> 00:22:42,560
API throughput.
614
00:22:42,560 --> 00:22:44,680
Copy paste is not an integration strategy.
615
00:22:44,680 --> 00:22:46,080
Dataverse has data flows,
616
00:22:46,080 --> 00:22:48,320
APIs, power automate and bulk operations.
617
00:22:48,320 --> 00:22:50,080
You move thousands of rows predictably,
618
00:22:50,080 --> 00:22:51,880
identitently and on schedule.
619
00:22:51,880 --> 00:22:53,560
Reliability beats heroics.
620
00:22:53,560 --> 00:22:55,080
Referential integrity.
621
00:22:55,080 --> 00:22:57,040
Lookup illusions fracture under rename,
622
00:22:57,040 --> 00:22:59,520
resort or oops, I deleted the tab.
623
00:22:59,520 --> 00:23:02,600
Dataverse enforces relationships and cascades.
624
00:23:02,600 --> 00:23:04,800
Parents first children follow rules applied.
625
00:23:04,800 --> 00:23:06,960
It's civilization not chaos.
626
00:23:06,960 --> 00:23:08,600
Minimal remediation path.
627
00:23:08,600 --> 00:23:09,960
The fix that sticks.
628
00:23:09,960 --> 00:23:12,240
Here's the fix you actually follow tomorrow.
629
00:23:12,240 --> 00:23:14,360
Step one, model core entities,
630
00:23:14,360 --> 00:23:16,760
products, suppliers, locations, categories,
631
00:23:16,760 --> 00:23:18,960
one table per concept, no casuals.
632
00:23:18,960 --> 00:23:20,440
Step two, keys.
633
00:23:20,440 --> 00:23:22,320
Keep data versus GUID primary.
634
00:23:22,320 --> 00:23:23,840
Add alternate keys on supplier.
635
00:23:23,840 --> 00:23:28,120
Name, location, name, category.code, product, bond, code,
636
00:23:28,120 --> 00:23:29,880
step three, normalize.
637
00:23:29,880 --> 00:23:33,760
Choice for status, lookup for supplier, location, category.
638
00:23:33,760 --> 00:23:35,600
Split any multipurpose columns.
639
00:23:35,600 --> 00:23:37,680
Step four, type discipline.
640
00:23:37,680 --> 00:23:40,760
Whole number for counts, decimal for measurements,
641
00:23:40,760 --> 00:23:43,000
currency for money, date only for dates,
642
00:23:43,000 --> 00:23:44,360
Boolean for binary facts.
643
00:23:44,360 --> 00:23:47,160
Step five, pre-process with power query.
644
00:23:47,160 --> 00:23:49,040
Trim uppercase keys collapse wide space,
645
00:23:49,040 --> 00:23:51,680
dedupe on alternate keys reject ambiguous rows.
646
00:23:51,680 --> 00:23:54,080
Map yes, no variance to true false ones.
647
00:23:54,080 --> 00:23:55,800
Step six, import order.
648
00:23:55,800 --> 00:23:58,200
Parents first, activate alternate keys,
649
00:23:58,200 --> 00:24:01,200
then products mapping text to lookups by those keys.
650
00:24:01,200 --> 00:24:04,440
First load X append, updates it will absurd only
651
00:24:04,440 --> 00:24:06,040
with clean keys.
652
00:24:06,040 --> 00:24:08,480
Step seven, integrity enforcement.
653
00:24:08,480 --> 00:24:11,960
Make lookups required, configure delete X-holes restrict
654
00:24:11,960 --> 00:24:15,240
on parents, add business rules for lifecycle constraints.
655
00:24:15,240 --> 00:24:16,960
Step eight, computation policy.
656
00:24:16,960 --> 00:24:19,840
Move math to calculated formula columns, cast currency
657
00:24:19,840 --> 00:24:21,240
to decimal where needed.
658
00:24:21,240 --> 00:24:22,840
No spreadsheet math in text.
659
00:24:22,840 --> 00:24:24,120
Step nine, governance.
660
00:24:24,120 --> 00:24:26,240
Roads, field security for sensitive columns,
661
00:24:26,240 --> 00:24:28,920
DLP policies, auditing on parents and intersects,
662
00:24:28,920 --> 00:24:31,120
manage environment optional IP firewall.
663
00:24:31,120 --> 00:24:34,040
Step 10, iterate, build validation views for orphans
664
00:24:34,040 --> 00:24:34,880
and duplicates.
665
00:24:34,880 --> 00:24:37,480
Schedule delta loads, monitor failures, fix pipelines,
666
00:24:37,480 --> 00:24:38,480
not just records.
667
00:24:38,480 --> 00:24:40,600
Do this and your import stop gambling.
668
00:24:40,600 --> 00:24:43,200
Your app stops lying, your data grows up.
669
00:24:43,200 --> 00:24:45,760
Power apps doesn't fix spreadsheet thinking.
670
00:24:45,760 --> 00:24:47,200
Clear data modeling does.
671
00:24:47,200 --> 00:24:50,400
Keys, types and relationships turn chaos into a system.
672
00:24:50,400 --> 00:24:52,160
If this finally snapped things into focus,
673
00:24:52,160 --> 00:24:53,400
do the efficient thing.
674
00:24:53,400 --> 00:24:56,000
Lock in your upgrade path, subscribe, turn on alerts
675
00:24:56,000 --> 00:24:58,280
and let the next episode deploy automatically.
676
00:24:58,280 --> 00:25:00,760
We're diving into dataverse governance next.
677
00:25:00,760 --> 00:25:04,080
Managed environments, role field security, auditing,
678
00:25:04,080 --> 00:25:07,080
DLP, the IP firewall, and why citizen developer
679
00:25:07,080 --> 00:25:09,040
shouldn't mean unmanaged risk.
680
00:25:09,040 --> 00:25:10,840
Press follow, enable notifications,
681
00:25:10,840 --> 00:25:13,920
and convert curiosity into a reliable signal.
682
00:25:13,920 --> 00:25:15,920
Entropy wins unless you choose structure.
