Michael Alexander - Excel Power Pivot & Power Query For Dummies

12 Chapter 12FIGURE 12-1: Enter your custom code in the Advanced Editor window.FIGURE 12-2: Give your custom function a friendly name.FIGURE 12-3: A text file containing Invoice details.FIGURE 12-4: Use the Custom Column action to invoke your function.FIGURE 12-5: The custom column showing the results of the function for each row...FIGURE 12-6: You need to merge into one table the data in all the Excel files i...FIGURE 12-7: Connect to one of the Excel files in the target folder, and naviga...FIGURE 12-8: Use the Query Editor to apply any necessary transformation actions...FIGURE 12-9: Open the Advanced Editor to see the starter code.FIGURE 12-10: Wrapping the starter code with function tags and replacing the ha...FIGURE 12-11: The Query Editor after defining parameters.FIGURE 12-12: Create a new query using the From Folder connection type to retri...FIGURE 12-13: Use the Custom Column action to invoke the function.FIGURE 12-14: Power Query triggers the function and returns a table array for e...FIGURE 12-15: Click the Custom column header to expand the table arrays.FIGURE 12-16: Power Query exposes the columns pulled from each Excel file and a...FIGURE 12-17: Confirming that the parameters in the URL actually work.FIGURE 12-18: The clean base query.FIGURE 12-19: Wrapping the starter code with function tags and specifying a Yea...FIGURE 12-20: Create a simple parameter table.FIGURE 12-21: Use the Custom Column action to invoke the function.FIGURE 12-22: The combining of Excel and Web data triggers Power Query to ask a...FIGURE 12-23: Choose to load the final query results under the parameters table...FIGURE 12-24: The final parameter query provides an interactive mechanism to fl...

13 Chapter 13FIGURE 13-1: A star schema is the most efficient data model, with a single fact...FIGURE 13-2: Snowflake schemas are less efficient, causing Power Pivot to perfo...FIGURE 13-3: Slicers work together to show relevant data items based on a selec...FIGURE 13-4: Deselecting the Visually Indicate Items option with No Data disabl...

14 Chapter 14FIGURE 14-1: Hover the cursor over a query to get quick information, including ...FIGURE 14-2: Queries can be organized into groups.FIGURE 14-3: Use the Choose Columns command to find and select columns faster.FIGURE 14-4: Get in the habit of renaming applied steps.FIGURE 14-5: Create a new query from an existing column.FIGURE 14-6: The Query Dependencies dialog box displays how each of your querie...FIGURE 14-7: Use the Global Data Load options to set a default load behavior.FIGURE 14-8: Power Query automatically adds a step to change data types when da...FIGURE 14-9: Disabling the type detection feature.FIGURE 14-10: Disabling the privacy-level settings.FIGURE 14-11: Disabling relationship detection.

1 Cover

2 Title Page

3 Copyright

4 Table of Contents

5 Begin Reading

6 Index

7 About the Author

1 i

2 ii

3 1

4 2

5 3

6 4

7 5

8 6

9 7

10 8

11 9

12 10

13 11

14 12

15 13

16 14

17 15

18 17

19 18

20 19

21 20

22 21

23 22

24 23

25 24

26 25

27 26

28 27

29 28

30 29

31 30

32 31

33 32

34 33

35 34

36 35

37 36

38 37

39 38

40 39

41 40

42 41

43 42

44 43

45 44

46 45

47 46

48 47

49 48

50 49

51 50

52 51

53 52

54 53

55 54

56 55

57 56

58 57

59 58

60 59

61 60

62 61

63 63

64 64

65 65

66 66

67 67

68 68

69 69

70 70

71 71

72 72

73 73

74 74

75 75

76 76

77 77

78 78

79 79

80 80

81 81

82 82

83 83

84 84

85 85

86 86

87 87

88 89

89 90

90 91

91 92

92 93

93 94

94 95

95 96

96 97

97 98

98 99

99 100

100 101

101 103

102 104

103 105

104 106

105 107

106 108

107 109

108 110

109 111

110 112

111 113

112 114

113 115

114 116

115 117

116 118

117 119

118 120

119 121

120 122

121 123

122 124

123 125

124 126

125 127

126 128

127 129

128 130

129 131

130 132

131 133

132 134

133 135

134 136

135 137

136 138

137 139

138 141

139 142

140 143

141 144

142 145

143 146

144 147

145 148

146 149

147 150

148 151

149 152

150 153

151 154

152 155

153 156

154 157

155 158

156 159

157 160

158 161

159 162

160 163

161 164

162 165

163 166

164 167

165 168

166 169

167 170

168 171

169 172

170 173

171 174

172 175

173 176

174 177

175 178

176 179

177 180

178 181

179 182

180 183

181 184

182 185

183 186

184 187

185 188

186 189

187 190

188 191

189 192

190 193

191 194

192 195

193 196

194 197

195 198

196 199

197 200

198 201

199 202

200 203

201 204

202 205

203 207

204 208

205 209

206 210

207 211

208 212

209 213

210 214

211 215

212 216

213 217

214 218

215 219

216 220

217 221

218 222

219 223

220 225

221 226

222 227

223 228

224 229

225 230

226 231

227 232

228 233

229 234

230 235

231 236

232 237

233 238

234 239

235 240

236 241

237 242

238 243

239 244

240 245

241 246

242 247

243 248

244 249

245 250

246 251

247 253

248 254

249 255

250 256

251 257

252 258

253 259

254 260

255 261

256 262

257 263

258 264

259 265

260 266

261 267

262 268

263 269

264 270

265 271

266 272

267 273

268 274

269 275

270 277

271 278

272 279

Over the past few years, the concept of self-service business intelligence (BI) has taken over the corporate world. Self-service BI is a form of business intelligence in which end users can independently generate their own reports, run their own queries, and conduct their own analyses, without the need to engage the IT department.

The demand for self-service BI is a direct result of several factors:

More power users: Organizations are realizing that no single enterprise reporting system or BI tool can accommodate all their users. Predefined reports and high-level dashboards may be sufficient for casual users, but a large portion of today’s users are savvy enough to be considered power users. Power users have a greater understanding of data analysis and prefer to perform their own analysis, often within Excel.

Changing analytical needs: In the past, business intelligence primarily consisted of IT-managed dashboards showing historic data on an agreed-upon set of key performance metrics. Managers now demand more dynamic predictive analysis, the ability to perform data discovery iteratively, and the freedom to take the hard left and right turns on data presentation. These managers often turn to Excel to provide the needed analytics and visualization tools.

Speed of BI: Users are increasingly dissatisfied with the inability of IT to quickly deliver new reporting and metrics. Most traditional BI implementations fail specifically because the need for changes and answers to new questions overwhelmingly outpaces the IT department’s ability to deliver them. As a result, users often find ways to work around the perceived IT bottleneck and ultimately build their own shadow BI (under the radar) solutions in Excel.

Читать дальше