Why Your Excel Data Will Still Fail

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.