1 00:00:00,000 --> 00:00:07,799 I gave this talk for the first time a 2 00:00:07,799 --> 00:00:10,860 little over a year ago and I guess what 3 00:00:10,860 --> 00:00:12,059 I think I'm trying to show is that you 4 00:00:12,059 --> 00:00:13,679 know the better you get with very tool 5 00:00:13,679 --> 00:00:14,700 you know the more you're going to be 6 00:00:14,700 --> 00:00:16,859 able to take on these giant problems 7 00:00:16,859 --> 00:00:23,670 that you face inside here so also this 8 00:00:23,670 --> 00:00:25,109 area that we're about to look at you 9 00:00:25,109 --> 00:00:27,510 know M programming there isn't a lot of 10 00:00:27,510 --> 00:00:30,000 resources out there you know if you want 11 00:00:30,000 --> 00:00:31,980 to learn decks you know first of all 12 00:00:31,980 --> 00:00:34,020 there's the two Italian guys Margaret 13 00:00:34,020 --> 00:00:35,790 Rosa Alberto Ferrari you read their 14 00:00:35,790 --> 00:00:36,989 books you know everything about it and 15 00:00:36,989 --> 00:00:38,640 there's tons of other resources on decks 16 00:00:38,640 --> 00:00:41,250 you get to em and you're gonna find 17 00:00:41,250 --> 00:00:44,370 there there's seven blog entries you 18 00:00:44,370 --> 00:00:45,750 know by Chris Webb and that's pretty 19 00:00:45,750 --> 00:00:51,539 much it but I learned so much from these 20 00:00:51,539 --> 00:00:53,129 three guys right here you know so Matt 21 00:00:53,129 --> 00:00:55,410 Mason works on the team in Microsoft 22 00:00:55,410 --> 00:00:57,210 with Howard brewery and he's one of the 23 00:00:57,210 --> 00:00:58,710 ones that's kind of driving things 24 00:00:58,710 --> 00:01:00,870 forward and evangelizing there's a guy 25 00:01:00,870 --> 00:01:02,940 named Chris Webb out of the UK you know 26 00:01:02,940 --> 00:01:04,860 who is what I think of you is the 27 00:01:04,860 --> 00:01:07,619 leading third-party expert in power 28 00:01:07,619 --> 00:01:09,540 purring and working with them and then 29 00:01:09,540 --> 00:01:11,189 another fellow from Down Under 30 00:01:11,189 --> 00:01:13,740 Reza read okay so I just want to call 31 00:01:13,740 --> 00:01:16,830 out to them nothing all right 32 00:01:16,830 --> 00:01:22,259 okay can we got him in an EM suit okay 33 00:01:22,259 --> 00:01:24,659 also everything I'm doing here you know 34 00:01:24,659 --> 00:01:28,680 there is a github repository you know if 35 00:01:28,680 --> 00:01:29,880 you go there you'll be able to download 36 00:01:29,880 --> 00:01:31,259 these slides you'll be able to download 37 00:01:31,259 --> 00:01:35,340 the samples inside here okay so we're 38 00:01:35,340 --> 00:01:36,840 gonna cover first of all just the power 39 00:01:36,840 --> 00:01:38,790 query matchup engine talking about you 40 00:01:38,790 --> 00:01:41,430 know what that is and one of the things 41 00:01:41,430 --> 00:01:42,689 that we're gonna look at is that when 42 00:01:42,689 --> 00:01:44,399 you use power query you know quite often 43 00:01:44,399 --> 00:01:47,040 we just stay within the user experience 44 00:01:47,040 --> 00:01:48,600 that we click here and click there and 45 00:01:48,600 --> 00:01:49,380 we kind of create these 46 00:01:49,380 --> 00:01:51,240 very transforms you know but there is 47 00:01:51,240 --> 00:01:53,100 also the ability you know to go in and 48 00:01:53,100 --> 00:01:55,079 work directly with em code and so one of 49 00:01:55,079 --> 00:01:56,280 the things I want to bring out you know 50 00:01:56,280 --> 00:01:57,570 is that you know there are certain 51 00:01:57,570 --> 00:01:59,189 advantages and certainly there's a lot 52 00:01:59,189 --> 00:02:01,049 more that you can do if you work 53 00:02:01,049 --> 00:02:02,850 directly with the EM code so we're gonna 54 00:02:02,850 --> 00:02:04,259 look at them just as a new programming 55 00:02:04,259 --> 00:02:06,659 language I know not all of you are 56 00:02:06,659 --> 00:02:10,530 developers but it's got to start out 57 00:02:10,530 --> 00:02:12,870 with you know this tool and if I go 58 00:02:12,870 --> 00:02:18,080 ahead and I bring up power bi desktop so 59 00:02:18,080 --> 00:02:20,450 how many people use power bi desktop 60 00:02:20,450 --> 00:02:23,040 okay just kind of seen hoping everyone 61 00:02:23,040 --> 00:02:25,680 in the room raises their hand here you 62 00:02:25,680 --> 00:02:26,730 know let's say I want to do something 63 00:02:26,730 --> 00:02:29,459 you know fairly straight ahead you know 64 00:02:29,459 --> 00:02:32,489 so I bring up this new project now one 65 00:02:32,489 --> 00:02:35,250 of the things I have here is this CSV 66 00:02:35,250 --> 00:02:37,680 file so I'm going to use NFL teams in 67 00:02:37,680 --> 00:02:40,290 this example and so one neat thing about 68 00:02:40,290 --> 00:02:44,970 NFL teams is they all have a webpage 69 00:02:44,970 --> 00:02:47,790 that has a roster you know so let's say 70 00:02:47,790 --> 00:02:53,329 that I get inside here and I grab this 71 00:02:53,329 --> 00:02:56,579 so if I just bring up a browser you know 72 00:02:56,579 --> 00:02:58,500 and I go through this particular page 73 00:02:58,500 --> 00:03:00,209 right here no we should see there is a 74 00:03:00,209 --> 00:03:02,340 page and sooner or later there's going 75 00:03:02,340 --> 00:03:04,889 to be an HTML table you know but what we 76 00:03:04,889 --> 00:03:06,180 want to do is we don't care about the UI 77 00:03:06,180 --> 00:03:07,709 we just want to go and scrape the data 78 00:03:07,709 --> 00:03:17,730 from this HTML table inside there I tend 79 00:03:17,730 --> 00:03:19,410 to like teams that are at the bottom it 80 00:03:19,410 --> 00:03:21,030 doesn't matter you bring up it's gonna 81 00:03:21,030 --> 00:03:23,910 be controversy okay oh Portland doesn't 82 00:03:23,910 --> 00:03:25,920 like football I forgot okay should have 83 00:03:25,920 --> 00:03:27,030 something I should have had soccer 84 00:03:27,030 --> 00:03:29,730 examples inside here but let's say we go 85 00:03:29,730 --> 00:03:32,280 to web yeah that's it now I go through 86 00:03:32,280 --> 00:03:34,769 here and I enter this inside and so 87 00:03:34,769 --> 00:03:36,090 there's this neat experience you know 88 00:03:36,090 --> 00:03:37,739 where you're behind the scenes they're 89 00:03:37,739 --> 00:03:39,389 doing all kinds of things like looking 90 00:03:39,389 --> 00:03:41,790 at that particular page and determining 91 00:03:41,790 --> 00:03:44,340 you know what are the HTML tables and 92 00:03:44,340 --> 00:03:46,470 they're gonna bring up this navigator so 93 00:03:46,470 --> 00:03:48,299 the one that I want is going to be the 94 00:03:48,299 --> 00:03:50,190 first HTML table it's the one that's 95 00:03:50,190 --> 00:03:52,560 named active and so we're gonna go ahead 96 00:03:52,560 --> 00:03:56,639 and say edit inside here power bi 97 00:03:56,639 --> 00:03:57,840 desktop also has this kind of 98 00:03:57,840 --> 00:03:59,609 unfortunate bug to the query editor is 99 00:03:59,609 --> 00:04:00,340 not there it 100 00:04:00,340 --> 00:04:01,599 it just doesn't become the active window 101 00:04:01,599 --> 00:04:04,030 you know so we need to go through but 102 00:04:04,030 --> 00:04:05,800 now that we have this inside here you 103 00:04:05,800 --> 00:04:07,330 know what are some of the things you 104 00:04:07,330 --> 00:04:09,849 know that I might want to do you know so 105 00:04:09,849 --> 00:04:11,200 I might want to go through and start you 106 00:04:11,200 --> 00:04:14,140 know changing some of the columns inside 107 00:04:14,140 --> 00:04:19,000 here you know here one of our main jobs 108 00:04:19,000 --> 00:04:21,130 that we have when we create queries is 109 00:04:21,130 --> 00:04:23,199 to clean the data and if you have data 110 00:04:23,199 --> 00:04:25,270 that's supposed to be numeric and you 111 00:04:25,270 --> 00:04:26,800 don't convert it and it's text-based 112 00:04:26,800 --> 00:04:28,300 you know all of a sudden you can't sums 113 00:04:28,300 --> 00:04:30,160 you can't average you know all you can 114 00:04:30,160 --> 00:04:32,110 really do is count so want to remain job 115 00:04:32,110 --> 00:04:33,970 just to make sure that these things are 116 00:04:33,970 --> 00:04:36,280 all being converted yeah so if I have 117 00:04:36,280 --> 00:04:39,160 weight inside here let's go through here 118 00:04:39,160 --> 00:04:43,450 and we'll put in weight age looks like 119 00:04:43,450 --> 00:04:45,820 it's already fine if I look down here at 120 00:04:45,820 --> 00:04:48,669 experience you know this is the number 121 00:04:48,669 --> 00:04:51,820 of years that a player has been in the 122 00:04:51,820 --> 00:04:55,180 league but instead of giving rookies 0 123 00:04:55,180 --> 00:04:56,770 they give them an R so this could 124 00:04:56,770 --> 00:04:58,570 convert into a number you know so the 125 00:04:58,570 --> 00:04:59,919 tool is gonna let me do something such 126 00:04:59,919 --> 00:05:03,250 as do a replace value you know find 127 00:05:03,250 --> 00:05:06,910 everything that's is an r and replace it 128 00:05:06,910 --> 00:05:10,030 with a zero and now I can go through 129 00:05:10,030 --> 00:05:12,220 here and I can go ahead and convert that 130 00:05:12,220 --> 00:05:13,570 to a whole number you know and now I've 131 00:05:13,570 --> 00:05:15,970 done like conversion inside there okay 132 00:05:15,970 --> 00:05:17,770 now let's go back to the slides for a 133 00:05:17,770 --> 00:05:20,770 second you know so we have this tool and 134 00:05:20,770 --> 00:05:22,389 it's all about going to different data 135 00:05:22,389 --> 00:05:25,060 sources you know be its equal you know 136 00:05:25,060 --> 00:05:29,320 via Big Data it's in CSV files or Web 137 00:05:29,320 --> 00:05:31,270 Services you know I'm able to connect 138 00:05:31,270 --> 00:05:33,940 the multiple data sources and there's 139 00:05:33,940 --> 00:05:36,880 this engine now so this is index this is 140 00:05:36,880 --> 00:05:38,169 the query engine which is called the 141 00:05:38,169 --> 00:05:41,410 mashup engine inside there and that's 142 00:05:41,410 --> 00:05:43,599 what basically runs my queries and pulls 143 00:05:43,599 --> 00:05:45,400 the data back and dumps it inside the 144 00:05:45,400 --> 00:05:49,210 dataset now when I wrote this 145 00:05:49,210 --> 00:05:52,300 m-code was just good to create datasets 146 00:05:52,300 --> 00:05:54,190 Microsoft has now introduced data flows 147 00:05:54,190 --> 00:05:56,770 which give us yet another place where 148 00:05:56,770 --> 00:05:58,150 everything we're gonna learn tonight you 149 00:05:58,150 --> 00:06:01,000 know we can use the idea of a data flow 150 00:06:01,000 --> 00:06:03,970 is that you want to stage the data and 151 00:06:03,970 --> 00:06:06,250 you might have a lot more data than you 152 00:06:06,250 --> 00:06:07,960 can fit the power bi data set you know 153 00:06:07,960 --> 00:06:10,210 it might be 100 gigs worth of data you 154 00:06:10,210 --> 00:06:11,770 can ever load that in but we just want 155 00:06:11,770 --> 00:06:13,210 to pull the data down cleanse it 156 00:06:13,210 --> 00:06:15,400 transform it and then kind of put it in 157 00:06:15,400 --> 00:06:18,340 Azure storage you know where I can 158 00:06:18,340 --> 00:06:20,320 basically ass or as much data as I want 159 00:06:20,320 --> 00:06:23,080 because data is cheap no so whether 160 00:06:23,080 --> 00:06:24,730 you're pushing data into a data flow or 161 00:06:24,730 --> 00:06:27,310 into a data set in power bi you know the 162 00:06:27,310 --> 00:06:28,840 query tool in the mashup engine you know 163 00:06:28,840 --> 00:06:30,540 it's going to be your friend 164 00:06:30,540 --> 00:06:33,670 so what many people do is they use this 165 00:06:33,670 --> 00:06:36,580 query editor window and you know you can 166 00:06:36,580 --> 00:06:38,350 see that as I was creating this query 167 00:06:38,350 --> 00:06:40,200 right here you know we started with 168 00:06:40,200 --> 00:06:42,550 there was this one particular page that 169 00:06:42,550 --> 00:06:44,470 had a bunch of tables you know and as I 170 00:06:44,470 --> 00:06:46,480 picked the table and then I change 171 00:06:46,480 --> 00:06:48,010 different types and I changed things 172 00:06:48,010 --> 00:06:50,110 inside there you know it was able to go 173 00:06:50,110 --> 00:06:55,300 through and do the work now these 174 00:06:55,300 --> 00:06:58,960 applied steps are things where there's 175 00:06:58,960 --> 00:07:01,540 an instruction that when I click and I 176 00:07:01,540 --> 00:07:03,760 create a new step there's a piece of 177 00:07:03,760 --> 00:07:07,000 code you know that's written and so you 178 00:07:07,000 --> 00:07:09,280 know every one of these steps you know 179 00:07:09,280 --> 00:07:11,830 has a piece of code that's written 180 00:07:11,830 --> 00:07:14,640 behind in a language which is called M 181 00:07:14,640 --> 00:07:16,930 my understanding is they basically 182 00:07:16,930 --> 00:07:18,820 created a language called M as kind of 183 00:07:18,820 --> 00:07:20,770 the cool code name and then no one had 184 00:07:20,770 --> 00:07:22,540 any ideas how to create the real name so 185 00:07:22,540 --> 00:07:24,670 M just kind of stuck and I guess there's 186 00:07:24,670 --> 00:07:26,260 other languages like are with one light 187 00:07:26,260 --> 00:07:29,950 or so also it's called the power query 188 00:07:29,950 --> 00:07:31,420 formula language you know if you look at 189 00:07:31,420 --> 00:07:35,140 the specification for this now if I go 190 00:07:35,140 --> 00:07:37,890 back here in the tool and I go to view 191 00:07:37,890 --> 00:07:40,510 note that I can bring up the formula bar 192 00:07:40,510 --> 00:07:43,030 and see the M code or make it disappear 193 00:07:43,030 --> 00:07:45,580 you know so there are two kinds of them 194 00:07:45,580 --> 00:07:47,920 those that like M code those that want 195 00:07:47,920 --> 00:07:49,210 to ignore their family and friends if 196 00:07:49,210 --> 00:07:50,500 there's nothing I'd rather do than write 197 00:07:50,500 --> 00:07:52,180 code and then the other extreme is I 198 00:07:52,180 --> 00:07:53,440 don't want to see the uncooked if my 199 00:07:53,440 --> 00:07:55,409 head will explode so 200 00:07:55,409 --> 00:07:56,429 the tool is really made for both 201 00:07:56,429 --> 00:07:59,099 audiences so certainly there's gonna be 202 00:07:59,099 --> 00:08:00,389 lots of people that just want to hide 203 00:08:00,389 --> 00:08:01,979 this and just use the tool and click 204 00:08:01,979 --> 00:08:04,199 click click yeah but what we can see is 205 00:08:04,199 --> 00:08:08,069 that for every single step here that you 206 00:08:08,069 --> 00:08:10,979 know the tool generated M code and if I 207 00:08:10,979 --> 00:08:12,659 go to the advanced editor and I bring 208 00:08:12,659 --> 00:08:15,119 this up here to the code that's actually 209 00:08:15,119 --> 00:08:17,099 stored and when they write your furry 210 00:08:17,099 --> 00:08:18,779 this is the code that goes against that 211 00:08:18,779 --> 00:08:22,649 mashup engine and executes ok so what 212 00:08:22,649 --> 00:08:23,759 we're gonna do is kind of just look at 213 00:08:23,759 --> 00:08:25,949 the language and talk about you know why 214 00:08:25,949 --> 00:08:29,099 we might want to get more involved now 215 00:08:29,099 --> 00:08:31,349 the first time you might come into 216 00:08:31,349 --> 00:08:34,649 contact with this is that you want to 217 00:08:34,649 --> 00:08:37,078 use something like a custom column now 218 00:08:37,078 --> 00:08:39,568 so here's an example of a custom column 219 00:08:39,568 --> 00:08:42,120 so you kind of saw that I was able to 220 00:08:42,120 --> 00:08:45,839 take you know things like experience and 221 00:08:45,839 --> 00:08:48,060 just using the tool I was able to 222 00:08:48,060 --> 00:08:49,769 replace the arm with a zero and then 223 00:08:49,769 --> 00:08:52,740 convert okay great how about this other 224 00:08:52,740 --> 00:08:54,509 thing down here you know where we have 225 00:08:54,509 --> 00:08:57,540 the height five foot ten six foot four 226 00:08:57,540 --> 00:08:59,300 how the heck am I going to average that 227 00:08:59,300 --> 00:09:02,100 so let's kind of start by I'm gonna 228 00:09:02,100 --> 00:09:04,680 right-click and I'm gonna basically say 229 00:09:04,680 --> 00:09:07,139 let's go ahead and split you know by a 230 00:09:07,139 --> 00:09:10,769 delimiter and a year ago power bi 231 00:09:10,769 --> 00:09:13,079 desktop would be smart enough to see in 232 00:09:13,079 --> 00:09:14,639 the data there was life and now it is 233 00:09:14,639 --> 00:09:16,259 you know so I don't have to do much I'll 234 00:09:16,259 --> 00:09:17,220 just go ahead and do it okay 235 00:09:17,220 --> 00:09:19,709 you know but now I have you know one 236 00:09:19,709 --> 00:09:22,920 column for B and then I have you know 237 00:09:22,920 --> 00:09:28,439 one column for inches inside here and so 238 00:09:28,439 --> 00:09:30,300 now what I want to do is I want to 239 00:09:30,300 --> 00:09:32,790 create a custom column and I want to be 240 00:09:32,790 --> 00:09:34,290 able to you know fine you know what is 241 00:09:34,290 --> 00:09:36,120 the height of inches so we go to add 242 00:09:36,120 --> 00:09:41,490 column we now go to custom column and so 243 00:09:41,490 --> 00:09:43,170 what we'll do is we'll create something 244 00:09:43,170 --> 00:09:45,600 you know which is called height and now 245 00:09:45,600 --> 00:09:48,569 I've got a rhytm code luckily the M code 246 00:09:48,569 --> 00:09:49,500 here is not going to be all that 247 00:09:49,500 --> 00:09:51,269 challenging you know because I'll open 248 00:09:51,269 --> 00:09:53,730 up a parenthesis and then I'll say let's 249 00:09:53,730 --> 00:09:56,279 take the number of feet and asterisks 250 00:09:56,279 --> 00:09:57,029 times 251 00:09:57,029 --> 00:10:01,319 twelve plus the number of inches and 252 00:10:01,319 --> 00:10:03,000 let's go ahead and choose okay 253 00:10:03,000 --> 00:10:04,949 and now you can see that we have the 254 00:10:04,949 --> 00:10:06,480 number of inches note that when you do 255 00:10:06,480 --> 00:10:09,060 this if I ever look up there and I see 256 00:10:09,060 --> 00:10:10,319 abc123 257 00:10:10,319 --> 00:10:12,209 it means I could determine what type it 258 00:10:12,209 --> 00:10:14,670 is so let's up type any and generally 259 00:10:14,670 --> 00:10:16,290 you want to you know convert to a 260 00:10:16,290 --> 00:10:18,029 specific type rather than having any 261 00:10:18,029 --> 00:10:18,720 inside there 262 00:10:18,720 --> 00:10:20,430 but now you've kind of seen the first 263 00:10:20,430 --> 00:10:22,019 place the way you might have to write em 264 00:10:22,019 --> 00:10:26,029 code is that you have a custom column 265 00:10:26,029 --> 00:10:29,160 okay now the second thing is we can also 266 00:10:29,160 --> 00:10:32,699 use the advanced editor you know so 267 00:10:32,699 --> 00:10:37,079 let's say that I have this query inside 268 00:10:37,079 --> 00:10:39,120 here and notice that when I created the 269 00:10:39,120 --> 00:10:43,079 query and I go back again remember okay 270 00:10:43,079 --> 00:10:44,670 this was the Giants so let's go ahead 271 00:10:44,670 --> 00:10:48,959 and rename this one to the Giants and 272 00:10:48,959 --> 00:10:50,970 now I want to create a second and a 273 00:10:50,970 --> 00:10:53,100 third and a fourth query but I don't 274 00:10:53,100 --> 00:10:55,170 want to have to click click click each 275 00:10:55,170 --> 00:10:57,360 time so one of the things that we can do 276 00:10:57,360 --> 00:11:00,449 is we can go back to the advanced editor 277 00:11:00,449 --> 00:11:03,389 window once I get there I'm simply going 278 00:11:03,389 --> 00:11:06,569 to take all this code and copy it let's 279 00:11:06,569 --> 00:11:09,750 now create a new query so if I go down 280 00:11:09,750 --> 00:11:12,149 here and say give me a flight query they 281 00:11:12,149 --> 00:11:14,490 give you it's absolutely a blank query 282 00:11:14,490 --> 00:11:16,949 for various reasons they probably should 283 00:11:16,949 --> 00:11:18,269 just bring up the advanced editor window 284 00:11:18,269 --> 00:11:20,100 here you know but now let's go ahead 285 00:11:20,100 --> 00:11:23,970 take that and you know paste my work in 286 00:11:23,970 --> 00:11:26,129 now all I really got to do is kind of 287 00:11:26,129 --> 00:11:28,470 figure out you know what is you know the 288 00:11:28,470 --> 00:11:32,339 URL inside here so if I now go back here 289 00:11:32,339 --> 00:11:34,259 okay I guess everyone probably hates the 290 00:11:34,259 --> 00:11:35,839 Eagles even if you don't know about but 291 00:11:35,839 --> 00:11:38,939 you hate the Philadelphia Eagles but now 292 00:11:38,939 --> 00:11:40,170 let's go ahead and kind of put that 293 00:11:40,170 --> 00:11:42,329 inside there you know so for people who 294 00:11:42,329 --> 00:11:44,250 are kind of willing to work you know at 295 00:11:44,250 --> 00:11:46,790 this level of working with the EM code 296 00:11:46,790 --> 00:11:50,459 moving things back and forth and lots of 297 00:11:50,459 --> 00:11:51,809 other things that we do you know we're 298 00:11:51,809 --> 00:11:54,559 just gonna be a lot faster it's ident 299 00:11:54,559 --> 00:11:57,550 okay now 300 00:11:57,550 --> 00:12:00,620 what we want is we want more advanced 301 00:12:00,620 --> 00:12:02,480 editors now once again I wrote these 302 00:12:02,480 --> 00:12:05,300 slides a year ago so back then you can 303 00:12:05,300 --> 00:12:07,220 use notepad plus plus and there's one 304 00:12:07,220 --> 00:12:08,600 guy who kind of put an extension and to 305 00:12:08,600 --> 00:12:11,540 make that work Visual Studio code also 306 00:12:11,540 --> 00:12:13,640 has M query extensions and you can use 307 00:12:13,640 --> 00:12:14,660 what we're going to show here at the end 308 00:12:14,660 --> 00:12:17,560 is that there is a power query SDK now 309 00:12:17,560 --> 00:12:19,370 one wonderful thing that happened 310 00:12:19,370 --> 00:12:22,550 recently with power bi desktop let me go 311 00:12:22,550 --> 00:12:25,190 ahead and close the query tool for just 312 00:12:25,190 --> 00:12:26,480 a second 313 00:12:26,480 --> 00:12:28,490 and now I'm gonna go back and there's 314 00:12:28,490 --> 00:12:30,500 this pretty cool feature where now 315 00:12:30,500 --> 00:12:32,930 instead of just seeing the advanced 316 00:12:32,930 --> 00:12:34,640 editor with a dumb text box that doesn't 317 00:12:34,640 --> 00:12:36,920 have a coating or intellisense let's go 318 00:12:36,920 --> 00:12:40,310 back up here to options let's go to 319 00:12:40,310 --> 00:12:42,680 preview features and there is now M 320 00:12:42,680 --> 00:12:44,779 intellisense once again it says preview 321 00:12:44,779 --> 00:12:47,000 probably will you know go to GA sometime 322 00:12:47,000 --> 00:12:48,890 over the next couple months so I set 323 00:12:48,890 --> 00:12:53,779 that I'm gonna go ahead and close and I 324 00:12:53,779 --> 00:12:55,250 better go ahead and give this a name 325 00:12:55,250 --> 00:12:57,800 I'll just gonna call this n FL now well 326 00:12:57,800 --> 00:12:59,300 once you change the Primo features you 327 00:12:59,300 --> 00:13:01,700 have to close and reopen power bi 328 00:13:01,700 --> 00:13:07,190 desktop and now that we have done this 329 00:13:07,190 --> 00:13:09,560 inside here let me go ahead and open 330 00:13:09,560 --> 00:13:13,850 that project one more time and so we'll 331 00:13:13,850 --> 00:13:17,120 open the query editor window you know 332 00:13:17,120 --> 00:13:18,650 and now when we go to the advanced 333 00:13:18,650 --> 00:13:21,529 editor you know now we got the you know 334 00:13:21,529 --> 00:13:24,830 color coding inside there and you'll 335 00:13:24,830 --> 00:13:26,390 also see that you know if I go to 336 00:13:26,390 --> 00:13:30,470 something like you know table and I say 337 00:13:30,470 --> 00:13:33,740 table dot you know this takes us you 338 00:13:33,740 --> 00:13:35,060 know way ahead of where we used to be 339 00:13:35,060 --> 00:13:36,470 you know we just have to write the code 340 00:13:36,470 --> 00:13:38,870 and hope it worked okay so now we have a 341 00:13:38,870 --> 00:13:40,250 better editor that's just kind of built 342 00:13:40,250 --> 00:13:44,420 into power bi desktop now why would you 343 00:13:44,420 --> 00:13:45,430 learn em 344 00:13:45,430 --> 00:13:47,170 you know many people the answer is going 345 00:13:47,170 --> 00:13:48,730 to be no good reason I'm just gonna keep 346 00:13:48,730 --> 00:13:51,160 using power curry as it is you know but 347 00:13:51,160 --> 00:13:53,770 what you'll see is that I'm gonna show 348 00:13:53,770 --> 00:13:56,110 you a technique where we use something 349 00:13:56,110 --> 00:13:57,970 called a query function that just adds 350 00:13:57,970 --> 00:13:59,680 an amazing amount of power you know to 351 00:13:59,680 --> 00:14:00,930 me that would be your first motivation 352 00:14:00,930 --> 00:14:03,690 of kind of learning things at this level 353 00:14:03,690 --> 00:14:06,580 you want to perform calculations across 354 00:14:06,580 --> 00:14:08,050 rows you know we're going to show some 355 00:14:08,050 --> 00:14:10,870 examples but you know other things would 356 00:14:10,870 --> 00:14:13,029 be attached to a SharePoint list and 357 00:14:13,029 --> 00:14:17,020 what they do you know in the code that 358 00:14:17,020 --> 00:14:19,300 comes out of the box is that they figure 359 00:14:19,300 --> 00:14:21,190 out what is the grid of that particular 360 00:14:21,190 --> 00:14:24,339 list and they hard-code that in in 361 00:14:24,339 --> 00:14:26,170 SharePoint I often use this technique 362 00:14:26,170 --> 00:14:28,450 where if you want to take a list with 363 00:14:28,450 --> 00:14:30,339 10,000 items and delete every item you 364 00:14:30,339 --> 00:14:31,870 got to deal with one-by-one is going to 365 00:14:31,870 --> 00:14:33,550 take you know hours to do that or you 366 00:14:33,550 --> 00:14:34,839 can just drop the list and rephrase it 367 00:14:34,839 --> 00:14:37,180 and you can recreate the same the list 368 00:14:37,180 --> 00:14:39,070 with the same title but it's gonna have 369 00:14:39,070 --> 00:14:41,230 a different width so it breaks so you 370 00:14:41,230 --> 00:14:42,399 could basically jump in and kind of 371 00:14:42,399 --> 00:14:44,230 change the dependency you know from the 372 00:14:44,230 --> 00:14:46,300 quiz of the list to the title once again 373 00:14:46,300 --> 00:14:47,740 just another example of a technique 374 00:14:47,740 --> 00:14:49,779 where if you learn to work with an atom 375 00:14:49,779 --> 00:14:52,029 you can get there and if you're reliant 376 00:14:52,029 --> 00:14:53,980 just on the you know the UI experience 377 00:14:53,980 --> 00:14:57,880 you can't get there okay another reason 378 00:14:57,880 --> 00:15:00,040 you know might be that we have the same 379 00:15:00,040 --> 00:15:01,600 query logic and I want to use it across 380 00:15:01,600 --> 00:15:03,490 you know 10 different power bi desktop 381 00:15:03,490 --> 00:15:06,270 projects you know so writing the M code 382 00:15:06,270 --> 00:15:08,320 put in a source file and checking the 383 00:15:08,320 --> 00:15:09,910 source control you know means that I can 384 00:15:09,910 --> 00:15:11,350 now take that code out and copy and 385 00:15:11,350 --> 00:15:12,820 paste into ten different projects you 386 00:15:12,820 --> 00:15:15,370 know so it gets to be a good way to you 387 00:15:15,370 --> 00:15:17,050 know have you know golden query logic 388 00:15:17,050 --> 00:15:19,050 that you want to use across projects 389 00:15:19,050 --> 00:15:23,740 okay and stay ahead of the pack so let's 390 00:15:23,740 --> 00:15:25,410 go ahead and kind of look at some of the 391 00:15:25,410 --> 00:15:27,640 you know some of the fundamentals of 392 00:15:27,640 --> 00:15:30,310 working with it so it will have a 393 00:15:30,310 --> 00:15:32,380 functional programming language and if 394 00:15:32,380 --> 00:15:34,240 you program with VBA or your program 395 00:15:34,240 --> 00:15:35,950 with B B or C sharp you know their 396 00:15:35,950 --> 00:15:38,800 procedural languages so the idea of a 397 00:15:38,800 --> 00:15:40,630 functional language is that you will 398 00:15:40,630 --> 00:15:43,360 write a you know series of functions 399 00:15:43,360 --> 00:15:47,290 that basically get evaluated and I'll 400 00:15:47,290 --> 00:15:49,180 write function one which calls function 401 00:15:49,180 --> 00:15:51,040 two and when we evaluate function one it 402 00:15:51,040 --> 00:15:52,570 has to evaluate function two and then 403 00:15:52,570 --> 00:15:53,000 evaluate 404 00:15:53,000 --> 00:15:54,710 punks you want you know but what we're 405 00:15:54,710 --> 00:15:55,880 going to see is we're gonna miss Lee 406 00:15:55,880 --> 00:15:58,370 write a bunch of different expressions 407 00:15:58,370 --> 00:16:01,850 instead of writing statements now every 408 00:16:01,850 --> 00:16:04,700 query you know is a single expression 409 00:16:04,700 --> 00:16:07,580 that returns a single value and that 410 00:16:07,580 --> 00:16:09,290 might mean not mean a lot now and 411 00:16:09,290 --> 00:16:10,820 hopefully you know after half an hour 412 00:16:10,820 --> 00:16:12,320 talking about this and looking at the 413 00:16:12,320 --> 00:16:14,240 language you know that will start making 414 00:16:14,240 --> 00:16:17,360 sense also every query that you write 415 00:16:17,360 --> 00:16:20,840 has a return type now just getting 416 00:16:20,840 --> 00:16:21,410 started 417 00:16:21,410 --> 00:16:24,410 M is case sensitive you know so if you 418 00:16:24,410 --> 00:16:25,940 have uppercase first location you get it 419 00:16:25,940 --> 00:16:28,100 wrong it's not gonna work it's all about 420 00:16:28,100 --> 00:16:30,830 writing expressions then also when you 421 00:16:30,830 --> 00:16:33,410 write a query you know it can reference 422 00:16:33,410 --> 00:16:37,460 other queries by name so let's kind of 423 00:16:37,460 --> 00:16:40,400 start with something easy inside here 424 00:16:40,400 --> 00:16:45,130 and that is let's say I go back here and 425 00:16:45,130 --> 00:16:49,100 I want to create a new query yes I'll 426 00:16:49,100 --> 00:16:52,520 say give me a blank query inside here 427 00:16:52,520 --> 00:16:54,710 and I'll put something like you know the 428 00:16:54,710 --> 00:16:58,100 number two inside here notice that most 429 00:16:58,100 --> 00:16:59,480 of the queries that you have look at the 430 00:16:59,480 --> 00:17:01,820 icon it's a table you know so when we 431 00:17:01,820 --> 00:17:02,570 start using it 432 00:17:02,570 --> 00:17:03,980 we just gotta expect every query to 433 00:17:03,980 --> 00:17:05,810 return a table but now I have a query 434 00:17:05,810 --> 00:17:07,880 that returns a two and you can kind of 435 00:17:07,880 --> 00:17:11,119 see the type inside there if I you know 436 00:17:11,119 --> 00:17:14,079 change this to something else 437 00:17:14,079 --> 00:17:16,520 you know so we're gonna put something 438 00:17:16,520 --> 00:17:19,310 like hello inside there and notice that 439 00:17:19,310 --> 00:17:21,680 change to a B and C you know so every 440 00:17:21,680 --> 00:17:24,680 query you know has a return type let's 441 00:17:24,680 --> 00:17:27,640 go back to query one and in query one 442 00:17:27,640 --> 00:17:33,290 I'm going to let me just for this simple 443 00:17:33,290 --> 00:17:36,050 demo i'll create a new query you know so 444 00:17:36,050 --> 00:17:38,750 here we'll create another blank one and 445 00:17:38,750 --> 00:17:41,300 this one will put a world inside here 446 00:17:41,300 --> 00:17:44,060 you know so this is a query 3 and now 447 00:17:44,060 --> 00:17:46,370 let's create query 4 and let's kind of 448 00:17:46,370 --> 00:17:48,320 put things together and what you can see 449 00:17:48,320 --> 00:17:51,290 is that if I call everyone inside of 450 00:17:51,290 --> 00:17:54,290 another query so back here we'll go 451 00:17:54,290 --> 00:17:58,360 and create yet a another query and now 452 00:17:58,360 --> 00:18:01,010 inside here we're gonna reference other 453 00:18:01,010 --> 00:18:02,720 queries by name you know so there is a 454 00:18:02,720 --> 00:18:05,090 query to and then when I want to parse 455 00:18:05,090 --> 00:18:06,050 things together we're gonna use the 456 00:18:06,050 --> 00:18:07,760 ampersand and we'll put a space in there 457 00:18:07,760 --> 00:18:11,090 and now we'll have query 3 inside here 458 00:18:11,090 --> 00:18:13,910 and you know what we should be able to 459 00:18:13,910 --> 00:18:17,810 do is to put yeah thank you very much 460 00:18:17,810 --> 00:18:20,360 you put that equals in front of there 461 00:18:20,360 --> 00:18:22,040 yeah and then all of a sudden you can 462 00:18:22,040 --> 00:18:23,750 kind of see we get things working 463 00:18:23,750 --> 00:18:28,190 correctly inside there okay so when you 464 00:18:28,190 --> 00:18:30,650 start referencing queries by name things 465 00:18:30,650 --> 00:18:34,010 start working now the other thing we 466 00:18:34,010 --> 00:18:35,450 have is this thing which is called a 467 00:18:35,450 --> 00:18:36,920 flat statement so when you want to write 468 00:18:36,920 --> 00:18:39,080 things that are more complex you have 469 00:18:39,080 --> 00:18:41,480 lat and then you have this you know 470 00:18:41,480 --> 00:18:44,390 series of you know I kind of think of 471 00:18:44,390 --> 00:18:45,590 them as variables that you're grading 472 00:18:45,590 --> 00:18:48,590 var 1 equals hello world bar 2 equals I 473 00:18:48,590 --> 00:18:50,750 borrowed 3 equals I want to bounce 474 00:18:50,750 --> 00:18:53,150 something you know to uppercase and then 475 00:18:53,150 --> 00:18:55,940 the let statement the idea is we start 476 00:18:55,940 --> 00:18:58,100 by evaluating what's at the very bottom 477 00:18:58,100 --> 00:19:02,170 and in and quite often to evaluate that 478 00:19:02,170 --> 00:19:05,420 it has to come back up and evaluate well 479 00:19:05,420 --> 00:19:09,020 which triggers the evaluation of this ok 480 00:19:09,020 --> 00:19:12,190 but remember this is a single M 481 00:19:12,190 --> 00:19:14,870 expression that returns a single value 482 00:19:14,870 --> 00:19:16,640 so there might be lots of different 483 00:19:16,640 --> 00:19:19,250 calculations of individual expressions 484 00:19:19,250 --> 00:19:20,960 inside there as it calculates you know 485 00:19:20,960 --> 00:19:25,820 the final value of this state now also 486 00:19:25,820 --> 00:19:29,210 you can see that when I write this kind 487 00:19:29,210 --> 00:19:30,860 of code and I create a variable one 488 00:19:30,860 --> 00:19:32,420 variable two and it's got an expression 489 00:19:32,420 --> 00:19:35,750 if I look at applied steps I can see 490 00:19:35,750 --> 00:19:37,430 those kind of match up to what's inside 491 00:19:37,430 --> 00:19:40,040 there now here's where things get a 492 00:19:40,040 --> 00:19:42,230 little bit ugly we can either have that 493 00:19:42,230 --> 00:19:44,720 M code ugly or we can have the user 494 00:19:44,720 --> 00:19:48,110 experience ugly so what's gonna happen 495 00:19:48,110 --> 00:19:53,870 here is that if I want to have a step or 496 00:19:53,870 --> 00:19:56,270 a variable and I want there to be spaces 497 00:19:56,270 --> 00:19:58,280 inside to make it more readable the 498 00:19:58,280 --> 00:19:59,660 language says you can do that but you 499 00:19:59,660 --> 00:20:03,860 extra stuff so in particular if your 500 00:20:03,860 --> 00:20:05,210 variable doesn't have 501 00:20:05,210 --> 00:20:06,980 they could just have the name and the 502 00:20:06,980 --> 00:20:09,110 encodes a lot prettier if you have a 503 00:20:09,110 --> 00:20:11,330 space you have to then say pound sign 504 00:20:11,330 --> 00:20:13,040 and put the variable name in quotes 505 00:20:13,040 --> 00:20:15,890 inside there but you can see that I I 506 00:20:15,890 --> 00:20:18,050 can out break one that's really long and 507 00:20:18,050 --> 00:20:19,520 I can start referencing those things 508 00:20:19,520 --> 00:20:24,800 inside there now when I said one side 509 00:20:24,800 --> 00:20:29,090 has to be ugly or the other when you 510 00:20:29,090 --> 00:20:32,090 have spaces the applied steps looks a 511 00:20:32,090 --> 00:20:33,140 lot prettier but you can have spaces 512 00:20:33,140 --> 00:20:35,780 inside here so when you're using the 513 00:20:35,780 --> 00:20:37,760 tool they err on the side of making the 514 00:20:37,760 --> 00:20:39,650 encode a lot more messy by putting 515 00:20:39,650 --> 00:20:41,930 spaces inside there but then forcing you 516 00:20:41,930 --> 00:20:44,000 know they have the you know that extra 517 00:20:44,000 --> 00:20:47,240 pound sign and quotes around there when 518 00:20:47,240 --> 00:20:49,070 I do you believe with spaces and 519 00:20:49,070 --> 00:20:52,340 variable names are evil yeah but I don't 520 00:20:52,340 --> 00:21:01,010 think you can use underscores so it's 521 00:21:01,010 --> 00:21:02,840 really just well there's a series of 522 00:21:02,840 --> 00:21:05,150 characters but underscores and I think 523 00:21:05,150 --> 00:21:06,380 it's the same requirement but you know 524 00:21:06,380 --> 00:21:08,960 as you go back to the UI and you click 525 00:21:08,960 --> 00:21:10,280 click click it's going to be creating 526 00:21:10,280 --> 00:21:12,590 these things that have the pound sign so 527 00:21:12,590 --> 00:21:14,210 if I take over a script that I know I 528 00:21:14,210 --> 00:21:15,920 just want it right by hand I'll get rid 529 00:21:15,920 --> 00:21:17,420 of the spaces and get rid of the pound 530 00:21:17,420 --> 00:21:18,920 sign and the quotes just to make this 531 00:21:18,920 --> 00:21:20,210 script look a lot cleaner and you're 532 00:21:20,210 --> 00:21:22,010 writing em code you know but it's one 533 00:21:22,010 --> 00:21:25,820 side or the other now if we look at this 534 00:21:25,820 --> 00:21:27,130 right here you know here's kind of a 535 00:21:27,130 --> 00:21:30,800 common scenario where I've written a 536 00:21:30,800 --> 00:21:32,780 query and I have you know going down 537 00:21:32,780 --> 00:21:35,810 here and so when this is evaluated the 538 00:21:35,810 --> 00:21:37,160 first thing they do is they evaluate 539 00:21:37,160 --> 00:21:40,580 output but in order to evaluate output 540 00:21:40,580 --> 00:21:44,510 they then have to go up and evaluate you 541 00:21:44,510 --> 00:21:46,550 know text upper and now text upper bar 542 00:21:46,550 --> 00:21:48,430 three they have to evaluate bar three 543 00:21:48,430 --> 00:21:51,200 bar three you know to calculate its 544 00:21:51,200 --> 00:21:52,790 value we have to calculate what's the 545 00:21:52,790 --> 00:21:56,210 value of you know bar one and bar two so 546 00:21:56,210 --> 00:21:59,030 in order to calculate output there's 547 00:21:59,030 --> 00:22:01,130 this trigger that basically evaluates 548 00:22:01,130 --> 00:22:03,020 bar one then bar two of the large 549 00:22:03,020 --> 00:22:04,880 and an output so everything gets 550 00:22:04,880 --> 00:22:06,380 evaluated and then then returned you 551 00:22:06,380 --> 00:22:10,400 know a single value back now here's an 552 00:22:10,400 --> 00:22:12,170 interesting one if you look at this code 553 00:22:12,170 --> 00:22:15,410 bar 1 bar 2 bar 3 output let's say I 554 00:22:15,410 --> 00:22:19,070 took my things and I reverse them with 555 00:22:19,070 --> 00:22:21,530 this code work yes yeah and this code 556 00:22:21,530 --> 00:22:23,990 will absolutely work well this code 557 00:22:23,990 --> 00:22:25,220 confuse the heck out of the UI 558 00:22:25,220 --> 00:22:26,450 experience and the answer is absolutely 559 00:22:26,450 --> 00:22:28,880 yes yeah so if you want to just go and 560 00:22:28,880 --> 00:22:30,350 encode and do nothing but write encode 561 00:22:30,350 --> 00:22:33,680 this is fine you know so the order of 562 00:22:33,680 --> 00:22:35,210 expression in the let block doesn't 563 00:22:35,210 --> 00:22:38,450 matter to the mash-up engine but it will 564 00:22:38,450 --> 00:22:40,190 confuse in this tool and then you won't 565 00:22:40,190 --> 00:22:41,510 be able to kind of go back and use the 566 00:22:41,510 --> 00:22:47,900 UI portion anymore ok next thing is that 567 00:22:47,900 --> 00:22:50,150 when I start writing M code there's this 568 00:22:50,150 --> 00:22:52,700 really valuable principle of query 569 00:22:52,700 --> 00:22:56,150 folding and the idea of query folding is 570 00:22:56,150 --> 00:23:01,460 that if I'm writing a query and I say 571 00:23:01,460 --> 00:23:03,590 connect to that sequel box and then the 572 00:23:03,590 --> 00:23:05,060 very next step I say let me choose 573 00:23:05,060 --> 00:23:06,860 columns I don't want every single column 574 00:23:06,860 --> 00:23:08,390 I don't want select start you know I 575 00:23:08,390 --> 00:23:11,200 want select column 1 column 2 column 4 576 00:23:11,200 --> 00:23:14,120 so right after I have the first 577 00:23:14,120 --> 00:23:15,710 statement that gets the data from the 578 00:23:15,710 --> 00:23:17,660 database well then I have select columns 579 00:23:17,660 --> 00:23:20,360 and then I have a select rows so the 580 00:23:20,360 --> 00:23:22,220 mashup engine is smart enough to say hey 581 00:23:22,220 --> 00:23:23,900 we're getting rid of some columns we're 582 00:23:23,900 --> 00:23:25,340 getting rid of some rows why bring 583 00:23:25,340 --> 00:23:27,140 everything back so the idea of query 584 00:23:27,140 --> 00:23:29,240 folding is it can create a single sequel 585 00:23:29,240 --> 00:23:31,400 statement that selects only the columns 586 00:23:31,400 --> 00:23:33,290 that you want and has a where clause to 587 00:23:33,290 --> 00:23:34,820 only bring back two rows that you want 588 00:23:34,820 --> 00:23:37,460 so in general you know query folding is 589 00:23:37,460 --> 00:23:39,620 a very important concept and we like 590 00:23:39,620 --> 00:23:40,610 that inside there 591 00:23:40,610 --> 00:23:42,770 does that get broken though when you 592 00:23:42,770 --> 00:23:44,720 start doing certain things in them code 593 00:23:44,720 --> 00:23:46,820 so that query folding efficiency stops 594 00:23:46,820 --> 00:23:48,350 in a certain pool yes absolutely 595 00:23:48,350 --> 00:23:49,790 so this some of the things we have to 596 00:23:49,790 --> 00:23:51,200 worry about you know for instance there 597 00:23:51,200 --> 00:23:52,790 are some things that would stop query 598 00:23:52,790 --> 00:23:55,340 holding from being able to occur so then 599 00:23:55,340 --> 00:23:56,840 if you put your where clause down below 600 00:23:56,840 --> 00:23:59,360 that it has to bring back all the data 601 00:23:59,360 --> 00:24:00,710 and then kind of apply the filter 602 00:24:00,710 --> 00:24:02,660 locally and obviously if we bring back 603 00:24:02,660 --> 00:24:04,580 10,000 IU's to filter down to 100 that's 604 00:24:04,580 --> 00:24:06,860 not efficient you know so generally we 605 00:24:06,860 --> 00:24:08,300 want to have you know all the things 606 00:24:08,300 --> 00:24:09,980 that support query folding up at the top 607 00:24:09,980 --> 00:24:12,509 as close as possible 608 00:24:12,509 --> 00:24:15,779 okay now some data sources support 609 00:24:15,779 --> 00:24:18,269 folding in generally its relational 610 00:24:18,269 --> 00:24:20,369 databases and Oh data data sources that 611 00:24:20,369 --> 00:24:24,389 support query folding inside there okay 612 00:24:24,389 --> 00:24:25,619 what happens when the data source 613 00:24:25,619 --> 00:24:27,299 doesn't work for it building it just has 614 00:24:27,299 --> 00:24:29,190 to bring back all the data and basically 615 00:24:29,190 --> 00:24:32,009 you know do the extra work you know in 616 00:24:32,009 --> 00:24:33,839 memory wherever you're pulling the data 617 00:24:33,839 --> 00:24:37,469 back to okay what affects the way that 618 00:24:37,469 --> 00:24:39,539 query folding works the way we structure 619 00:24:39,539 --> 00:24:42,599 em code privacy levels can also affect 620 00:24:42,599 --> 00:24:45,149 that and then also you're able to do 621 00:24:45,149 --> 00:24:47,159 native query execution I just want to 622 00:24:47,159 --> 00:24:48,389 write my sequel statement and push that 623 00:24:48,389 --> 00:24:50,459 through if you're ever using native 624 00:24:50,459 --> 00:24:51,899 sequel statements query fully won't 625 00:24:51,899 --> 00:24:54,659 happen but then the converse is if I'm 626 00:24:54,659 --> 00:24:55,979 writing my own sequel statement I've 627 00:24:55,979 --> 00:24:57,629 probably got my columns and where 628 00:24:57,629 --> 00:24:59,039 clauses inside there so I'm bringing 629 00:24:59,039 --> 00:25:00,869 back the exact data that I want you know 630 00:25:00,869 --> 00:25:02,699 so I've kind of got the same effective 631 00:25:02,699 --> 00:25:04,589 query folding just by writing the sequel 632 00:25:04,589 --> 00:25:07,349 statements in design so here's an 633 00:25:07,349 --> 00:25:10,529 example I have the sequel database and I 634 00:25:10,529 --> 00:25:13,069 connect to the database inside there and 635 00:25:13,069 --> 00:25:16,379 I'm gonna say items equal customer so 636 00:25:16,379 --> 00:25:19,859 that basically when I connect to this 637 00:25:19,859 --> 00:25:21,569 database that's the name of a table 638 00:25:21,569 --> 00:25:24,479 there's my select rows there's my select 639 00:25:24,479 --> 00:25:27,089 columns I can also rename columns so 640 00:25:27,089 --> 00:25:28,440 these are the main things that support 641 00:25:28,440 --> 00:25:30,569 query folding and then what you're gonna 642 00:25:30,569 --> 00:25:33,299 see is that the mashup engines not going 643 00:25:33,299 --> 00:25:34,859 to you know create the sequel statement 644 00:25:34,859 --> 00:25:36,690 until all these are done and it's able 645 00:25:36,690 --> 00:25:39,029 to you know take everything inside of 646 00:25:39,029 --> 00:25:41,489 each of those and create a single sequel 647 00:25:41,489 --> 00:25:43,769 statement that brings back to exactly 648 00:25:43,769 --> 00:25:47,459 what I want okay now I might want to use 649 00:25:47,459 --> 00:25:50,159 native words you know so note that in 650 00:25:50,159 --> 00:25:53,579 the previous slide if you say source 651 00:25:53,579 --> 00:25:55,469 item equals customers you know there's 652 00:25:55,469 --> 00:25:57,029 an entity where a table that you're 653 00:25:57,029 --> 00:25:59,069 connecting to and so here we're doing 654 00:25:59,069 --> 00:26:00,329 something a little bit different you 655 00:26:00,329 --> 00:26:02,279 know we're saying query equals sequel 656 00:26:02,279 --> 00:26:04,259 I'm with another sequel statement you 657 00:26:04,259 --> 00:26:05,999 know so once again if you have anything 658 00:26:05,999 --> 00:26:08,009 afterwards that's filtering or selecting 659 00:26:08,009 --> 00:26:10,349 columns it's not gonna work you know so 660 00:26:10,349 --> 00:26:12,419 native queries you know never include 661 00:26:12,419 --> 00:26:14,669 any query folding and if you're gonna 662 00:26:14,669 --> 00:26:17,999 use these you know I won't use them you 663 00:26:17,999 --> 00:26:18,300 know 664 00:26:18,300 --> 00:26:21,300 as my first choice you might find some 665 00:26:21,300 --> 00:26:23,580 scenarios where you have to or you need 666 00:26:23,580 --> 00:26:25,170 to call a stored procedure you know so 667 00:26:25,170 --> 00:26:26,790 there cases where we find ourselves this 668 00:26:26,790 --> 00:26:29,790 is our only choice you know but in 669 00:26:29,790 --> 00:26:32,100 general if you just let the mashup 670 00:26:32,100 --> 00:26:34,410 engine work with tables and let it 671 00:26:34,410 --> 00:26:35,490 figure out the sequel statements you 672 00:26:35,490 --> 00:26:37,520 know generally that is what's preferred 673 00:26:37,520 --> 00:26:39,660 now as you're creating things inside 674 00:26:39,660 --> 00:26:43,410 there now with a good type system and 675 00:26:43,410 --> 00:26:45,120 that's just a programming language just 676 00:26:45,120 --> 00:26:49,200 like VBA c-sharp other languages so it 677 00:26:49,200 --> 00:26:51,750 has types you know we've seen that if it 678 00:26:51,750 --> 00:26:53,730 can't figure out what the type is it's a 679 00:26:53,730 --> 00:26:57,750 type any sometimes we get back values 680 00:26:57,750 --> 00:26:59,420 that are unknown you know so there's no 681 00:26:59,420 --> 00:27:04,460 logic to our false number text in binary 682 00:27:04,460 --> 00:27:08,700 now we also have data types for time and 683 00:27:08,700 --> 00:27:11,100 that second one should be date not data 684 00:27:11,100 --> 00:27:15,060 but time they take time date/time with 685 00:27:15,060 --> 00:27:17,220 the timezone built-in and also iteration 686 00:27:17,220 --> 00:27:19,710 and then there's complex types that 687 00:27:19,710 --> 00:27:22,380 we're gonna look at so one of the things 688 00:27:22,380 --> 00:27:23,970 that I might want to do is work with 689 00:27:23,970 --> 00:27:28,110 lists or work with a record or if I have 690 00:27:28,110 --> 00:27:30,750 a list of Records I have a table you 691 00:27:30,750 --> 00:27:32,820 know so list record table and function 692 00:27:32,820 --> 00:27:36,540 are also types inside the language if I 693 00:27:36,540 --> 00:27:39,720 go back here you can kind of see the 694 00:27:39,720 --> 00:27:41,910 return type yeah that's table because of 695 00:27:41,910 --> 00:27:44,180 the icon you know this one right here 696 00:27:44,180 --> 00:27:48,960 you know is you know a number inside 697 00:27:48,960 --> 00:27:52,650 here if you know I want to create 698 00:27:52,650 --> 00:27:57,950 something one two and we get the right 699 00:27:57,950 --> 00:28:01,200 character inside there let's see if I 700 00:28:01,200 --> 00:28:03,020 can finally get the correct character 701 00:28:03,020 --> 00:28:05,640 inside you know basically we're able to 702 00:28:05,640 --> 00:28:08,970 create lists now there's also 703 00:28:08,970 --> 00:28:11,580 user-defined types and the idea is that 704 00:28:11,580 --> 00:28:12,570 you have something like a table 705 00:28:12,570 --> 00:28:14,460 structure and you wanted to find a type 706 00:28:14,460 --> 00:28:17,250 we'll see that in just a second you know 707 00:28:17,250 --> 00:28:20,200 but here's writing M code 708 00:28:20,200 --> 00:28:22,119 and you know here's a number here's a 709 00:28:22,119 --> 00:28:23,649 boolean you know so true and false are 710 00:28:23,649 --> 00:28:26,499 both keywords in the language if I want 711 00:28:26,499 --> 00:28:30,309 to create a list inside here I basically 712 00:28:30,309 --> 00:28:32,350 just have something that is inside the 713 00:28:32,350 --> 00:28:34,149 curly braces now that's gonna be seen as 714 00:28:34,149 --> 00:28:35,679 a list 715 00:28:35,679 --> 00:28:39,269 one other thing let me spin back here to 716 00:28:39,269 --> 00:28:42,399 this notice that when you write a let's 717 00:28:42,399 --> 00:28:43,919 State Minh 20 writing this code by hand 718 00:28:43,919 --> 00:28:46,539 every single expression in the let block 719 00:28:46,539 --> 00:28:49,539 has to have a comma behind it except for 720 00:28:49,539 --> 00:28:51,249 the very last one so that's gonna trip 721 00:28:51,249 --> 00:28:52,419 you up when you do this you're gonna 722 00:28:52,419 --> 00:28:54,549 copy some things out and you'll forget 723 00:28:54,549 --> 00:28:56,830 the last one you know has a comma where 724 00:28:56,830 --> 00:28:58,359 it should or one of these doesn't have a 725 00:28:58,359 --> 00:29:02,649 comma so now we've seen that let's go a 726 00:29:02,649 --> 00:29:06,789 little bit further inside here and here 727 00:29:06,789 --> 00:29:08,739 is an example of initializing variables 728 00:29:08,739 --> 00:29:12,220 for time you know or date and you see 729 00:29:12,220 --> 00:29:13,570 that there's functions that have the 730 00:29:13,570 --> 00:29:15,369 pound sign built-in you know so pound 731 00:29:15,369 --> 00:29:18,489 time pound date and I can initialize 732 00:29:18,489 --> 00:29:21,580 dates and times with that also it's 733 00:29:21,580 --> 00:29:23,559 great a list you know so if I create 734 00:29:23,559 --> 00:29:26,169 this list you know think of a one 735 00:29:26,169 --> 00:29:28,179 dimensional array so I basically have a 736 00:29:28,179 --> 00:29:30,940 string array of three elements inside so 737 00:29:30,940 --> 00:29:34,019 when I create this variable right here I 738 00:29:34,019 --> 00:29:37,239 can also use this syntax right here 739 00:29:37,239 --> 00:29:39,460 where I specify the list and then I say 740 00:29:39,460 --> 00:29:41,980 I want the first element sub-zero sub-1 741 00:29:41,980 --> 00:29:45,399 sub-2 inside here and that's gonna allow 742 00:29:45,399 --> 00:29:47,109 me to kind of pull things you know out 743 00:29:47,109 --> 00:29:52,659 of a particular list now also what 744 00:29:52,659 --> 00:29:56,830 you'll find is that here you know we 745 00:29:56,830 --> 00:29:59,769 only have three elements you know so the 746 00:29:59,769 --> 00:30:01,809 index goes from zero to two so what's 747 00:30:01,809 --> 00:30:03,580 gonna happen when I say rat-pack for you 748 00:30:03,580 --> 00:30:05,499 know that's gonna be an error however 749 00:30:05,499 --> 00:30:08,710 there is this syntax right here where if 750 00:30:08,710 --> 00:30:10,840 I have something that result in an error 751 00:30:10,840 --> 00:30:12,820 but then I basically put the question 752 00:30:12,820 --> 00:30:15,399 mark behind it it basically says if 753 00:30:15,399 --> 00:30:16,899 there's nothing there don't give me a 754 00:30:16,899 --> 00:30:19,210 runtime error just return a null so 755 00:30:19,210 --> 00:30:20,590 quite often we kind of break that code 756 00:30:20,590 --> 00:30:22,659 you know to be protective and make sure 757 00:30:22,659 --> 00:30:26,080 that our queries don't blow up now 758 00:30:26,080 --> 00:30:28,960 here's an interesting you know example 759 00:30:28,960 --> 00:30:30,820 this one that Chris Webb came up with 760 00:30:30,820 --> 00:30:34,300 and that is what something that I have 761 00:30:34,300 --> 00:30:36,430 to write em code for I can't do through 762 00:30:36,430 --> 00:30:39,730 the UI so let's say that there's this 763 00:30:39,730 --> 00:30:41,380 issue where we keep pulling back data 764 00:30:41,380 --> 00:30:43,720 from the database and our gosh-darn 765 00:30:43,720 --> 00:30:45,220 users keep adding things you know like 766 00:30:45,220 --> 00:30:47,880 the pound sign or the @ sign it blows up 767 00:30:47,880 --> 00:30:50,410 so one of the things that I could do is 768 00:30:50,410 --> 00:30:55,060 I could write a fairly simple query just 769 00:30:55,060 --> 00:30:56,410 using the UI now I have no right now 770 00:30:56,410 --> 00:31:00,460 code we says find the pound sign and 771 00:31:00,460 --> 00:31:02,170 replace it with a blank string and find 772 00:31:02,170 --> 00:31:03,460 the outside and replace it with a byte 773 00:31:03,460 --> 00:31:06,100 string the problem is I can't think of 774 00:31:06,100 --> 00:31:07,570 what people might do in the future 775 00:31:07,570 --> 00:31:09,310 so instead of creating the list to 776 00:31:09,310 --> 00:31:10,960 exclude things when we come up with a 777 00:31:10,960 --> 00:31:14,170 list of what to include so what we can 778 00:31:14,170 --> 00:31:17,680 do is I can basically say set 1 and also 779 00:31:17,680 --> 00:31:19,030 this is kind of a tricky way to do a 780 00:31:19,030 --> 00:31:21,340 list where I say give me everything from 781 00:31:21,340 --> 00:31:23,350 this to that so now this is going to 782 00:31:23,350 --> 00:31:24,550 give me all the capital letters A 783 00:31:24,550 --> 00:31:26,140 through Z this is going to give me all 784 00:31:26,140 --> 00:31:28,930 the lowercase letters A through Z and 785 00:31:28,930 --> 00:31:30,430 now I can do something like this let's 786 00:31:30,430 --> 00:31:32,140 transform I'm hoping I'm not stealing 787 00:31:32,140 --> 00:31:33,780 the Thunder of who's going to come next 788 00:31:33,780 --> 00:31:37,060 but now I get 0 through 9 and now what 789 00:31:37,060 --> 00:31:38,620 are the other characters that I want and 790 00:31:38,620 --> 00:31:40,900 now here's all the characters I allow 791 00:31:40,900 --> 00:31:44,260 and now we'll say text select and it 792 00:31:44,260 --> 00:31:45,820 looks through strings and any characters 793 00:31:45,820 --> 00:31:47,650 that I have an additive or automatically 794 00:31:47,650 --> 00:31:49,720 excluded inside it you know so once 795 00:31:49,720 --> 00:31:50,800 again there's kind of a practical 796 00:31:50,800 --> 00:31:52,630 example of something that if I'm willing 797 00:31:52,630 --> 00:31:55,060 to work with the EM code we can kind of 798 00:31:55,060 --> 00:31:58,900 get done ok now the next thing we want 799 00:31:58,900 --> 00:32:01,060 to look at is records you know so 800 00:32:01,060 --> 00:32:03,730 remember when I work with a list we use 801 00:32:03,730 --> 00:32:06,280 the curly brace so what does it record 802 00:32:06,280 --> 00:32:08,530 just like a record a database you know 803 00:32:08,530 --> 00:32:12,250 it's a single set of data typically for 804 00:32:12,250 --> 00:32:13,690 you know one particular entity that has 805 00:32:13,690 --> 00:32:18,220 fields and so if I open the square 806 00:32:18,220 --> 00:32:19,990 brackets and say first name equals and 807 00:32:19,990 --> 00:32:21,790 last name equals well now I have a 808 00:32:21,790 --> 00:32:24,490 record now when you want to access 809 00:32:24,490 --> 00:32:26,020 things within a record right here you 810 00:32:26,020 --> 00:32:28,720 can kind of see that I'll say person and 811 00:32:28,720 --> 00:32:30,460 then inside the square brackets I 812 00:32:30,460 --> 00:32:33,400 reference the field name inside there so 813 00:32:33,400 --> 00:32:34,540 now that we have less than we have 814 00:32:34,540 --> 00:32:36,370 records what's the next thing we want a 815 00:32:36,370 --> 00:32:38,100 list of records 816 00:32:38,100 --> 00:32:42,880 okay now another reason that we might 817 00:32:42,880 --> 00:32:45,990 use records is that there are certain 818 00:32:45,990 --> 00:32:48,700 functions that we like to call you know 819 00:32:48,700 --> 00:32:51,160 so web content is a good example because 820 00:32:51,160 --> 00:32:55,090 what I call web contents I passed the 821 00:32:55,090 --> 00:32:57,850 URL there is a second options parameter 822 00:32:57,850 --> 00:33:00,160 but for me to pass something there I 823 00:33:00,160 --> 00:33:01,120 have to be able to correct records 824 00:33:01,120 --> 00:33:02,950 because it takes a record as the 825 00:33:02,950 --> 00:33:07,390 argument so here when I pass this time 826 00:33:07,390 --> 00:33:09,340 you create the options record and the 827 00:33:09,340 --> 00:33:11,770 header is equal and what are the headers 828 00:33:11,770 --> 00:33:13,660 equal is another record here we're gonna 829 00:33:13,660 --> 00:33:15,400 take a record and bury it inside of 830 00:33:15,400 --> 00:33:16,900 another record so I can basically pass 831 00:33:16,900 --> 00:33:21,040 that as a parameter now one other thing 832 00:33:21,040 --> 00:33:24,990 I want to note is that when you call web 833 00:33:24,990 --> 00:33:27,370 content it basically generates an HP 834 00:33:27,370 --> 00:33:30,250 request goes across the network and one 835 00:33:30,250 --> 00:33:31,660 of the things that you could do kind of 836 00:33:31,660 --> 00:33:33,310 dropping down to the lever develop the 837 00:33:33,310 --> 00:33:34,690 level stuff is that you can start 838 00:33:34,690 --> 00:33:38,080 putting headers you know named headers 839 00:33:38,080 --> 00:33:41,770 in the request that goes out so here if 840 00:33:41,770 --> 00:33:45,460 I say except equals well now I've got a 841 00:33:45,460 --> 00:33:47,380 record with the accept and this just 842 00:33:47,380 --> 00:33:49,480 gets translated you know into the HTTP 843 00:33:49,480 --> 00:33:52,720 request you know Heather variables that 844 00:33:52,720 --> 00:33:54,670 are going inside there and there's one 845 00:33:54,670 --> 00:33:57,130 which is OData max version and because 846 00:33:57,130 --> 00:33:59,860 this has the - inside I've got to 847 00:33:59,860 --> 00:34:01,450 basically play you know inside of the 848 00:34:01,450 --> 00:34:04,690 pound side and things inside there but 849 00:34:04,690 --> 00:34:06,190 there's just a practical example of I 850 00:34:06,190 --> 00:34:08,469 want to call web doc config I want to 851 00:34:08,469 --> 00:34:09,880 have more options I have the Bay of 852 00:34:09,880 --> 00:34:12,250 still you know crank records you know to 853 00:34:12,250 --> 00:34:14,199 be able to pass those parameters inside 854 00:34:14,199 --> 00:34:18,429 there okay now here's a couple other 855 00:34:18,429 --> 00:34:19,920 things you have this thing called the 856 00:34:19,920 --> 00:34:22,570 concatenation operator so it's pretty 857 00:34:22,570 --> 00:34:24,159 obvious with a string what that's gonna 858 00:34:24,159 --> 00:34:27,070 do you know but look what it can do if I 859 00:34:27,070 --> 00:34:29,949 have this list it has one element then I 860 00:34:29,949 --> 00:34:31,960 have another list and I basically can 861 00:34:31,960 --> 00:34:33,370 kind of make them together just kind of 862 00:34:33,370 --> 00:34:35,139 gives me a union one list that has you 863 00:34:35,139 --> 00:34:36,840 know all the elements of both lists and 864 00:34:36,840 --> 00:34:40,449 also I have a record that has a and I 865 00:34:40,449 --> 00:34:41,770 have another record that has B and I 866 00:34:41,770 --> 00:34:43,120 merge them together and then I get one 867 00:34:43,120 --> 00:34:44,980 record with both those fields you know 868 00:34:44,980 --> 00:34:46,420 so quite often when you're taking data 869 00:34:46,420 --> 00:34:47,679 for multiple places and 870 00:34:47,679 --> 00:34:50,168 merging it together you know the tool is 871 00:34:50,168 --> 00:34:52,179 kind of creating you know this type of 872 00:34:52,179 --> 00:34:54,429 logic behind the scenes and sometimes 873 00:34:54,429 --> 00:34:55,869 you know you want to be able to get the 874 00:34:55,869 --> 00:34:57,520 point where you can write this type of 875 00:34:57,520 --> 00:35:01,329 logic inside to okay now let's look at 876 00:35:01,329 --> 00:35:05,349 tables so let's say at first we'll just 877 00:35:05,349 --> 00:35:06,970 kind of hard code you in a table by hand 878 00:35:06,970 --> 00:35:10,690 you know so customers table table front 879 00:35:10,690 --> 00:35:12,970 record and then I just basically have a 880 00:35:12,970 --> 00:35:14,109 bunch of Records there to have first 881 00:35:14,109 --> 00:35:16,510 name and last name you can kind of see 882 00:35:16,510 --> 00:35:19,329 the output inside that's you at this 883 00:35:19,329 --> 00:35:24,010 point let me also go back to this demo 884 00:35:24,010 --> 00:35:29,710 and instead of using this file I'm going 885 00:35:29,710 --> 00:35:31,809 to open up a demo file you know so as 886 00:35:31,809 --> 00:35:37,690 part of the demo files we have one which 887 00:35:37,690 --> 00:35:39,309 is called introduction to M programming 888 00:35:39,309 --> 00:35:41,619 and when you open this up you know 889 00:35:41,619 --> 00:35:42,460 you're gonna see that there's really 890 00:35:42,460 --> 00:35:44,410 nothing in the report other than a 891 00:35:44,410 --> 00:35:46,750 message saying everything lives up in 892 00:35:46,750 --> 00:35:49,480 the queries here no but now if I open 893 00:35:49,480 --> 00:35:50,920 this up right here you know so this is 894 00:35:50,920 --> 00:35:52,480 something that you can open up and now 895 00:35:52,480 --> 00:35:54,099 you can kind of start looking at some of 896 00:35:54,099 --> 00:35:57,579 the things inside here and you know kind 897 00:35:57,579 --> 00:35:59,829 of seeing how you know we can start 898 00:35:59,829 --> 00:36:02,710 working with some of these inside here 899 00:36:02,710 --> 00:36:04,690 know for instance you know there is the 900 00:36:04,690 --> 00:36:07,540 simplest also over here you can kind of 901 00:36:07,540 --> 00:36:09,460 see the different icons you know if it's 902 00:36:09,460 --> 00:36:10,960 a list you're gonna get this icon you 903 00:36:10,960 --> 00:36:12,730 know if it's a table you're gonna get 904 00:36:12,730 --> 00:36:15,819 you know this icon inside here and so 905 00:36:15,819 --> 00:36:17,799 you know if I open this up you know 906 00:36:17,799 --> 00:36:19,450 there's my table that I've gotten back 907 00:36:19,450 --> 00:36:23,049 inside there okay now going a little bit 908 00:36:23,049 --> 00:36:27,369 further here these table columns you 909 00:36:27,369 --> 00:36:29,619 know are not strongly typed because when 910 00:36:29,619 --> 00:36:30,940 they come back it says what is the type 911 00:36:30,940 --> 00:36:33,460 and II don't like that I could go 912 00:36:33,460 --> 00:36:34,780 through by hand and convert the types 913 00:36:34,780 --> 00:36:36,220 you know but if I'm right in the EM code 914 00:36:36,220 --> 00:36:37,420 I might as well figure out you know how 915 00:36:37,420 --> 00:36:39,640 to write the EM code so that my columns 916 00:36:39,640 --> 00:36:42,930 you know have strong typing inside there 917 00:36:42,930 --> 00:36:46,900 so here's how you can do that you can 918 00:36:46,900 --> 00:36:49,660 create a user defined type so here we've 919 00:36:49,660 --> 00:36:51,339 created this type called customer record 920 00:36:51,339 --> 00:36:53,619 type and only it was I basically defined 921 00:36:53,619 --> 00:36:56,230 I have a record that has a first name 922 00:36:56,230 --> 00:36:57,910 and a last name column and both of those 923 00:36:57,910 --> 00:36:58,980 or 924 00:36:58,980 --> 00:37:01,049 you know I can create other columns you 925 00:37:01,049 --> 00:37:04,140 know of type number or type boolean 926 00:37:04,140 --> 00:37:07,710 inside there and now what I can do is I 927 00:37:07,710 --> 00:37:10,349 can say I want to have a type which is a 928 00:37:10,349 --> 00:37:13,200 table which is composed of this record 929 00:37:13,200 --> 00:37:16,230 type inside here now kind of putting 930 00:37:16,230 --> 00:37:19,200 this you know into a more practical set 931 00:37:19,200 --> 00:37:22,019 here let's go here and I create the type 932 00:37:22,019 --> 00:37:25,619 of record that I want and then I create 933 00:37:25,619 --> 00:37:29,249 the type of that and now down here when 934 00:37:29,249 --> 00:37:31,440 I say at table I say customer table type 935 00:37:31,440 --> 00:37:33,630 so now I've defined you know what the 936 00:37:33,630 --> 00:37:36,420 column types are going to be so that's 937 00:37:36,420 --> 00:37:37,859 how you can basically create strongly 938 00:37:37,859 --> 00:37:39,839 typed columns you know as you're writing 939 00:37:39,839 --> 00:37:45,269 your EM code inside there okay now the 940 00:37:45,269 --> 00:37:46,740 next thing I want is to discuss 941 00:37:46,740 --> 00:37:48,450 something which is called unary 942 00:37:48,450 --> 00:37:51,720 functions now if I kind of do the 943 00:37:51,720 --> 00:37:56,700 long-winded way first you have tables 944 00:37:56,700 --> 00:37:59,869 Legros you have this customer table and 945 00:37:59,869 --> 00:38:02,130 then what you want to be able to do is 946 00:38:02,130 --> 00:38:03,809 kind of do a for each you know so you 947 00:38:03,809 --> 00:38:05,640 have this kind of row and then you have 948 00:38:05,640 --> 00:38:10,019 this operator right here and so what I 949 00:38:10,019 --> 00:38:11,730 want to do is I want to select rows I 950 00:38:11,730 --> 00:38:13,380 want to filter and so you basically 951 00:38:13,380 --> 00:38:15,960 write a function here that looks at the 952 00:38:15,960 --> 00:38:18,859 rows and a customer ID you know is 953 00:38:18,859 --> 00:38:23,069 either equal to or less than ten return 954 00:38:23,069 --> 00:38:25,559 it you know so here's kind of how I can 955 00:38:25,559 --> 00:38:26,970 do the long-winded way and you kind of 956 00:38:26,970 --> 00:38:29,579 see that you put a table inside here you 957 00:38:29,579 --> 00:38:30,660 know here's the thing I want to filter 958 00:38:30,660 --> 00:38:33,480 and then here is a function that we won 959 00:38:33,480 --> 00:38:36,210 on each row to come up with a true or 960 00:38:36,210 --> 00:38:37,799 false of whether to include that row in 961 00:38:37,799 --> 00:38:39,619 the set floor to exclude it from the set 962 00:38:39,619 --> 00:38:42,450 okay now watch how the syntax you know 963 00:38:42,450 --> 00:38:45,150 gets easier maybe not quite as 964 00:38:45,150 --> 00:38:48,150 understandable but now this is the exact 965 00:38:48,150 --> 00:38:50,849 same code just with different syntax so 966 00:38:50,849 --> 00:38:52,920 what we can say is we can say each I 967 00:38:52,920 --> 00:38:54,539 want to go through there look at each of 968 00:38:54,539 --> 00:38:56,279 these records and what you'll see is 969 00:38:56,279 --> 00:38:57,150 that there's this weird little 970 00:38:57,150 --> 00:39:00,420 underscore and basically that is the 971 00:39:00,420 --> 00:39:02,609 record you're looking at so you go ahead 972 00:39:02,609 --> 00:39:04,589 record number one record to record three 973 00:39:04,589 --> 00:39:05,630 and yours 974 00:39:05,630 --> 00:39:07,400 when I look at this record look at the 975 00:39:07,400 --> 00:39:09,890 customer ID you know so it's just kind 976 00:39:09,890 --> 00:39:13,190 of that and a little easier syntax but 977 00:39:13,190 --> 00:39:16,970 now let's go ahead and get you know rid 978 00:39:16,970 --> 00:39:20,360 of that underscore so because this is so 979 00:39:20,360 --> 00:39:22,900 common that you're looking at records 980 00:39:22,900 --> 00:39:25,190 but now you just want to look at one 981 00:39:25,190 --> 00:39:27,740 field that the underscore is optional 982 00:39:27,740 --> 00:39:30,160 but still you're looking at you know the 983 00:39:30,160 --> 00:39:32,810 field of a record instead of a whole 984 00:39:32,810 --> 00:39:37,220 record itself now here's another example 985 00:39:37,220 --> 00:39:41,180 that I want to add column and as I add a 986 00:39:41,180 --> 00:39:43,310 column I have a first name and a last 987 00:39:43,310 --> 00:39:44,750 name but let's go ahead and add a 988 00:39:44,750 --> 00:39:47,750 calculated column for display name if 989 00:39:47,750 --> 00:39:49,190 you go through the tool and you 990 00:39:49,190 --> 00:39:51,860 basically say add custom column like I 991 00:39:51,860 --> 00:39:53,600 did before you know this is the type of 992 00:39:53,600 --> 00:39:55,160 M code that they're going to MIT for you 993 00:39:55,160 --> 00:39:57,200 they're gonna have in each there can 994 00:39:57,200 --> 00:39:59,000 have these things inside so now I have a 995 00:39:59,000 --> 00:40:01,310 new column name display name and it 996 00:40:01,310 --> 00:40:03,440 simply goes row by row and for each row 997 00:40:03,440 --> 00:40:05,390 by the first name last name within the 998 00:40:05,390 --> 00:40:06,830 spacing between concatenates them 999 00:40:06,830 --> 00:40:10,580 together now most of the time when you 1000 00:40:10,580 --> 00:40:12,950 use this you're working with a table so 1001 00:40:12,950 --> 00:40:14,210 you're looking at a record and you care 1002 00:40:14,210 --> 00:40:17,180 about one field okay here's another 1003 00:40:17,180 --> 00:40:19,970 scenario and the scenario right here I 1004 00:40:19,970 --> 00:40:21,590 don't have records but I have individual 1005 00:40:21,590 --> 00:40:24,050 strings so it's not something that has a 1006 00:40:24,050 --> 00:40:26,930 sub object it's the thing itself so now 1007 00:40:26,930 --> 00:40:28,850 when I go through this if I do list 1008 00:40:28,850 --> 00:40:32,630 transform and now I say each text upper 1009 00:40:32,630 --> 00:40:35,720 and so that refers to the item itself 1010 00:40:35,720 --> 00:40:37,790 the underscore not very readable you 1011 00:40:37,790 --> 00:40:39,560 know but once you understand what the 1012 00:40:39,560 --> 00:40:42,080 underscore beans you know it is the item 1013 00:40:42,080 --> 00:40:43,880 that we're looking through and it goes 1014 00:40:43,880 --> 00:40:45,710 through you know enumerations of item 1 1015 00:40:45,710 --> 00:40:50,510 item 2 item 3 and so on okay everyone's 1016 00:40:50,510 --> 00:40:53,510 having fun so far I can tell that let's 1017 00:40:53,510 --> 00:40:57,110 go ahead and look at some of the other 1018 00:40:57,110 --> 00:40:59,930 things that we can do with M code you 1019 00:40:59,930 --> 00:41:03,530 know so here is an example where let's 1020 00:41:03,530 --> 00:41:08,630 say that when I'm here I have a value of 1021 00:41:08,630 --> 00:41:12,350 124 but when I go here I want this to be 1022 00:41:12,350 --> 00:41:13,310 you know 1023 00:41:13,310 --> 00:41:16,940 some of these two inside here okay now 1024 00:41:16,940 --> 00:41:19,130 first of all you're gonna say why would 1025 00:41:19,130 --> 00:41:20,960 you ever do this in EM where we could 1026 00:41:20,960 --> 00:41:23,060 probably do that much easier index and 1027 00:41:23,060 --> 00:41:25,340 that's a pretty good argument you know 1028 00:41:25,340 --> 00:41:27,650 but there couldn't be cases where you 1029 00:41:27,650 --> 00:41:29,120 want to calculate a running sum and you 1030 00:41:29,120 --> 00:41:31,580 want to stop at some point or there just 1031 00:41:31,580 --> 00:41:32,750 could be cases where you want to do it 1032 00:41:32,750 --> 00:41:37,100 in your M code right I can't write - 1033 00:41:37,100 --> 00:41:38,690 Mike in my demo that would be one good 1034 00:41:38,690 --> 00:41:42,520 example so if I say not list dot range 1035 00:41:42,520 --> 00:41:46,090 what we can do is we can basically 1036 00:41:46,090 --> 00:41:49,060 figure out you know what the sales are 1037 00:41:49,060 --> 00:41:54,670 but also automatically you get an index 1038 00:41:54,670 --> 00:41:57,260 so when I look at a row in a table it's 1039 00:41:57,260 --> 00:41:58,490 going to give me an index if it's the 1040 00:41:58,490 --> 00:42:00,800 first item is gonna be zero so what 1041 00:42:00,800 --> 00:42:02,990 we're gonna do here is we're gonna say 1042 00:42:02,990 --> 00:42:05,960 you know when I get to this place right 1043 00:42:05,960 --> 00:42:08,720 here you know what we'd like to do you 1044 00:42:08,720 --> 00:42:11,150 know define this value you know is 1045 00:42:11,150 --> 00:42:13,520 basically to take all those values that 1046 00:42:13,520 --> 00:42:15,350 sum together you know so once again this 1047 00:42:15,350 --> 00:42:16,790 is something you could not do working 1048 00:42:16,790 --> 00:42:18,200 through the UI you know you'd have to 1049 00:42:18,200 --> 00:42:19,970 write em code you know if you want to 1050 00:42:19,970 --> 00:42:22,130 basically sum up things across rows 1051 00:42:22,130 --> 00:42:26,920 inside there now when we work with em 1052 00:42:26,920 --> 00:42:29,720 there is the power query language 1053 00:42:29,720 --> 00:42:31,160 reference that talks about getting 1054 00:42:31,160 --> 00:42:33,380 started it's a kind of a geek document 1055 00:42:33,380 --> 00:42:35,180 you have to kind of be a developer to 1056 00:42:35,180 --> 00:42:36,680 really want to look at that but then 1057 00:42:36,680 --> 00:42:38,660 there is a second thing which is the EM 1058 00:42:38,660 --> 00:42:42,320 function library so let's look at some 1059 00:42:42,320 --> 00:42:45,380 of the EM functions inside there you 1060 00:42:45,380 --> 00:42:47,750 know so let's say that I want to pull 1061 00:42:47,750 --> 00:42:50,240 data back from an O data feed inside 1062 00:42:50,240 --> 00:42:52,070 there you know so now we can go ahead 1063 00:42:52,070 --> 00:42:55,190 and put in something and now when we get 1064 00:42:55,190 --> 00:42:57,890 to this o date at endpoint you know what 1065 00:42:57,890 --> 00:43:00,280 is the name of the entity you know it is 1066 00:43:00,280 --> 00:43:03,560 customers inside there and what are the 1067 00:43:03,560 --> 00:43:05,960 columns I want to keep so now I put this 1068 00:43:05,960 --> 00:43:06,320 together 1069 00:43:06,320 --> 00:43:08,360 and I say table dot select columns and I 1070 00:43:08,360 --> 00:43:10,030 put the columns to keep it side there 1071 00:43:10,030 --> 00:43:12,730 okay and then I want to select rows and 1072 00:43:12,730 --> 00:43:16,070 you know notice that here with my M code 1073 00:43:16,070 --> 00:43:17,990 I kind of kept the variable names so 1074 00:43:17,990 --> 00:43:19,280 they don't have spaces so we don't have 1075 00:43:19,280 --> 00:43:22,790 the pound sign and the you know the open 1076 00:43:22,790 --> 00:43:24,569 quote inside there 1077 00:43:24,569 --> 00:43:33,760 okay now Oh data dot feed is something 1078 00:43:33,760 --> 00:43:36,069 that makes a call to get native data 1079 00:43:36,069 --> 00:43:38,079 back and then makes another call you 1080 00:43:38,079 --> 00:43:40,150 know to actually get the data so it 1081 00:43:40,150 --> 00:43:42,190 turns out that web contents is more 1082 00:43:42,190 --> 00:43:44,829 efficient than using a data feed you 1083 00:43:44,829 --> 00:43:46,030 know but now you're gonna have to know 1084 00:43:46,030 --> 00:43:48,430 more about OData so here's an example 1085 00:43:48,430 --> 00:43:51,880 where I call into this API and I have to 1086 00:43:51,880 --> 00:43:53,319 know enough that when I call into Oh 1087 00:43:53,319 --> 00:43:55,270 data and I want to get just the columns 1088 00:43:55,270 --> 00:43:58,270 I want you know we can say dollar sign 1089 00:43:58,270 --> 00:44:00,099 select here are the columns that I want 1090 00:44:00,099 --> 00:44:02,250 I can also put filtering inside there 1091 00:44:02,250 --> 00:44:05,819 you know so if you use Oh data dot feed 1092 00:44:05,819 --> 00:44:09,130 it can basically get query folding but 1093 00:44:09,130 --> 00:44:10,420 it's gonna have to make an extra round 1094 00:44:10,420 --> 00:44:11,980 trip to get the metadata before it can 1095 00:44:11,980 --> 00:44:14,319 discover the query quality so some 1096 00:44:14,319 --> 00:44:15,970 people like this because it's a single 1097 00:44:15,970 --> 00:44:18,339 round trip you have to know more to kind 1098 00:44:18,339 --> 00:44:20,410 of write the expression inside there you 1099 00:44:20,410 --> 00:44:21,789 know but once again if you know how to 1100 00:44:21,789 --> 00:44:23,289 make a no data call to get back just the 1101 00:44:23,289 --> 00:44:24,910 blows and columns that you want you know 1102 00:44:24,910 --> 00:44:27,339 if that's gonna be more efficient as it 1103 00:44:27,339 --> 00:44:30,460 goes inside there okay now the next 1104 00:44:30,460 --> 00:44:32,680 thing we're going to talk about you know 1105 00:44:32,680 --> 00:44:34,119 is the reason that some of you might be 1106 00:44:34,119 --> 00:44:35,740 inspired to go actually you know work 1107 00:44:35,740 --> 00:44:38,559 with the SEM code this notion of a query 1108 00:44:38,559 --> 00:44:43,119 function now query functions are simply 1109 00:44:43,119 --> 00:44:45,940 a query that takes a parameter inside 1110 00:44:45,940 --> 00:44:49,930 there now let me you know show an 1111 00:44:49,930 --> 00:44:55,150 example so I've got this data and let's 1112 00:44:55,150 --> 00:44:57,970 go back here and inside here in data 1113 00:44:57,970 --> 00:45:01,890 yeah I've got a couple different 1114 00:45:02,490 --> 00:45:05,740 basically unstructured text files you 1115 00:45:05,740 --> 00:45:07,059 know they have some data inside there 1116 00:45:07,059 --> 00:45:12,059 now what I want to do is let's open up 1117 00:45:12,059 --> 00:45:15,490 SharePoint inside here so first of all 1118 00:45:15,490 --> 00:45:16,510 let me go ahead 1119 00:45:16,510 --> 00:45:19,800 and acade and now that I've done that 1120 00:45:19,800 --> 00:45:26,640 we're gonna go to Portland embed 1121 00:45:26,640 --> 00:45:30,100 SharePoint calm I know 1122 00:45:30,100 --> 00:45:33,250 and so let's say that my data lives 1123 00:45:33,250 --> 00:45:35,470 inside of SharePoint so I'm going to 1124 00:45:35,470 --> 00:45:38,140 create a new document library and you 1125 00:45:38,140 --> 00:45:41,500 know inside of this document library 1126 00:45:41,500 --> 00:45:44,830 we're gonna put some data file now in 1127 00:45:44,830 --> 00:45:46,540 our case right here I'm gonna turn the 1128 00:45:46,540 --> 00:45:47,890 sound off because general it means I did 1129 00:45:47,890 --> 00:45:49,440 something wrong 1130 00:45:49,440 --> 00:45:52,390 let's give this a good time so was that 1131 00:45:52,390 --> 00:45:54,550 it really wants you to set your time 1132 00:45:54,550 --> 00:45:59,200 zone to it so let's create this new 1133 00:45:59,200 --> 00:46:00,430 document library and I'll just kind of 1134 00:46:00,430 --> 00:46:01,690 call this data it's gonna make the path 1135 00:46:01,690 --> 00:46:04,480 a little bit easier to get to okay and 1136 00:46:04,480 --> 00:46:06,880 then inside here what I'm gonna do is 1137 00:46:06,880 --> 00:46:09,250 I'm going to take three of these data 1138 00:46:09,250 --> 00:46:10,690 files not all four I'm going to put the 1139 00:46:10,690 --> 00:46:13,770 fourth one later and let's go ahead and 1140 00:46:13,770 --> 00:46:16,990 get rid of this and take these inside 1141 00:46:16,990 --> 00:46:20,320 here okay now let's say for this very 1142 00:46:20,320 --> 00:46:23,320 first query if I want to get something 1143 00:46:23,320 --> 00:46:26,619 out of SharePoint most people would 1144 00:46:26,619 --> 00:46:28,330 think is there a SharePoint connector 1145 00:46:28,330 --> 00:46:31,450 but because this lives in a document 1146 00:46:31,450 --> 00:46:34,270 library and you need an HTTP request we 1147 00:46:34,270 --> 00:46:37,690 have to use web contents so what I need 1148 00:46:37,690 --> 00:46:39,100 to do is first of all you know figure 1149 00:46:39,100 --> 00:46:42,280 out you know what is the path so let's 1150 00:46:42,280 --> 00:46:47,530 go back here and I'm going to let's go 1151 00:46:47,530 --> 00:46:49,210 ahead and just create a new project 1152 00:46:49,210 --> 00:46:55,600 before I'm gonna do inside here and as I 1153 00:46:55,600 --> 00:46:58,180 create this new project I'm gonna 1154 00:46:58,180 --> 00:47:00,760 basically write a query which is gonna 1155 00:47:00,760 --> 00:47:03,340 go pull data back from there so we're 1156 00:47:03,340 --> 00:47:05,530 gonna say I want to get something from 1157 00:47:05,530 --> 00:47:08,530 the web it's gonna bring up this box and 1158 00:47:08,530 --> 00:47:10,720 I'll put this inside there and then I 1159 00:47:10,720 --> 00:47:14,440 need the name of the actual file now so 1160 00:47:14,440 --> 00:47:16,180 let's say I go here because I'm lazy and 1161 00:47:16,180 --> 00:47:18,550 I'll just kind of copy and paste that 1162 00:47:18,550 --> 00:47:21,160 inside okay now we'll go ahead and we'll 1163 00:47:21,160 --> 00:47:21,580 add 1164 00:47:21,580 --> 00:47:22,840 inside and we'll go ahead and choose 1165 00:47:22,840 --> 00:47:26,620 okay so at first it's going to say that 1166 00:47:26,620 --> 00:47:28,120 you've never connected to the SharePoint 1167 00:47:28,120 --> 00:47:30,520 site you know so it's going to want me 1168 00:47:30,520 --> 00:47:33,850 to connect and Microsoft gives you a 1169 00:47:33,850 --> 00:47:36,880 dialog where many of the choices they 1170 00:47:36,880 --> 00:47:38,530 give you will absolutely result in 1171 00:47:38,530 --> 00:47:40,570 failure you have to know to pick 1172 00:47:40,570 --> 00:47:42,760 organizational account we're going to go 1173 00:47:42,760 --> 00:47:46,060 ahead and sign in with my organizational 1174 00:47:46,060 --> 00:47:49,510 account and once I have signed in and 1175 00:47:49,510 --> 00:47:51,910 then I should be able to now get to the 1176 00:47:51,910 --> 00:47:54,130 data and power bi desktop 1177 00:47:54,130 --> 00:47:56,800 remembers this and it's not going to ask 1178 00:47:56,800 --> 00:47:58,420 me it again but let's go ahead and 1179 00:47:58,420 --> 00:48:04,030 choose connect now when I look at the 1180 00:48:04,030 --> 00:48:06,400 actual data source you know here's an 1181 00:48:06,400 --> 00:48:09,580 example of a file that maybe got 1182 00:48:09,580 --> 00:48:13,120 downloaded from a mainframe and I have 1183 00:48:13,120 --> 00:48:14,650 to basically figure out you know how to 1184 00:48:14,650 --> 00:48:17,440 get the data out of the valid rows and 1185 00:48:17,440 --> 00:48:19,560 how to ignore the rows that aren't valid 1186 00:48:19,560 --> 00:48:22,690 this is something where we have fixed 1187 00:48:22,690 --> 00:48:24,820 with columns inside there you know so 1188 00:48:24,820 --> 00:48:27,070 this is 12 the next one is 12 the next 1189 00:48:27,070 --> 00:48:30,540 one is 24 so back in power bi desktop 1190 00:48:30,540 --> 00:48:32,890 what I can do is I can say hey I'm 1191 00:48:32,890 --> 00:48:35,830 dealing with a fixed-width file and the 1192 00:48:35,830 --> 00:48:37,780 first one starts at zero and goes to 12 1193 00:48:37,780 --> 00:48:40,030 and the next one starts at 13 it goes to 1194 00:48:40,030 --> 00:48:42,940 24 the next one goes to 48 and so on and 1195 00:48:42,940 --> 00:48:45,640 now the rows that have valid data you 1196 00:48:45,640 --> 00:48:47,050 can see that I can see those inside 1197 00:48:47,050 --> 00:48:49,720 there so let's go ahead and choose edit 1198 00:48:49,720 --> 00:48:54,850 and you know as I choose edit you know 1199 00:48:54,850 --> 00:48:58,180 let's call this one you know expenses 1200 00:48:58,180 --> 00:49:02,980 inside here and you know as we've got 1201 00:49:02,980 --> 00:49:05,620 that inside let's go ahead and rename 1202 00:49:05,620 --> 00:49:08,200 these columns you know so this one is 1203 00:49:08,200 --> 00:49:11,230 going to be the date the next one is 1204 00:49:11,230 --> 00:49:16,060 going to be the expense amount going 1205 00:49:16,060 --> 00:49:20,310 over here then we have the expense 1206 00:49:20,310 --> 00:49:24,130 category and then finally we just have a 1207 00:49:24,130 --> 00:49:27,580 description inside here now step number 1208 00:49:27,580 --> 00:49:29,670 one is let's get rid of those rows that 1209 00:49:29,670 --> 00:49:32,490 have valid data so an easy way to do 1210 00:49:32,490 --> 00:49:34,619 that is to go over to this first column 1211 00:49:34,619 --> 00:49:36,150 and say let's go ahead and convert that 1212 00:49:36,150 --> 00:49:38,520 into a date and some of these converts 1213 00:49:38,520 --> 00:49:41,309 just fine and some of them had nothing 1214 00:49:41,309 --> 00:49:42,720 in there so they convert to null and 1215 00:49:42,720 --> 00:49:44,490 some had strings that can't convert to 1216 00:49:44,490 --> 00:49:47,940 date so their errors so a very easy way 1217 00:49:47,940 --> 00:49:50,579 to deal with this is to say remove 1218 00:49:50,579 --> 00:49:54,510 errors and then to say remove things 1219 00:49:54,510 --> 00:49:57,000 that are empty you have to do it in that 1220 00:49:57,000 --> 00:49:58,200 order if you did the opposite order 1221 00:49:58,200 --> 00:50:00,089 wouldn't work you know but now I've kind 1222 00:50:00,089 --> 00:50:02,400 of gotten rid of the invalid rows of 1223 00:50:02,400 --> 00:50:05,280 data let's now go to a mount and let's 1224 00:50:05,280 --> 00:50:07,680 go move that over to fixed decimal you 1225 00:50:07,680 --> 00:50:08,670 know which is what we should do for 1226 00:50:08,670 --> 00:50:11,490 currency now for category you kind of 1227 00:50:11,490 --> 00:50:12,990 see there's some white space before and 1228 00:50:12,990 --> 00:50:15,180 afterwards you know so what we can do is 1229 00:50:15,180 --> 00:50:18,599 we can do a trim which basically takes 1230 00:50:18,599 --> 00:50:20,299 the white space off the front and back 1231 00:50:20,299 --> 00:50:22,440 and I don't need in this case but 1232 00:50:22,440 --> 00:50:24,210 there's also a clean that will get rid 1233 00:50:24,210 --> 00:50:26,339 of the you know the non-printable 1234 00:50:26,339 --> 00:50:28,020 characters the things that you can't see 1235 00:50:28,020 --> 00:50:29,430 let's screw you up every time you try to 1236 00:50:29,430 --> 00:50:31,799 do something with data okay so let's get 1237 00:50:31,799 --> 00:50:33,960 rid of those here and we'll get rid of 1238 00:50:33,960 --> 00:50:38,700 those here as well okay so now you know 1239 00:50:38,700 --> 00:50:41,420 I've basically got my you know first 1240 00:50:41,420 --> 00:50:43,980 query inside here and you can kind of 1241 00:50:43,980 --> 00:50:45,059 see that you know I didn't really have 1242 00:50:45,059 --> 00:50:46,680 to work with the advanced editor you 1243 00:50:46,680 --> 00:50:50,369 know but I've got this work done now for 1244 00:50:50,369 --> 00:50:52,770 this demo I'm gonna create one more 1245 00:50:52,770 --> 00:50:55,470 query and this query is gonna use 1246 00:50:55,470 --> 00:50:57,660 SharePoint so I'm gonna say create a 1247 00:50:57,660 --> 00:51:01,700 query and with this I'm gonna pick 1248 00:51:01,700 --> 00:51:03,780 SharePoint inside here you know so there 1249 00:51:03,780 --> 00:51:06,750 is a SharePoint connector and here I 1250 00:51:06,750 --> 00:51:10,260 want to pick SharePoint folder now you 1251 00:51:10,260 --> 00:51:13,010 saw that earlier when I went to this 1252 00:51:13,010 --> 00:51:17,010 SharePoint site it basically queried me 1253 00:51:17,010 --> 00:51:20,880 for credentials let's go back here and 1254 00:51:20,880 --> 00:51:22,079 when I do this I'm gonna put this 1255 00:51:22,079 --> 00:51:24,480 SharePoint site in but what you're gonna 1256 00:51:24,480 --> 00:51:25,799 see is that I have to add in the 1257 00:51:25,799 --> 00:51:28,349 credentials again because before I was 1258 00:51:28,349 --> 00:51:31,369 using the web you know 1259 00:51:31,369 --> 00:51:33,680 web content and now I'm using one of the 1260 00:51:33,680 --> 00:51:34,880 SharePoint connectors and they don't 1261 00:51:34,880 --> 00:51:37,369 basically you know share credentials so 1262 00:51:37,369 --> 00:51:39,859 now here I'm using an organizational 1263 00:51:39,859 --> 00:51:41,869 account you know but just for fun they 1264 00:51:41,869 --> 00:51:42,980 go ahead and switch it to organ is a 1265 00:51:42,980 --> 00:51:44,420 Microsoft account just to confuse you 1266 00:51:44,420 --> 00:51:46,880 you know but now we'll go through and 1267 00:51:46,880 --> 00:51:49,339 you know once I've logged in here then 1268 00:51:49,339 --> 00:51:51,109 I'm going to be able to use you know any 1269 00:51:51,109 --> 00:51:52,940 of the SharePoint connectors and they'll 1270 00:51:52,940 --> 00:51:54,319 already have my credentials cached 1271 00:51:54,319 --> 00:51:58,460 inside there okay now here when I pick 1272 00:51:58,460 --> 00:52:01,279 the SharePoint folder what it really 1273 00:52:01,279 --> 00:52:04,130 does is it basically does a site-wide 1274 00:52:04,130 --> 00:52:08,509 search and just to you know verify that 1275 00:52:08,509 --> 00:52:10,369 let me go ahead and choose edit right 1276 00:52:10,369 --> 00:52:15,319 here and let's go back to this site for 1277 00:52:15,319 --> 00:52:17,660 a second and let me go to a different 1278 00:52:17,660 --> 00:52:20,509 document library and let me find you 1279 00:52:20,509 --> 00:52:24,049 know some other file inside here so I go 1280 00:52:24,049 --> 00:52:26,240 here to slides and once kind of take a 1281 00:52:26,240 --> 00:52:30,140 slide and add this one side and so now 1282 00:52:30,140 --> 00:52:34,369 that we've added this other file inside 1283 00:52:34,369 --> 00:52:37,730 and there it is let's go back to our 1284 00:52:37,730 --> 00:52:41,299 query and refresh my query and what 1285 00:52:41,299 --> 00:52:42,799 we're going to see is that it's truly 1286 00:52:42,799 --> 00:52:44,230 doing kind of a site-wide 1287 00:52:44,230 --> 00:52:46,609 looking at every single document library 1288 00:52:46,609 --> 00:52:49,309 and every file inside so now what we're 1289 00:52:49,309 --> 00:52:50,210 gonna do is we're going to go to the 1290 00:52:50,210 --> 00:52:52,910 folder path because now this tells me 1291 00:52:52,910 --> 00:52:54,650 exactly you know what document library 1292 00:52:54,650 --> 00:52:57,170 is coming from and now I can filter and 1293 00:52:57,170 --> 00:52:59,599 say don't find things at any other 1294 00:52:59,599 --> 00:53:02,269 document library other than this one and 1295 00:53:02,269 --> 00:53:03,440 share points one of the things that 1296 00:53:03,440 --> 00:53:05,269 supports query folding so that will 1297 00:53:05,269 --> 00:53:06,890 basically send a request and not bring 1298 00:53:06,890 --> 00:53:08,210 back you know data about all the files 1299 00:53:08,210 --> 00:53:09,859 that will just bring back files for this 1300 00:53:09,859 --> 00:53:12,829 one thing inside there now for later in 1301 00:53:12,829 --> 00:53:15,230 the demo I need a file path to each one 1302 00:53:15,230 --> 00:53:18,140 of these files so what we're gonna do is 1303 00:53:18,140 --> 00:53:20,989 I'm gonna pick a folder path I'll come 1304 00:53:20,989 --> 00:53:24,019 back over to this side right here we 1305 00:53:24,019 --> 00:53:26,329 have the file name and I'll just 1306 00:53:26,329 --> 00:53:29,269 basically select those two and then 1307 00:53:29,269 --> 00:53:30,529 there is something where I can say 1308 00:53:30,529 --> 00:53:32,509 remove other columns and then I just 1309 00:53:32,509 --> 00:53:35,480 have those two so now what I'd like to 1310 00:53:35,480 --> 00:53:37,099 do is I'd like to kind of combine these 1311 00:53:37,099 --> 00:53:40,970 two together now it doesn't matter 1312 00:53:40,970 --> 00:53:42,500 which is on the left which is on the 1313 00:53:42,500 --> 00:53:44,480 right it matters which one I select 1314 00:53:44,480 --> 00:53:47,150 first I select this first then I hold 1315 00:53:47,150 --> 00:53:48,800 down either controller ship select the 1316 00:53:48,800 --> 00:53:51,500 second one and so now we're gonna 1317 00:53:51,500 --> 00:53:53,740 basically do a merge columns together 1318 00:53:53,740 --> 00:53:56,750 what's the separator none what is going 1319 00:53:56,750 --> 00:53:58,940 to be the name when I'm all done you 1320 00:53:58,940 --> 00:54:00,680 know and that is going to be file path 1321 00:54:00,680 --> 00:54:02,810 and now I basically just have one thing 1322 00:54:02,810 --> 00:54:06,710 that's the file path okay so I'm go 1323 00:54:06,710 --> 00:54:12,350 ahead and change this to expenses and 1324 00:54:12,350 --> 00:54:14,750 I'll change this to expenses too for a 1325 00:54:14,750 --> 00:54:16,310 second so I can come back and take the 1326 00:54:16,310 --> 00:54:18,830 other query ever this is the one that 1327 00:54:18,830 --> 00:54:21,050 actually does the work to take the data 1328 00:54:21,050 --> 00:54:23,570 and basically process it and put it I'm 1329 00:54:23,570 --> 00:54:26,930 going to change that to get expenses now 1330 00:54:26,930 --> 00:54:30,110 I'll come back to this one and I'll just 1331 00:54:30,110 --> 00:54:33,440 call this ones expenses now here is 1332 00:54:33,440 --> 00:54:34,760 where we're going to create a query 1333 00:54:34,760 --> 00:54:37,160 function and when I create a query 1334 00:54:37,160 --> 00:54:39,350 function I'm going to have to go into 1335 00:54:39,350 --> 00:54:42,470 the advanced editor and the way that we 1336 00:54:42,470 --> 00:54:44,570 create a query function inside here is 1337 00:54:44,570 --> 00:54:46,420 just by going to the very top and 1338 00:54:46,420 --> 00:54:48,860 putting in a parameter list open and 1339 00:54:48,860 --> 00:54:51,530 close paren and then I put the error 1340 00:54:51,530 --> 00:54:53,630 operator now which is the equal sign and 1341 00:54:53,630 --> 00:54:55,940 so inside here you know we're gonna say 1342 00:54:55,940 --> 00:55:00,080 file URL it's optional but it's good to 1343 00:55:00,080 --> 00:55:02,090 kind of say as text so you kind of imply 1344 00:55:02,090 --> 00:55:04,930 what type so now that we've done that 1345 00:55:04,930 --> 00:55:08,240 I'm gonna take this parameter and let's 1346 00:55:08,240 --> 00:55:11,540 get rid of this hard-coded file path 1347 00:55:11,540 --> 00:55:14,750 inside here so the idea is we're trying 1348 00:55:14,750 --> 00:55:17,060 to make this so that I can use this 1349 00:55:17,060 --> 00:55:18,830 function and call it repeatedly and just 1350 00:55:18,830 --> 00:55:20,180 pass a different file path and it's 1351 00:55:20,180 --> 00:55:21,440 going to give me a different result each 1352 00:55:21,440 --> 00:55:25,340 time now the good news is it's really 1353 00:55:25,340 --> 00:55:28,370 powerful the bad news is once you create 1354 00:55:28,370 --> 00:55:31,130 a function query you can no longer use 1355 00:55:31,130 --> 00:55:34,010 the UI experience so basically the 1356 00:55:34,010 --> 00:55:35,510 advanced editor is all you've got you 1357 00:55:35,510 --> 00:55:36,950 know so the recommendation is do as much 1358 00:55:36,950 --> 00:55:39,140 work as you can before you convert it 1359 00:55:39,140 --> 00:55:40,970 you know into the function query so you 1360 00:55:40,970 --> 00:55:42,140 kind of see I've already done all my 1361 00:55:42,140 --> 00:55:42,860 logic here 1362 00:55:42,860 --> 00:55:44,750 I just basically made it you know into a 1363 00:55:44,750 --> 00:55:47,720 function query at the end so now we've 1364 00:55:47,720 --> 00:55:49,520 done that let's go back to the first 1365 00:55:49,520 --> 00:55:50,609 query that has the file 1366 00:55:50,609 --> 00:55:53,130 so what I'd like to do is for each one 1367 00:55:53,130 --> 00:55:55,650 of these files I'd like to go ahead and 1368 00:55:55,650 --> 00:55:58,170 call that function query and pass the 1369 00:55:58,170 --> 00:56:00,599 file path inside there so the way that 1370 00:56:00,599 --> 00:56:02,759 we're gonna achieve that is by going to 1371 00:56:02,759 --> 00:56:06,029 add column once I get to add column I'm 1372 00:56:06,029 --> 00:56:07,950 gonna say I would like to invoke a 1373 00:56:07,950 --> 00:56:11,759 custom function so now and what is the 1374 00:56:11,759 --> 00:56:14,819 column name and it doesn't really matter 1375 00:56:14,819 --> 00:56:16,950 what I call this something's gonna call 1376 00:56:16,950 --> 00:56:20,220 it expense expand we're gonna get rid of 1377 00:56:20,220 --> 00:56:21,299 the column the column names gonna 1378 00:56:21,299 --> 00:56:23,910 disappear before the end but now what is 1379 00:56:23,910 --> 00:56:26,579 the query function and so for every 1380 00:56:26,579 --> 00:56:27,900 query where I've added one or more 1381 00:56:27,900 --> 00:56:29,700 parameters it shows up as a query 1382 00:56:29,700 --> 00:56:30,900 function so we'll go ahead and pick that 1383 00:56:30,900 --> 00:56:33,390 and because I've defined one parameter 1384 00:56:33,390 --> 00:56:35,730 it says pick one of the columns in this 1385 00:56:35,730 --> 00:56:37,890 list when there's only one and have that 1386 00:56:37,890 --> 00:56:39,710 be what feeds the parameter inside there 1387 00:56:39,710 --> 00:56:43,730 now I'll go ahead and choose okay and 1388 00:56:43,730 --> 00:56:45,930 basically saying information about 1389 00:56:45,930 --> 00:56:49,440 privacy so what I'm gonna do is I'm 1390 00:56:49,440 --> 00:56:51,089 going to come through here and I'm gonna 1391 00:56:51,089 --> 00:56:52,980 say let's make everything public you 1392 00:56:52,980 --> 00:56:55,470 know so that these error messages go 1393 00:56:55,470 --> 00:56:58,440 away inside there okay and now it's 1394 00:56:58,440 --> 00:57:01,650 going to run this inside here and it's 1395 00:57:01,650 --> 00:57:03,509 gonna make me still do more stuff inside 1396 00:57:03,509 --> 00:57:06,059 and make that public and what I probably 1397 00:57:06,059 --> 00:57:07,140 should have done is just go ahead and 1398 00:57:07,140 --> 00:57:08,940 turn the session option on to basically 1399 00:57:08,940 --> 00:57:11,489 get rid of you know those warnings okay 1400 00:57:11,489 --> 00:57:15,329 but now we have one row for each file 1401 00:57:15,329 --> 00:57:18,089 what we'd like to do is expand that so 1402 00:57:18,089 --> 00:57:20,190 now you can see that inside of that cell 1403 00:57:20,190 --> 00:57:21,509 for each one of these rows you have a 1404 00:57:21,509 --> 00:57:24,630 table and that table has multiple rows 1405 00:57:24,630 --> 00:57:27,359 so what we can do is we can now expand 1406 00:57:27,359 --> 00:57:29,970 this so that we get you know basically 1407 00:57:29,970 --> 00:57:32,609 one row per expense instead of one row 1408 00:57:32,609 --> 00:57:36,329 per file now when I do that here's the 1409 00:57:36,329 --> 00:57:38,249 data that's going to be inside there 1410 00:57:38,249 --> 00:57:41,130 let's go ahead and choose okay and now 1411 00:57:41,130 --> 00:57:44,819 it does the expansion inside now you can 1412 00:57:44,819 --> 00:57:46,529 see that when you do this it kind of 1413 00:57:46,529 --> 00:57:48,299 loses the type so I'm going to go 1414 00:57:48,299 --> 00:57:49,710 through here and I'm gonna put you know 1415 00:57:49,710 --> 00:57:53,609 the correct types back inside here okay 1416 00:57:53,609 --> 00:57:56,670 and now that we have done that and let's 1417 00:57:56,670 --> 00:57:58,630 go ahead and change that to text 1418 00:57:58,630 --> 00:58:02,860 and we can change this to text do we 1419 00:58:02,860 --> 00:58:03,670 need this anymore 1420 00:58:03,670 --> 00:58:05,650 then the answer is no you know we've 1421 00:58:05,650 --> 00:58:07,450 used this well in its golden locks a 1422 00:58:07,450 --> 00:58:08,950 thank you very much for playing you know 1423 00:58:08,950 --> 00:58:11,280 we'll go ahead and get rid of that so 1424 00:58:11,280 --> 00:58:14,410 let's kind of finish up the demo by you 1425 00:58:14,410 --> 00:58:16,780 know taking this and doing a close apply 1426 00:58:16,780 --> 00:58:21,100 and so this is now gonna run it on three 1427 00:58:21,100 --> 00:58:23,890 files and to kind of demonstrate this 1428 00:58:23,890 --> 00:58:25,750 let's go ahead and create you know a 1429 00:58:25,750 --> 00:58:28,870 simple report you know so we'll create a 1430 00:58:28,870 --> 00:58:33,130 chart and we'll look at the amount and 1431 00:58:33,130 --> 00:58:35,080 then we'll basically take the date and 1432 00:58:35,080 --> 00:58:37,870 have that be the access but now we're 1433 00:58:37,870 --> 00:58:39,460 gonna kind of use that neat feature 1434 00:58:39,460 --> 00:58:41,590 where it automatically builds out a 1435 00:58:41,590 --> 00:58:44,260 calendar hierarchy so I want to look at 1436 00:58:44,260 --> 00:58:46,750 each particular month right here okay so 1437 00:58:46,750 --> 00:58:49,990 now what we have is January through 1438 00:58:49,990 --> 00:58:52,960 September okay but what's really neat 1439 00:58:52,960 --> 00:58:54,670 about this query is that discovering 1440 00:58:54,670 --> 00:58:57,700 what files are there you know so now to 1441 00:58:57,700 --> 00:58:59,950 kind of finish off the demo let's go 1442 00:58:59,950 --> 00:59:03,190 ahead and go back to that data and the 1443 00:59:03,190 --> 00:59:08,080 document library currently this has 1444 00:59:08,080 --> 00:59:12,150 three files inside will now go back to 1445 00:59:12,150 --> 00:59:15,820 find that fourth file and let's drop 1446 00:59:15,820 --> 00:59:18,250 this you know down inside here and now 1447 00:59:18,250 --> 00:59:20,500 we have four files I don't to change my 1448 00:59:20,500 --> 00:59:22,630 query logic let's go ahead you know and 1449 00:59:22,630 --> 00:59:24,850 do a refresh inside here and that just 1450 00:59:24,850 --> 00:59:26,410 discovers you know there's four files 1451 00:59:26,410 --> 00:59:29,590 instead of three you know so holy david 1452 00:59:29,590 --> 00:59:31,630 together from multiple places you know 1453 00:59:31,630 --> 00:59:33,190 gets to be something that once you learn 1454 00:59:33,190 --> 00:59:34,960 how to use query functions you're gonna 1455 00:59:34,960 --> 00:59:36,010 be able to do some pretty neat things 1456 00:59:36,010 --> 00:59:38,710 you know think about i have a point of 1457 00:59:38,710 --> 00:59:41,140 sale databases you know I have 27 sequel 1458 00:59:41,140 --> 00:59:43,330 databases strewn out across America and 1459 00:59:43,330 --> 00:59:45,670 I don't need to get data from one of 1460 00:59:45,670 --> 00:59:47,560 them they have the exact same schema but 1461 00:59:47,560 --> 00:59:49,630 I need to basically get all 27 built 1462 00:59:49,630 --> 00:59:51,280 into one dataset so you create a table 1463 00:59:51,280 --> 00:59:53,500 that's got the you know query string for 1464 00:59:53,500 --> 00:59:55,000 each one and then you can do something 1465 00:59:55,000 --> 00:59:56,500 similar where you basically just move 1466 00:59:56,500 --> 00:59:57,760 through and run the query against 1467 00:59:57,760 --> 00:59:59,710 database one the database to database 1468 00:59:59,710 --> 01:00:01,780 three and it all dumps into a single 1469 01:00:01,780 --> 01:00:05,260 output table yeah so I've just seen most 1470 01:00:05,260 --> 01:00:07,720 people as they see M codes for me I want 1471 01:00:07,720 --> 01:00:09,640 to get into it this typically you know 1472 01:00:09,640 --> 01:00:09,900 if you 1473 01:00:09,900 --> 01:00:12,630 comes the motivation for that okay let 1474 01:00:12,630 --> 01:00:14,789 me take a pause take questions at this 1475 01:00:14,789 --> 01:00:18,990 point anybody have questions okay you 1476 01:00:18,990 --> 01:00:21,599 got to make these presentation materials 1477 01:00:21,599 --> 01:00:23,010 available yeah they've been available 1478 01:00:23,010 --> 01:00:26,039 for a year so at the very beginning you 1479 01:00:26,039 --> 01:00:30,710 know I only had this you know basically 1480 01:00:30,710 --> 01:00:33,210 this place right here so these slides 1481 01:00:33,210 --> 01:00:35,789 and all the demo code is available here 1482 01:00:35,789 --> 01:00:41,940 into this repository okay let me move 1483 01:00:41,940 --> 01:00:45,359 ahead to where I was okay so now I kind 1484 01:00:45,359 --> 01:00:48,029 of seen some of the basic things inside 1485 01:00:48,029 --> 01:00:53,700 here and finally get to query functions 1486 01:00:53,700 --> 01:00:58,829 inside okay now some of the other things 1487 01:00:58,829 --> 01:01:03,869 that you can do is let's say that you 1488 01:01:03,869 --> 01:01:05,880 want to create a query to generate a 1489 01:01:05,880 --> 01:01:07,319 sequence of numbers you know maybe 1490 01:01:07,319 --> 01:01:08,730 you're doing some type of mathematical 1491 01:01:08,730 --> 01:01:10,559 work or you want to make some kind of 1492 01:01:10,559 --> 01:01:14,279 sample data set so list dot generate you 1493 01:01:14,279 --> 01:01:15,210 know is one of the functions that's 1494 01:01:15,210 --> 01:01:16,980 built-in and accepts three parameters 1495 01:01:16,980 --> 01:01:22,520 and so the first one you know is a 1496 01:01:22,520 --> 01:01:25,619 function that takes no parameters and 1497 01:01:25,619 --> 01:01:27,599 has a single output they probably should 1498 01:01:27,599 --> 01:01:30,089 have made this a function you know so 1499 01:01:30,089 --> 01:01:32,730 you pass that inside the second thing is 1500 01:01:32,730 --> 01:01:35,099 the item that you're looking at and you 1501 01:01:35,099 --> 01:01:39,359 can have some type of a mistake then 1502 01:01:39,359 --> 01:01:40,859 inside there and then we're gonna say 1503 01:01:40,859 --> 01:01:45,289 each item you know is the item before +1 1504 01:01:45,289 --> 01:01:48,299 now one of the things that you could do 1505 01:01:48,299 --> 01:01:50,910 is you could kind of replace the second 1506 01:01:50,910 --> 01:01:53,309 and third parameter you know with each 1507 01:01:53,309 --> 01:01:55,650 inside there and the idea is let's go 1508 01:01:55,650 --> 01:01:57,569 through each record and the underscore 1509 01:01:57,569 --> 01:02:00,779 you know now becomes the item that 1510 01:02:00,779 --> 01:02:04,200 you're looking at okay and then also you 1511 01:02:04,200 --> 01:02:05,579 know I could split this up if I think 1512 01:02:05,579 --> 01:02:07,890 it's more readable inside here yeah so 1513 01:02:07,890 --> 01:02:10,049 you have the start function that's this 1514 01:02:10,049 --> 01:02:11,760 and this just returns a 1 that's my 1515 01:02:11,760 --> 01:02:13,579 starting point you have a test function 1516 01:02:13,579 --> 01:02:17,039 let's go ahead and test you know where 1517 01:02:17,039 --> 01:02:18,420 we should stop and then you have an 1518 01:02:18,420 --> 01:02:20,789 increment function also the idea that 1519 01:02:20,789 --> 01:02:21,450 this says 1520 01:02:21,450 --> 01:02:23,670 one that says keep going you know until 1521 01:02:23,670 --> 01:02:26,040 your item you know reaches ten we don't 1522 01:02:26,040 --> 01:02:27,690 want to be over ten and this basically 1523 01:02:27,690 --> 01:02:29,220 says take the item and add one for the 1524 01:02:29,220 --> 01:02:31,200 next one but you can kind of see that 1525 01:02:31,200 --> 01:02:32,549 you know mathematicians could do all 1526 01:02:32,549 --> 01:02:34,380 kinds of things if you want to break 1527 01:02:34,380 --> 01:02:35,970 like the Fibonacci sequence or something 1528 01:02:35,970 --> 01:02:37,530 like that you know this would give you 1529 01:02:37,530 --> 01:02:39,780 the ability to do that okay 1530 01:02:39,780 --> 01:02:41,400 who's created the Fibonacci sequence in 1531 01:02:41,400 --> 01:02:43,680 the last week or so nobody okay 1532 01:02:43,680 --> 01:02:46,079 Paul has Paul does it in his dreams 1533 01:02:46,079 --> 01:02:50,190 what's that five minutes okay so we also 1534 01:02:50,190 --> 01:02:53,160 have query parameters and the idea of a 1535 01:02:53,160 --> 01:02:55,440 query parameter is that you can define 1536 01:02:55,440 --> 01:02:57,660 something in a power bi desktop project 1537 01:02:57,660 --> 01:03:02,490 that then can easily change so one 1538 01:03:02,490 --> 01:03:04,099 reason we might make a query parameter 1539 01:03:04,099 --> 01:03:07,230 is that I don't want to hard-code the 1540 01:03:07,230 --> 01:03:09,390 database connection string you know into 1541 01:03:09,390 --> 01:03:11,670 my project because I have this one power 1542 01:03:11,670 --> 01:03:14,430 bi desktop project that can look at any 1543 01:03:14,430 --> 01:03:16,079 one of ten different sequel databases 1544 01:03:16,079 --> 01:03:18,240 that all have the exact stable schema so 1545 01:03:18,240 --> 01:03:20,730 I want to kind of pram ERISA way the 1546 01:03:20,730 --> 01:03:22,980 connection string so instead of having 1547 01:03:22,980 --> 01:03:25,020 to kind of dig into the guts of a query 1548 01:03:25,020 --> 01:03:27,059 and find the connection string I just 1549 01:03:27,059 --> 01:03:28,260 basically want to set up once per 1550 01:03:28,260 --> 01:03:29,640 project and then you know inside the 1551 01:03:29,640 --> 01:03:31,349 query you can reference the parameters 1552 01:03:31,349 --> 01:03:34,559 inside so query parameters you know get 1553 01:03:34,559 --> 01:03:38,190 to be a big thing now you can use query 1554 01:03:38,190 --> 01:03:40,380 parameters without writing em code you 1555 01:03:40,380 --> 01:03:42,089 know you can see that if I create a 1556 01:03:42,089 --> 01:03:44,280 query parameter I can then go into you 1557 01:03:44,280 --> 01:03:45,599 know many of the dialogues and say I 1558 01:03:45,599 --> 01:03:47,280 want to use a parameter as opposed to a 1559 01:03:47,280 --> 01:03:52,500 hard-coded thing inside there and then 1560 01:03:52,500 --> 01:03:54,900 what we're able to do is you know come 1561 01:03:54,900 --> 01:03:57,299 up with ways of allowing the user is 1562 01:03:57,299 --> 01:03:59,280 using the project to change the 1563 01:03:59,280 --> 01:04:01,349 parameter and there's different ways to 1564 01:04:01,349 --> 01:04:04,559 be able to do that inside now but what 1565 01:04:04,559 --> 01:04:05,970 you'll see is that parameters you know 1566 01:04:05,970 --> 01:04:10,470 get to be very flexible when you are 1567 01:04:10,470 --> 01:04:14,119 working directly with EMCO 1568 01:04:14,799 --> 01:04:18,699 you try and publish it well parameters 1569 01:04:18,699 --> 01:04:21,279 if I go back in year we're only 1570 01:04:21,279 --> 01:04:23,199 understood by power bi desktop so when 1571 01:04:23,199 --> 01:04:24,880 you publish them they totally disappear 1572 01:04:24,880 --> 01:04:27,279 crying and then because of so many 1573 01:04:27,279 --> 01:04:29,109 customers telling Microsoft I really 1574 01:04:29,109 --> 01:04:30,609 want to be able to control things they 1575 01:04:30,609 --> 01:04:33,759 now make it so that either manually you 1576 01:04:33,759 --> 01:04:35,170 know or through PowerShell or through 1577 01:04:35,170 --> 01:04:36,789 custom code that I write I can go and 1578 01:04:36,789 --> 01:04:39,249 change a parameter you know in a power 1579 01:04:39,249 --> 01:04:41,739 bi desktop project and what's neat about 1580 01:04:41,739 --> 01:04:44,109 that is I could publish pvx file and I 1581 01:04:44,109 --> 01:04:46,299 can have a piece of PowerShell that says 1582 01:04:46,299 --> 01:04:47,859 change the parameter that has the 1583 01:04:47,859 --> 01:04:49,119 database connection string in it and 1584 01:04:49,119 --> 01:04:51,039 then go ahead and do a refresh you know 1585 01:04:51,039 --> 01:04:53,229 so running PowerShell I can say take my 1586 01:04:53,229 --> 01:04:55,599 data set now the power bi desktop it's 1587 01:04:55,599 --> 01:04:57,039 already in the cloud switch the data 1588 01:04:57,039 --> 01:04:58,299 source and go ahead and refresh it those 1589 01:04:58,299 --> 01:04:59,619 are some of the things you know that you 1590 01:04:59,619 --> 01:05:04,749 can do with that decide they're the 1591 01:05:04,749 --> 01:05:07,689 power bi service API gives you access to 1592 01:05:07,689 --> 01:05:10,660 parameters and also the new PowerShell 1593 01:05:10,660 --> 01:05:13,359 support you know does the same thing but 1594 01:05:13,359 --> 01:05:15,309 they're supported in both you know I 1595 01:05:15,309 --> 01:05:16,869 would use the power bi think it's one 1596 01:05:16,869 --> 01:05:18,789 reason a parameter needs to have a 1597 01:05:18,789 --> 01:05:22,839 default value so that it can you can run 1598 01:05:22,839 --> 01:05:24,999 even if input isn't provided that's 1599 01:05:24,999 --> 01:05:25,359 correct 1600 01:05:25,359 --> 01:05:26,619 well there's a default value I think you 1601 01:05:26,619 --> 01:05:27,999 have to provide a default value when you 1602 01:05:27,999 --> 01:05:29,559 add them inside there in occur so 1603 01:05:29,559 --> 01:05:31,809 there's some fallback that it needs 1604 01:05:31,809 --> 01:05:34,150 inside there okay give them the time 1605 01:05:34,150 --> 01:05:35,410 constraints I think rather than covering 1606 01:05:35,410 --> 01:05:36,910 more material I'm just gonna open up to 1607 01:05:36,910 --> 01:05:38,170 a couple more questions about things 1608 01:05:38,170 --> 01:05:43,199 that we've covered here yeah go ahead 1609 01:05:43,510 --> 01:05:47,560 function to do a select from multiple 1610 01:05:47,560 --> 01:05:49,930 databases with the same structure I will 1611 01:05:49,930 --> 01:05:57,520 create a desktop will I try to is there 1612 01:05:57,520 --> 01:06:00,430 a trick to getting an F function that 1613 01:06:00,430 --> 01:06:03,670 iterates through multiple sources - yeah 1614 01:06:03,670 --> 01:06:06,550 I've heard of many problems the dimple 1615 01:06:06,550 --> 01:06:09,250 have had you know I can't speak to one 1616 01:06:09,250 --> 01:06:10,630 specific issue that I've heard about 1617 01:06:10,630 --> 01:06:13,120 that solves that particular problem you 1618 01:06:13,120 --> 01:06:29,830 know some have to do with privacy once 1619 01:06:29,830 --> 01:06:31,960 you try to string them all together with 1620 01:06:31,960 --> 01:06:34,870 function and basically a numerator all 1621 01:06:34,870 --> 01:06:37,270 at once it fails you know off the top of 1622 01:06:37,270 --> 01:06:39,490 my head I don't have an answer and then 1623 01:06:39,490 --> 01:06:42,010 in the core problem is probably that the 1624 01:06:42,010 --> 01:06:45,040 formula firewall doesn't doesn't trust 1625 01:06:45,040 --> 01:06:47,920 ya the the connections and it has to do 1626 01:06:47,920 --> 01:06:51,370 with privacy and authentication and the 1627 01:06:51,370 --> 01:06:53,740 the formula firewall has all kinds of 1628 01:06:53,740 --> 01:06:56,320 rules about whether whether or not 1629 01:06:56,320 --> 01:06:58,030 connections should be trusted 1630 01:06:58,030 --> 01:07:00,100 there isn't a silver ball an answer to 1631 01:07:00,100 --> 01:07:01,930 the to the question but that is probably 1632 01:07:01,930 --> 01:07:04,920 the core issue that you're experiencing 1633 01:07:04,920 --> 01:07:10,900 there's just a trick well if the new 1634 01:07:10,900 --> 01:07:14,470 trick would be a higher Chris Webb if 1635 01:07:14,470 --> 01:07:19,200 there's like a case mismatch in the 1636 01:07:19,200 --> 01:07:21,880 server name or something like that if 1637 01:07:21,880 --> 01:07:23,770 that if the server name and database 1638 01:07:23,770 --> 01:07:28,300 name strings don't match exactly you're 1639 01:07:28,300 --> 01:07:31,240 gonna get a Gateway problem even those 1640 01:07:31,240 --> 01:07:32,650 who haven't dealt with privacy of 1641 01:07:32,650 --> 01:07:34,330 firewall rules you know the problem 1642 01:07:34,330 --> 01:07:35,740 comes down to you have two different 1643 01:07:35,740 --> 01:07:37,780 data sources so you go query data source 1644 01:07:37,780 --> 01:07:39,100 one you get back a bunch of customer 1645 01:07:39,100 --> 01:07:40,960 names and then you send a query with all 1646 01:07:40,960 --> 01:07:42,520 those customer names and we're close to 1647 01:07:42,520 --> 01:07:44,110 something else and all of a sudden the 1648 01:07:44,110 --> 01:07:45,160 other thing is oh look at all this 1649 01:07:45,160 --> 01:07:47,050 customer information this is great you 1650 01:07:47,050 --> 01:07:49,270 know so it kind of errors on the side of 1651 01:07:49,270 --> 01:07:51,010 being conservative and locking things 1652 01:07:51,010 --> 01:07:54,670 down you know so the firewall rules are 1653 01:07:54,670 --> 01:07:55,010 really 1654 01:07:55,010 --> 01:07:56,690 tough to understand and that's 1655 01:07:56,690 --> 01:07:57,980 definitely something where Chris whateva 1656 01:07:57,980 --> 01:07:59,960 said the only blog entries that have put 1657 01:07:59,960 --> 01:08:04,340 any reasoning around how those work okay 1658 01:08:04,340 --> 01:08:06,500 maybe one more question anyone have did 1659 01:08:06,500 --> 01:08:10,250 you a question for the recursive 1660 01:08:10,250 --> 01:08:13,790 functions ir or if we want ideal 1661 01:08:13,790 --> 01:08:19,670 recursive functions for ya m does 1662 01:08:19,670 --> 01:08:21,799 support recursive functions and if you 1663 01:08:21,799 --> 01:08:24,589 look at the M language specification 1664 01:08:24,589 --> 01:08:26,210 they have an example of using recursion 1665 01:08:26,210 --> 01:08:28,609 inside there you know so you're able to 1666 01:08:28,609 --> 01:08:30,830 create a function that called itself you 1667 01:08:30,830 --> 01:08:31,819 know so if you wanted you somebody like 1668 01:08:31,819 --> 01:08:33,529 you know crawl a directory structure and 1669 01:08:33,529 --> 01:08:39,439 you usually recursion you do that yeah 1670 01:08:39,439 --> 01:08:41,630 but it does support recursion inside 1671 01:08:41,630 --> 01:08:44,450 their language you know supports it okay 1672 01:08:44,450 --> 01:08:46,689 well with that I think time constraints 1673 01:08:46,689 --> 01:08:48,770 Gregg is kind of putting the cane on me 1674 01:08:48,770 --> 01:08:51,170 now pulling me off seizure regulate 1675 01:08:51,170 --> 01:08:53,770 become very close 1676 01:08:54,050 --> 01:08:56,990 [Applause] 1677 01:08:56,990 --> 01:09:13,619 [Music]