1 00:00:00,040 --> 00:00:03,759 Welcome to the M365 FM podcast, where we explore the people, 2 00:00:03,759 --> 00:00:06,919 ideas and technologies shaping the future of work. 3 00:00:06,919 --> 00:00:08,240 Your power BI reports keep 4 00:00:08,240 --> 00:00:09,580 redoing the same clean up a data 5 00:00:09,580 --> 00:00:11,599 flow could do once every refresh 6 00:00:11,599 --> 00:00:13,039 reruns the same steps, every 7 00:00:13,039 --> 00:00:14,640 report keeps its own copy, and 8 00:00:14,640 --> 00:00:15,919 that duplication slows things 9 00:00:15,919 --> 00:00:17,039 down and hammers your source 10 00:00:17,039 --> 00:00:17,719 system. 11 00:00:17,719 --> 00:00:21,679 Welcome to the M365 FM podcast, where we explore the people, 12 00:00:21,679 --> 00:00:24,120 ideas, and technologies shaping the future of work. 13 00:00:24,120 --> 00:00:25,519 You can prepare that data once 14 00:00:25,519 --> 00:00:26,920 in the cloud, then reuse it 15 00:00:26,920 --> 00:00:27,480 everywhere. 16 00:00:27,480 --> 00:00:29,879 By the end, you'll know where your data actually sits. 17 00:00:29,879 --> 00:00:31,120 When a data flow is worth the 18 00:00:31,120 --> 00:00:32,359 effort, and which of the two 19 00:00:32,359 --> 00:00:32,719 versions. 20 00:00:32,719 --> 00:00:36,240 To learn the messy data problem everyone hits. 21 00:00:36,240 --> 00:00:37,600 First, start with the thing 22 00:00:37,600 --> 00:00:39,079 you've probably already lived 23 00:00:39,079 --> 00:00:39,719 through. 24 00:00:39,719 --> 00:00:41,679 Sales numbers arrive in a spreadsheet, and the spreadsheet 25 00:00:41,679 --> 00:00:42,640 is a mess. 26 00:00:42,640 --> 00:00:44,640 Store names are spelled three different ways. 27 00:00:44,640 --> 00:00:46,240 Half the dates sit in one format 28 00:00:46,240 --> 00:00:47,840 and half in another, and someone 29 00:00:47,840 --> 00:00:49,039 left a trailing space on every 30 00:00:49,039 --> 00:00:49,799 single row. 31 00:00:49,799 --> 00:00:51,200 So you clean it, you trim the 32 00:00:51,200 --> 00:00:52,880 spaces, you fix the dates, and 33 00:00:52,880 --> 00:00:54,280 you merge the store names into 34 00:00:54,280 --> 00:00:54,759 one. 35 00:00:54,759 --> 00:00:55,759 Twenty minutes later, a 36 00:00:55,759 --> 00:00:56,960 colleague opens the same file 37 00:00:56,960 --> 00:00:58,399 and does the exact same cleanup, 38 00:00:58,399 --> 00:00:59,494 except their version comes out 39 00:00:59,494 --> 00:01:00,229 slightly different. 40 00:01:00,229 --> 00:01:01,429 Now the finance report and the 41 00:01:01,429 --> 00:01:02,829 sales report disagree about the 42 00:01:02,829 --> 00:01:03,229 same week. 43 00:01:03,229 --> 00:01:04,430 And nobody can explain why. 44 00:01:04,430 --> 00:01:05,549 This is the text that sits 45 00:01:05,549 --> 00:01:06,829 underneath a lot of reporting 46 00:01:06,829 --> 00:01:07,430 work. 47 00:01:07,430 --> 00:01:09,829 Every report carries its own copy of the clean up logic. 48 00:01:09,829 --> 00:01:12,069 So the same steps run over and over again. 49 00:01:12,069 --> 00:01:14,390 Your source system gets hit by people pulling the same rows, 50 00:01:14,390 --> 00:01:17,030 and every fix has to be repeated in five different places. 51 00:01:17,030 --> 00:01:19,469 The real tension here is that none of this is hard work. 52 00:01:19,469 --> 00:01:21,150 Trimming a space isn't difficult. 53 00:01:21,150 --> 00:01:23,709 Doing it four hundred times across four different files is 54 00:01:23,709 --> 00:01:24,590 what wears people down. 55 00:01:24,590 --> 00:01:27,030 And the moment the numbers disagree, you lose the room. 56 00:01:27,030 --> 00:01:29,870 What makes it worse is that the cycle never actually stops. 57 00:01:29,870 --> 00:01:32,390 New data keeps arriving, new reports keep getting built, and 58 00:01:32,390 --> 00:01:34,510 every one of them starts that clean up from zero. 59 00:01:34,510 --> 00:01:36,909 Nobody sets out to duplicate work, and it happens anyway 60 00:01:36,909 --> 00:01:39,269 because the clean version of the data doesn't live anywhere. 61 00:01:39,269 --> 00:01:39,790 Shared. 62 00:01:39,790 --> 00:01:42,390 Now imagine the cleaning happened once in one place in 63 00:01:42,390 --> 00:01:45,189 the cloud and every report picked up the finished result. 64 00:01:45,189 --> 00:01:47,909 That's the problem a data flow is built to solve, and once you 65 00:01:47,909 --> 00:01:51,030 see it that way, the whole thing stops feeling technical. 66 00:01:51,030 --> 00:01:53,069 What a data flow actually is. 67 00:01:53,069 --> 00:01:54,510 A data flow is a cloud service 68 00:01:54,510 --> 00:01:56,109 that gets data from a source, 69 00:01:56,109 --> 00:01:57,829 cleans and shapes it, and saves 70 00:01:57,829 --> 00:01:59,349 the result as a table you can 71 00:01:59,349 --> 00:01:59,870 reuse. 72 00:01:59,870 --> 00:02:01,390 That's the whole idea in a single sentence. 73 00:02:01,390 --> 00:02:04,230 The work runs on Microsoft servers rather than your laptop, 74 00:02:04,230 --> 00:02:06,870 so your machine can be switched off while it happens. 75 00:02:06,870 --> 00:02:09,710 The tool doing the cleaning is Power Query, and you've probably 76 00:02:09,710 --> 00:02:10,550 already met it. 77 00:02:10,550 --> 00:02:12,909 It's the same engine behind the Get and Transform button in 78 00:02:12,909 --> 00:02:15,389 Excel and the transform step in power BI desktop. 79 00:02:15,389 --> 00:02:17,909 If you can click through those menus and fix a column, you 80 00:02:17,909 --> 00:02:20,270 already hold the skill a dataflow needs with no new 81 00:02:20,270 --> 00:02:22,189 language and no coding required. 82 00:02:22,189 --> 00:02:24,870 Every dataflow does three things in the same order. 83 00:02:24,870 --> 00:02:27,389 It connects to a source, which could be a database, a 84 00:02:27,389 --> 00:02:30,069 spreadsheet, a web service, or a folder full of files. 85 00:02:30,069 --> 00:02:32,110 Then it transforms the data, which is a fancy word for 86 00:02:32,110 --> 00:02:34,509 cleaning and reshaping it the way you tidy a messy sheet. 87 00:02:34,509 --> 00:02:35,669 Finally, it loads the result 88 00:02:35,669 --> 00:02:37,310 into storage as a clean table 89 00:02:37,310 --> 00:02:38,449 that's ready for anything to 90 00:02:38,449 --> 00:02:38,949 use. 91 00:02:38,949 --> 00:02:40,629 Think about how that plays out in practice. 92 00:02:40,629 --> 00:02:41,830 Say you pull a sales file every 93 00:02:41,830 --> 00:02:43,030 Monday and it always needs the 94 00:02:43,030 --> 00:02:43,669 same treatment. 95 00:02:43,669 --> 00:02:46,590 You clean the spaces, split one messy date column into two clean 96 00:02:46,590 --> 00:02:48,349 ones, and rename a few headers. 97 00:02:48,349 --> 00:02:49,550 You save those steps as a 98 00:02:49,550 --> 00:02:51,030 dataflow, put it on a Monday 99 00:02:51,030 --> 00:02:52,430 morning schedule, and from then 100 00:02:52,430 --> 00:02:54,550 on the clean table just appears 101 00:02:54,550 --> 00:02:55,830 without you touching a single 102 00:02:55,830 --> 00:02:56,110 menu. 103 00:02:56,110 --> 00:02:57,740 Again, it can reach more than 104 00:02:57,740 --> 00:02:58,900 eighty different kinds of 105 00:02:58,900 --> 00:03:00,460 sources, which matters because 106 00:03:00,460 --> 00:03:01,900 real companies keep their data 107 00:03:01,900 --> 00:03:03,300 in more places than they'd like 108 00:03:03,300 --> 00:03:04,099 to admit. 109 00:03:04,099 --> 00:03:05,539 One data flow can pull from the 110 00:03:05,539 --> 00:03:06,860 finance database, a sales 111 00:03:06,860 --> 00:03:08,300 spreadsheet, and a web service, 112 00:03:08,300 --> 00:03:09,379 and leave you with a single 113 00:03:09,379 --> 00:03:10,139 clean table. 114 00:03:10,139 --> 00:03:12,979 One practical detail is worth knowing about if your data lives 115 00:03:12,979 --> 00:03:15,740 on a company server instead of in the cloud, you need something 116 00:03:15,740 --> 00:03:18,180 called an on premises gateway to carry it up. 117 00:03:18,180 --> 00:03:19,699 That gateway is a small bridge 118 00:03:19,699 --> 00:03:21,180 installed inside your network, 119 00:03:21,180 --> 00:03:23,060 and your IT team usually sets it 120 00:03:23,060 --> 00:03:24,400 up once and then forgets it 121 00:03:24,400 --> 00:03:25,020 exists. 122 00:03:25,020 --> 00:03:28,099 The best part for a beginner is that there's nothing to install 123 00:03:28,099 --> 00:03:29,300 and no server to babysit. 124 00:03:29,300 --> 00:03:30,939 You open your browser, build the 125 00:03:30,939 --> 00:03:32,580 steps once, and the cloud does 126 00:03:32,580 --> 00:03:33,259 the rest. 127 00:03:33,259 --> 00:03:34,500 Whether or not your laptop is 128 00:03:34,500 --> 00:03:37,039 even awake where your data 129 00:03:37,039 --> 00:03:38,300 actually sits. 130 00:03:38,300 --> 00:03:40,580 Beginners rarely ask one question, and it's the one that 131 00:03:40,580 --> 00:03:41,819 makes everything click. 132 00:03:41,819 --> 00:03:43,020 Once a data flow cleans your 133 00:03:43,020 --> 00:03:44,219 data, the clean table has to 134 00:03:44,219 --> 00:03:44,819 live somewhere. 135 00:03:44,819 --> 00:03:46,300 It goes to cloud storage, and 136 00:03:46,300 --> 00:03:47,659 for power BI data flows, that 137 00:03:47,659 --> 00:03:48,860 storage is Azure Data Lake 138 00:03:48,860 --> 00:03:49,819 Storage Gen2. 139 00:03:49,819 --> 00:03:51,460 Try not to let the name scare you. 140 00:03:51,460 --> 00:03:52,659 A data lake is a big cloud 141 00:03:52,659 --> 00:03:53,900 filing cabinet built to hold 142 00:03:53,900 --> 00:03:55,694 lots of data cheaply, and it's a 143 00:03:55,694 --> 00:03:56,930 very different animal from a 144 00:03:56,930 --> 00:03:57,490 spreadsheet. 145 00:03:57,490 --> 00:03:58,810 You don't open it in Excel and 146 00:03:58,810 --> 00:04:00,449 scroll around, you point tools 147 00:04:00,449 --> 00:04:01,969 at it, and they pull what they 148 00:04:01,969 --> 00:04:02,569 need. 149 00:04:02,569 --> 00:04:03,729 The cheapness is the point, and 150 00:04:03,729 --> 00:04:05,530 it's worth understanding why the 151 00:04:05,530 --> 00:04:07,289 data lands as plain files rather 152 00:04:07,289 --> 00:04:08,569 than inside an expensive 153 00:04:08,569 --> 00:04:10,409 database, which is exactly why a 154 00:04:10,409 --> 00:04:11,770 data lake can hold millions of 155 00:04:11,770 --> 00:04:12,849 rows without a big bill 156 00:04:12,849 --> 00:04:13,569 attached. 157 00:04:13,569 --> 00:04:16,209 That design choice is what makes this whole approach practical 158 00:04:16,209 --> 00:04:17,529 for ordinary teams. 159 00:04:17,529 --> 00:04:19,649 There are two ways the storage gets set up. 160 00:04:19,649 --> 00:04:22,290 Most of the time Microsoft manages it for you and you never 161 00:04:22,290 --> 00:04:23,490 think about it again. 162 00:04:23,490 --> 00:04:26,730 In larger organizations, someone points power BI at the company's 163 00:04:26,730 --> 00:04:29,810 own Azure Storage account, which gives the company more control 164 00:04:29,810 --> 00:04:32,889 and lets other Azure services read the very same files. 165 00:04:32,889 --> 00:04:34,370 The data is saved in a shared 166 00:04:34,370 --> 00:04:35,730 open layout called the Common 167 00:04:35,730 --> 00:04:36,649 Data Model. 168 00:04:36,649 --> 00:04:39,290 That long name hides something simple. 169 00:04:39,290 --> 00:04:42,930 The tables sit in a predictable structure, so a power BI report, 170 00:04:42,930 --> 00:04:46,769 a power app, and an automation can all read the same clean data 171 00:04:46,769 --> 00:04:49,290 without any of them rebuilding it from scratch. 172 00:04:49,290 --> 00:04:52,170 This is the part that surprises people most because the result 173 00:04:52,170 --> 00:04:53,810 lives in shared cloud storage. 174 00:04:53,810 --> 00:04:56,160 It isn't locked inside power BI at all. 175 00:04:56,160 --> 00:04:59,040 Your clean sales table can feed a report, an app, and an 176 00:04:59,040 --> 00:05:00,279 automated flow at the same time. 177 00:05:00,279 --> 00:05:03,639 Because you built it once and a whole set of tools can share it. 178 00:05:03,639 --> 00:05:06,160 The key thing to hold on to is that the data isn't sitting in 179 00:05:06,160 --> 00:05:07,240 your report file. 180 00:05:07,240 --> 00:05:08,480 It lives in the cloud, in a 181 00:05:08,480 --> 00:05:09,759 shared place, which is what 182 00:05:09,759 --> 00:05:11,040 makes reuse possible in the 183 00:05:11,040 --> 00:05:12,120 first place. 184 00:05:12,120 --> 00:05:14,600 How data flows fit with data sets and reports. 185 00:05:14,600 --> 00:05:16,879 Now let's clear up the confusion that trips up almost every 186 00:05:16,879 --> 00:05:19,519 beginner, which is how a data flow differs from a data set. 187 00:05:19,519 --> 00:05:21,199 The two words get used loosely, 188 00:05:21,199 --> 00:05:22,319 and that's where the muddle 189 00:05:22,319 --> 00:05:23,000 starts. 190 00:05:23,000 --> 00:05:25,600 An assembly line is the cleanest way to picture the split. 191 00:05:25,600 --> 00:05:27,360 Raw data enters at one end. 192 00:05:27,360 --> 00:05:30,199 The data flow does the cleaning and shaping the data set decides 193 00:05:30,199 --> 00:05:33,019 how the tables relate and what they calculate, and the report 194 00:05:33,019 --> 00:05:35,480 turns it all into something a human can read. 195 00:05:35,480 --> 00:05:37,360 Each station has exactly one job 196 00:05:37,360 --> 00:05:38,680 and that separation is the whole 197 00:05:38,680 --> 00:05:39,199 point. 198 00:05:39,199 --> 00:05:40,759 Consider a concrete version. 199 00:05:40,759 --> 00:05:43,399 Say your company sells through stores and three teams need the 200 00:05:43,399 --> 00:05:44,639 same sales numbers. 201 00:05:44,639 --> 00:05:46,120 Finance wants a monthly rollup. 202 00:05:46,120 --> 00:05:47,240 Store managers want daily 203 00:05:47,240 --> 00:05:48,439 totals, and leadership wants a 204 00:05:48,439 --> 00:05:48,839 dashboard. 205 00:05:48,839 --> 00:05:49,680 Without a data flow. 206 00:05:49,680 --> 00:05:52,040 Each team builds its own clean up from scratch, and the three 207 00:05:52,040 --> 00:05:54,600 versions drift apart within a month with a data flow. 208 00:05:54,600 --> 00:05:57,459 The cleanup happens once all three teams point their own data 209 00:05:57,459 --> 00:06:00,560 sets at the same clean sales table, and each one builds the 210 00:06:00,560 --> 00:06:02,079 visuals it needs on top of it. 211 00:06:02,079 --> 00:06:03,360 When the store names need fixing 212 00:06:03,360 --> 00:06:04,959 again, you fix them in one place 213 00:06:04,959 --> 00:06:06,319 and all three reports pick up 214 00:06:06,319 --> 00:06:07,120 the change. 215 00:06:07,120 --> 00:06:08,519 A second benefit hides in there. 216 00:06:08,519 --> 00:06:10,079 Each data set still refreshes on 217 00:06:10,079 --> 00:06:11,439 its own schedule, so the three 218 00:06:11,439 --> 00:06:13,319 reports stay independent, and a 219 00:06:13,319 --> 00:06:14,439 change to one of them can't 220 00:06:14,439 --> 00:06:15,519 accidentally break the other 221 00:06:15,519 --> 00:06:15,920 two. 222 00:06:15,920 --> 00:06:18,160 You get shared data without shared fragility. 223 00:06:18,160 --> 00:06:20,000 The payoff here is bigger than saving time. 224 00:06:20,000 --> 00:06:22,600 When everyone builds on the same clean table, they're all arguing 225 00:06:22,600 --> 00:06:23,720 about the same numbers. 226 00:06:23,720 --> 00:06:26,519 And that ends a lot of needless arguments in meetings. 227 00:06:26,519 --> 00:06:29,639 Your source system also gets touched once per refresh instead 228 00:06:29,639 --> 00:06:32,199 of once per report, which keeps the people who own that system 229 00:06:32,199 --> 00:06:33,160 on your side. 230 00:06:33,160 --> 00:06:35,839 One honest note is worth making before we move on. 231 00:06:35,839 --> 00:06:38,639 A data flow isn't a report, and it isn't a data set either. 232 00:06:38,639 --> 00:06:40,000 You still build a data set and a 233 00:06:40,000 --> 00:06:41,319 report on top of it, and the 234 00:06:41,319 --> 00:06:42,680 data flow simply handles the 235 00:06:42,680 --> 00:06:43,800 messy part that used to be 236 00:06:43,800 --> 00:06:44,720 copied everywhere. 237 00:06:44,720 --> 00:06:47,319 Once the idea makes sense, though, there's one practical 238 00:06:47,319 --> 00:06:49,560 catch that decides whether you can actually use it. 239 00:06:49,560 --> 00:06:51,910 And that catch is the license. 240 00:06:51,910 --> 00:06:54,670 the licence and capacity part in plain terms. 241 00:06:54,670 --> 00:06:55,310 Now for the part. 242 00:06:55,310 --> 00:06:56,949 Almost every beginner gets wrong 243 00:06:56,949 --> 00:06:58,189 and I did promise to come back 244 00:06:58,189 --> 00:06:58,790 to it. 245 00:06:58,790 --> 00:07:00,589 The good news comes first and it's real. 246 00:07:00,589 --> 00:07:02,629 A data flow doesn't need a special license of its own, 247 00:07:02,629 --> 00:07:04,870 because the ability to create and run one is bundled with 248 00:07:04,870 --> 00:07:07,930 licenses you might already have, like power BI power apps or 249 00:07:07,930 --> 00:07:09,910 dynamics three hundred sixty five Customer Insights. 250 00:07:09,910 --> 00:07:13,029 So if you're sitting on a power BI license, you can create a 251 00:07:13,029 --> 00:07:13,910 basic data flow. 252 00:07:13,910 --> 00:07:16,110 Today in power BI Dataflows work 253 00:07:16,110 --> 00:07:17,649 on pro on premium per user and 254 00:07:17,649 --> 00:07:18,350 on premium. 255 00:07:18,350 --> 00:07:19,589 That last part is where the fine 256 00:07:19,589 --> 00:07:20,870 print lives, because a handful 257 00:07:20,870 --> 00:07:22,189 of the best features are premium 258 00:07:22,189 --> 00:07:22,629 only. 259 00:07:22,629 --> 00:07:24,069 Let me name those features in 260 00:07:24,069 --> 00:07:25,629 plain words, since the official 261 00:07:25,629 --> 00:07:27,329 list reads like a contract 262 00:07:27,329 --> 00:07:28,910 linking data flows together so 263 00:07:28,910 --> 00:07:29,990 one builds on, another needs 264 00:07:29,990 --> 00:07:30,829 premium. 265 00:07:30,829 --> 00:07:31,689 So does doing the heavy 266 00:07:31,689 --> 00:07:32,970 calculations inside the data 267 00:07:32,970 --> 00:07:34,269 flow storage instead of in your 268 00:07:34,269 --> 00:07:35,029 report. 269 00:07:35,029 --> 00:07:37,750 The same goes for refreshing just the new rows and for the 270 00:07:37,750 --> 00:07:40,870 speed up engine that makes large jobs finish faster. 271 00:07:40,870 --> 00:07:43,029 A second layer catches people even harder. 272 00:07:43,029 --> 00:07:44,509 The newer fabric version of data 273 00:07:44,509 --> 00:07:46,550 flows needs a fabric capacity, a 274 00:07:46,550 --> 00:07:48,389 fabric trial, or a power BI 275 00:07:48,389 --> 00:07:49,750 premium capacity. 276 00:07:49,750 --> 00:07:51,220 A Plain Pro license won't open 277 00:07:51,220 --> 00:07:52,300 that door, which surprises 278 00:07:52,300 --> 00:07:53,660 anyone who assumes newer always 279 00:07:53,660 --> 00:07:54,300 means available. 280 00:07:54,300 --> 00:07:57,220 The word capacity scares people, so let's make it concrete. 281 00:07:57,220 --> 00:08:00,019 Capacity is just a pool of computing power that a company 282 00:08:00,019 --> 00:08:02,620 buys and shares across its fabric and power BI workloads. 283 00:08:02,620 --> 00:08:05,180 It's a bit like renting a bigger kitchen so more cooks can work 284 00:08:05,180 --> 00:08:08,500 at once, and the price reflects how big a kitchen you need. 285 00:08:08,500 --> 00:08:09,699 The honest version of the rule 286 00:08:09,699 --> 00:08:10,980 is simpler than the docs make it 287 00:08:10,980 --> 00:08:11,500 sound. 288 00:08:11,500 --> 00:08:14,779 A data flow on a basic license is real and useful, and plenty 289 00:08:14,779 --> 00:08:16,620 of teams run on exactly that. 290 00:08:16,620 --> 00:08:17,660 The moment you want the heavy 291 00:08:17,660 --> 00:08:19,040 lifting, you're in capacity, 292 00:08:19,040 --> 00:08:20,420 territory, and capacity is a 293 00:08:20,420 --> 00:08:21,620 conversation with whoever holds 294 00:08:21,620 --> 00:08:22,339 the budget. 295 00:08:22,339 --> 00:08:25,079 The encouraging part is that you don't need to own capacity to 296 00:08:25,079 --> 00:08:26,300 learn any of this. 297 00:08:26,300 --> 00:08:28,980 A free trial gives you a temporary capacity, which is 298 00:08:28,980 --> 00:08:31,660 plenty of time to build a first data flow and see how it feels 299 00:08:31,660 --> 00:08:34,059 before anyone spends a dollar. 300 00:08:34,059 --> 00:08:36,539 When a data flow is worth it and when it isn't. 301 00:08:36,539 --> 00:08:39,019 With the license picture clear, one question is left, and it's 302 00:08:39,019 --> 00:08:41,379 the one that decides whether you ever build one. 303 00:08:41,379 --> 00:08:42,860 For situations tell you a data 304 00:08:42,860 --> 00:08:44,259 flow will earn its place and 305 00:08:44,259 --> 00:08:45,419 they're easy to spot once you 306 00:08:45,419 --> 00:08:46,019 know them. 307 00:08:46,019 --> 00:08:47,299 The first is reuse. 308 00:08:47,299 --> 00:08:49,850 If more than one report needs the same clean table. 309 00:08:49,850 --> 00:08:52,330 Build it once in a dataflow and let them share it. 310 00:08:52,330 --> 00:08:53,850 The second is protecting your source. 311 00:08:53,850 --> 00:08:55,289 If a dozen reports each pull 312 00:08:55,289 --> 00:08:56,950 their own copy of the same rows, 313 00:08:56,950 --> 00:08:58,409 the source system feels every 314 00:08:58,409 --> 00:09:00,009 one of those pulls, and a single 315 00:09:00,009 --> 00:09:01,409 dataflow refresh replaces the 316 00:09:01,409 --> 00:09:02,049 lot. 317 00:09:02,049 --> 00:09:03,090 The third is agreement. 318 00:09:03,090 --> 00:09:04,250 When you want one central 319 00:09:04,250 --> 00:09:05,529 version of a table that everyone 320 00:09:05,529 --> 00:09:07,250 treats as the truth, a dataflow 321 00:09:07,250 --> 00:09:08,450 gives you one place to define 322 00:09:08,450 --> 00:09:08,730 it. 323 00:09:08,730 --> 00:09:10,169 The fourth is people because a 324 00:09:10,169 --> 00:09:10,970 dataflow is made for 325 00:09:10,970 --> 00:09:11,450 self-service. 326 00:09:11,450 --> 00:09:14,049 So someone comfortable in Power Query but not a professional 327 00:09:14,049 --> 00:09:15,490 engineer can build and own it. 328 00:09:15,490 --> 00:09:18,529 That last point matters since it lets the person closest to the 329 00:09:18,529 --> 00:09:20,250 business shape the data. 330 00:09:20,250 --> 00:09:21,250 Now consider the other side, 331 00:09:21,250 --> 00:09:22,570 because a dataflow isn't always 332 00:09:22,570 --> 00:09:23,049 the answer. 333 00:09:23,049 --> 00:09:26,049 If a single person uses a single report, adding one gives you an 334 00:09:26,049 --> 00:09:28,450 extra layer and an extra schedule for no real gain. 335 00:09:28,450 --> 00:09:31,049 Just clean the data inside the report and move on. 336 00:09:31,049 --> 00:09:32,169 A middle case is worth calling 337 00:09:32,169 --> 00:09:33,409 out because it's where teams get 338 00:09:33,409 --> 00:09:33,929 stuck. 339 00:09:33,929 --> 00:09:36,210 You might have two reports that share a couple of columns, but 340 00:09:36,210 --> 00:09:37,090 not much else. 341 00:09:37,090 --> 00:09:38,649 Reuse only pays off when the 342 00:09:38,649 --> 00:09:39,850 shared part is the expensive 343 00:09:39,850 --> 00:09:40,129 part. 344 00:09:40,129 --> 00:09:42,529 The heavy cleaning, the slow join, the messy source. 345 00:09:42,529 --> 00:09:45,649 If the shared work is trivial, a dataflow just adds a hop. 346 00:09:45,649 --> 00:09:46,850 A simple test before you build 347 00:09:46,850 --> 00:09:48,169 one is to ask whether two people 348 00:09:48,169 --> 00:09:49,409 would be annoyed if the clean 349 00:09:49,409 --> 00:09:50,090 table changed. 350 00:09:50,090 --> 00:09:52,950 If the answer is yes, the table really is shared and the data 351 00:09:52,950 --> 00:09:54,330 flow is probably a good fit. 352 00:09:54,330 --> 00:09:55,370 If you're really building an 353 00:09:55,370 --> 00:09:57,009 enterprise data warehouse with 354 00:09:57,009 --> 00:09:58,370 deep history tracking and heavy 355 00:09:58,370 --> 00:10:00,090 audit requirements, a data flow 356 00:10:00,090 --> 00:10:01,129 can carry more than people 357 00:10:01,129 --> 00:10:02,850 expect, but a proper warehouse 358 00:10:02,850 --> 00:10:04,230 is usually the better home for 359 00:10:04,230 --> 00:10:05,129 that job. 360 00:10:05,129 --> 00:10:08,090 And if you only need the data to look right on one screen one 361 00:10:08,090 --> 00:10:10,009 time, a data flow is overkill. 362 00:10:10,009 --> 00:10:11,529 One fair trade off is worth naming. 363 00:10:11,529 --> 00:10:14,289 Every data flow you add is another thing that can fail and 364 00:10:14,289 --> 00:10:16,250 another refresh schedule someone has to watch. 365 00:10:16,250 --> 00:10:18,450 The reuse has to be worth that overhead. 366 00:10:18,450 --> 00:10:21,090 When three reports share one clean table, it clearly is. 367 00:10:21,090 --> 00:10:24,610 And when nobody shares anything, it clearly isn't the traps that 368 00:10:24,610 --> 00:10:25,690 catch people out. 369 00:10:25,690 --> 00:10:27,049 If you decide a data flow is 370 00:10:27,049 --> 00:10:28,250 worth it, a few traps are 371 00:10:28,250 --> 00:10:29,330 waiting, and knowing them in 372 00:10:29,330 --> 00:10:30,370 advance saves you a bad 373 00:10:30,370 --> 00:10:31,049 afternoon. 374 00:10:31,049 --> 00:10:32,769 The first one hits beginners immediately. 375 00:10:32,769 --> 00:10:33,929 Data flows aren't available in 376 00:10:33,929 --> 00:10:35,330 the personal my workspace area, 377 00:10:35,330 --> 00:10:36,409 so they have to live in a shared 378 00:10:36,409 --> 00:10:37,009 workspace. 379 00:10:37,009 --> 00:10:38,210 Hunt for the button in your own 380 00:10:38,210 --> 00:10:40,009 private sandbox and you won't 381 00:10:40,009 --> 00:10:41,370 find it, which makes the feature 382 00:10:41,370 --> 00:10:41,970 look missing. 383 00:10:41,970 --> 00:10:43,649 The second trap is about ownership. 384 00:10:43,649 --> 00:10:46,365 Only the owner of a data flow can edit it, so if that person 385 00:10:46,365 --> 00:10:49,080 changes roles or leaves the company, editing stops until 386 00:10:49,080 --> 00:10:50,559 someone takes ownership. 387 00:10:50,559 --> 00:10:51,919 Taking ownership can also break 388 00:10:51,919 --> 00:10:53,080 the saved connections to your 389 00:10:53,080 --> 00:10:54,679 data sources, which is fixable, 390 00:10:54,679 --> 00:10:56,100 but fiddly, and it tends to 391 00:10:56,100 --> 00:10:57,379 surface at the worst possible 392 00:10:57,379 --> 00:10:58,080 time. 393 00:10:58,080 --> 00:11:00,000 The third trap is the one that actually hurts. 394 00:11:00,000 --> 00:11:02,480 Deleted data flows can't be recovered, and there's no 395 00:11:02,480 --> 00:11:04,200 recycle bin and no undo. 396 00:11:04,200 --> 00:11:07,120 You can back up the definition as a file and restore from that. 397 00:11:07,120 --> 00:11:10,200 But if you never set the backup up, a wrong click is permanent. 398 00:11:10,200 --> 00:11:11,480 The fourth trap is a refresh 399 00:11:11,480 --> 00:11:12,879 surprise if you change the shape 400 00:11:12,879 --> 00:11:14,679 of the data, like adding or 401 00:11:14,679 --> 00:11:16,320 renaming a column, the stored 402 00:11:16,320 --> 00:11:17,720 data gets wiped until the next 403 00:11:17,720 --> 00:11:18,679 refresh runs. 404 00:11:18,679 --> 00:11:19,759 The same thing happens if you 405 00:11:19,759 --> 00:11:21,240 switch a data flow between 406 00:11:21,240 --> 00:11:22,919 refreshing only new rows and 407 00:11:22,919 --> 00:11:23,799 refreshing everything. 408 00:11:23,799 --> 00:11:26,799 Both moves drop the data and neither one warns you loudly. 409 00:11:26,799 --> 00:11:28,120 The fifth trap is about time 410 00:11:28,120 --> 00:11:29,360 limits, and they depend on your 411 00:11:29,360 --> 00:11:31,240 capacity on a shared non-premium 412 00:11:31,240 --> 00:11:32,080 capacity. 413 00:11:32,080 --> 00:11:33,419 A single table gets two hours to 414 00:11:33,419 --> 00:11:34,799 refresh and a whole data flow 415 00:11:34,799 --> 00:11:35,440 gets three. 416 00:11:35,440 --> 00:11:38,120 On premium, a data flow gets twenty four hours, which is a 417 00:11:38,120 --> 00:11:39,440 very different world. 418 00:11:39,440 --> 00:11:41,320 A further rule says refreshing 419 00:11:41,320 --> 00:11:42,799 more than ten data flows across 420 00:11:42,799 --> 00:11:44,080 different workspaces needs 421 00:11:44,080 --> 00:11:44,639 premium. 422 00:11:44,639 --> 00:11:47,409 The six strap is quieter and it catches people who change a data 423 00:11:47,409 --> 00:11:49,549 flow after reports already depend on it. 424 00:11:49,549 --> 00:11:50,950 If you rename a column or add 425 00:11:50,950 --> 00:11:52,429 one, the reports built on top 426 00:11:52,429 --> 00:11:53,470 don't see the change on their 427 00:11:53,470 --> 00:11:53,990 own. 428 00:11:53,990 --> 00:11:56,669 You have to open the report in power BI desktop, refresh it 429 00:11:56,669 --> 00:11:57,909 there and republish it. 430 00:11:57,909 --> 00:12:00,230 Or the refresh fails and the report looks broken. 431 00:12:00,230 --> 00:12:03,029 One more trap catches people on the per user premium license. 432 00:12:03,029 --> 00:12:06,190 Specifically, data refreshed in that environment isn't visible 433 00:12:06,190 --> 00:12:08,789 to anyone without the same license, and it gets cleared 434 00:12:08,789 --> 00:12:09,590 when it moves out. 435 00:12:09,590 --> 00:12:11,149 If your team is mixed, that one 436 00:12:11,149 --> 00:12:12,309 surprises everybody the first 437 00:12:12,309 --> 00:12:12,590 time. 438 00:12:12,590 --> 00:12:14,669 None of these are reasons to avoid data flows. 439 00:12:14,669 --> 00:12:15,789 There are reasons to set one up 440 00:12:15,789 --> 00:12:17,269 with a little care, a backup 441 00:12:17,269 --> 00:12:18,750 file, and a plan for who owns 442 00:12:18,750 --> 00:12:18,909 it. 443 00:12:18,909 --> 00:12:20,830 When people move on the two 444 00:12:20,830 --> 00:12:22,409 generations and which one to 445 00:12:22,409 --> 00:12:22,870 learn. 446 00:12:22,870 --> 00:12:25,389 One more thing is worth knowing and it changes what you should 447 00:12:25,389 --> 00:12:26,549 spend your time learning. 448 00:12:26,549 --> 00:12:27,830 Microsoft now has two versions 449 00:12:27,830 --> 00:12:29,149 of data flows living side by 450 00:12:29,149 --> 00:12:30,590 side, and they aren't the same 451 00:12:30,590 --> 00:12:31,070 product. 452 00:12:31,070 --> 00:12:34,070 The original is now called Dataflow Gen one, and Microsoft 453 00:12:34,070 --> 00:12:35,830 has labeled it legacy. 454 00:12:35,830 --> 00:12:37,789 Legacy is a specific word in Microsoft's world. 455 00:12:37,789 --> 00:12:40,629 It means the feature still works and still gets supported, but it 456 00:12:40,629 --> 00:12:43,190 won't receive new features for customers with fabric access. 457 00:12:43,190 --> 00:12:44,500 Microsoft points to the newer 458 00:12:44,500 --> 00:12:46,259 one Dataflow Gen two as the 459 00:12:46,259 --> 00:12:47,220 recommended path. 460 00:12:47,220 --> 00:12:48,580 Gen two adds some real things. 461 00:12:48,580 --> 00:12:51,019 It can send your clean data to many more places, including a 462 00:12:51,019 --> 00:12:53,779 fabric lakehouse, a fabric warehouse, an Azure SQL 463 00:12:53,779 --> 00:12:55,820 database, or even snowflake. 464 00:12:55,820 --> 00:12:57,580 It saves your work automatically as you go. 465 00:12:57,580 --> 00:13:00,059 It plugs into pipelines, which are Microsoft's way of chaining 466 00:13:00,059 --> 00:13:02,860 steps together, and it comes with a copilot assistant that 467 00:13:02,860 --> 00:13:05,580 can apply transformations from a plain language prompt. 468 00:13:05,580 --> 00:13:06,860 The good news for anyone already 469 00:13:06,860 --> 00:13:08,100 invested in Gen one is that 470 00:13:08,100 --> 00:13:09,340 moving isn't a rebuild from 471 00:13:09,340 --> 00:13:09,940 nothing. 472 00:13:09,940 --> 00:13:13,100 You can export your queries and import them into Gen two, or 473 00:13:13,100 --> 00:13:14,700 copy them across in Power Query. 474 00:13:14,700 --> 00:13:16,899 So the work you've already done carries over. 475 00:13:16,899 --> 00:13:18,620 It's a migration, not a fresh start. 476 00:13:18,620 --> 00:13:20,740 There's a catch that most summaries leave out. 477 00:13:20,740 --> 00:13:23,100 Gen two isn't a strict upgrade of everything in Gen one. 478 00:13:23,100 --> 00:13:25,919 One thing Gen one can do that Gen two can't is connect live to 479 00:13:25,919 --> 00:13:29,299 a data flow through the dataflow connector without importing a 480 00:13:29,299 --> 00:13:30,299 copy of the data. 481 00:13:30,299 --> 00:13:31,460 If that live connection is 482 00:13:31,460 --> 00:13:32,779 central to how your reports work 483 00:13:32,779 --> 00:13:34,600 today, then swapping to Gen two 484 00:13:34,600 --> 00:13:35,779 isn't straightforward. 485 00:13:35,779 --> 00:13:38,820 That leaves the practical question of what a beginner 486 00:13:38,820 --> 00:13:40,740 should actually learn. 487 00:13:40,740 --> 00:13:42,820 If your team is on fabric or premium. 488 00:13:42,820 --> 00:13:44,179 Learn Gen two because that's 489 00:13:44,179 --> 00:13:45,379 where Microsoft is putting its 490 00:13:45,379 --> 00:13:45,860 effort. 491 00:13:45,860 --> 00:13:48,100 If you're on a plane Pro license, Gen one is what you 492 00:13:48,100 --> 00:13:50,899 have, and it still works fine for the core job of cleaning 493 00:13:50,899 --> 00:13:52,259 data once and reusing it. 494 00:13:52,259 --> 00:13:53,460 If you're honestly not sure 495 00:13:53,460 --> 00:13:55,080 which one your organization has, 496 00:13:55,080 --> 00:13:56,460 that's a fair question to bring 497 00:13:56,460 --> 00:13:57,960 to whoever manages your power BI 498 00:13:57,960 --> 00:13:58,740 licenses. 499 00:13:58,740 --> 00:14:01,240 The answer decides what you should practice on this week, 500 00:14:01,240 --> 00:14:02,179 what to take away. 501 00:14:02,179 --> 00:14:03,460 And one question for you. 502 00:14:03,460 --> 00:14:04,940 So let's pull the threads together. 503 00:14:04,940 --> 00:14:07,580 A dataflow cleans and shapes your data once in the cloud, so 504 00:14:07,580 --> 00:14:10,019 every report can reuse the same clean table instead of 505 00:14:10,019 --> 00:14:11,059 rebuilding it from scratch. 506 00:14:11,059 --> 00:14:13,179 It sits between your raw source and your report. 507 00:14:13,179 --> 00:14:15,820 It can feed apps and automations as well as reports. 508 00:14:15,820 --> 00:14:17,139 And it's real limits are mostly 509 00:14:17,139 --> 00:14:18,539 about licenses, capacity, and 510 00:14:18,539 --> 00:14:19,100 ownership. 511 00:14:19,100 --> 00:14:21,620 Microsoft is also clearly steering new work toward the 512 00:14:21,620 --> 00:14:23,019 newer fabric generation. 513 00:14:23,019 --> 00:14:24,220 Here's my question for you. 514 00:14:24,220 --> 00:14:25,379 Think about the reporting your 515 00:14:25,379 --> 00:14:27,179 team does today, and count how 516 00:14:27,179 --> 00:14:28,820 many times the same cleanup gets 517 00:14:28,820 --> 00:14:29,860 done by different people in 518 00:14:29,860 --> 00:14:30,740 different files? 519 00:14:30,740 --> 00:14:31,899 If the answer is more than once, 520 00:14:31,899 --> 00:14:33,340 you already know exactly where a 521 00:14:33,340 --> 00:14:34,740 data flow would help if this 522 00:14:34,740 --> 00:14:35,700 made the topic feel less 523 00:14:35,700 --> 00:14:36,580 intimidating. 524 00:14:36,580 --> 00:14:40,620 Subscribe to M365 FM to master the tools and the mindset 525 00:14:40,620 --> 00:14:42,220 shaping the future of work.