第3章 M语言基础 第3章M语言基础 3.1Excel函数 在Power Query中，M语言函数一般会包含两部分，第一部分通常表示函数所在的类别，第二部分通常表示函数的功能。例如，List.Transform()函数，类别为List，功能函数为Transform()。参见第1章的表11，Excel中的Power Query M语言函数共分为125个类别。其实，Excel的函数也是有类别的，只不过Excel函数的类别是隐性的。 3.1.1Office支持 Excel中所有函数的类别可以通过微软官方网站或Excel内部功能按钮查看。 1. Excel 函数(按类别列出) 以下是如何查看Excel的函数类别。打开浏览器，在搜索中输入“Excel 函数 按类别”，单击“百度一下”按钮，便可显示微软官方相应网页，如图31所示。 图31Excel函数(按类别)查询 在微软“Excel函数(按类别列出)”网页，Excel函数被划分为14类，如图32所示。 图32Excel函数(按类别列出) 2. Excel函数(选择类别) Excel函数的类别也可以直接从Excel中获知。打开Excel，单击编辑栏左侧的“插入函数”(fx)图标，在弹出的“插入函数”对话框中可以找到Excel函数的所有类别，如图33所示。 图33Excel函数的类别 在图33中可供选择的Excel函数的类别有“财务、日期与时间、数学与三角函数、统计、查找与引用、数据库、文本、逻辑、信息、工程、多维数据集、兼容性、与加载项一起安装的用户定义的函数、Web函数”，其划分的标准与图32是一致的。 3.1.2Excel函数汇总 查找Excel函数的类别，可采用“按类别”方式，也可采用“按字母”方式。打开浏览器，在搜索中输入“Excel 函数 按字母”，单击“百度一下”按钮，便可显示微软官方相应网页，如图34所示。 图34Excel函数(按字母)查询 1. Excel 函数(按字母顺序) 在微软官网“Excel函数(按字母顺序)”页面，复制官网页面网址，完成代码如下： //ch301-001 let herf="https://support.microsoft.com/zh-cn/office/excel-函数-按字母顺序-b3944572-255d-4efb-bb96-c6d90033e188", 源 = Web.Page(Web.Contents(herf)){0}［Data］, 分列A = Table.SplitColumn( 源, "函数名称", Splitter.SplitTextByEachDelimiter( {" "}, QuoteStyle.Csv, false ), {"函数名称"} ), 分列B = Table.SplitColumn( 分列A, "类型和说明", Splitter.SplitTextByAnyDelimiter ( {"#(00A0)","： "," "}, QuoteStyle.Csv ), {"函数(类别)"} ), 替换 = Table.TransformColumns(分列B, { "函数(类别)", each List.Accumulate( { {"函数",""}, {"和","与"} }, _, (x,y)=>Text.Replace(x,y{0},y{1}) ) } ), 分组 = Table.Group( 替换, "函数(类别)", { {"Excel函数", each Text.Combine( List.Distinct( List.Sort(［函数名称］)), " , ") }, {"个数", each Table.RowCount(_)} } ), 排序 = Table.Sort(分组,{"个数",1}) in 排序 通过以上代码，可以获取网页中所有Excel函数及其类别划分，如表31所示。 表31Excel的所有函数 续表 函数(类别)Excel函数个数 统计AVEDEV，AVERAGE，AVERAGEA，AVERAGEIF，AVERAGEIFS，BETA.DIST，BETA.INV，BINOM.DIST，BINOM.DIST.RANGE，BINOM.INV，CHISQ.DIST，CHISQ.DIST.RT，CHISQ.INV，CHISQ.INV.RT，CHISQ.TEST，CONFIDENCE.NORM，CONFIDENCE.T，CORREL，COUNT，COUNTA，COUNTBLANK，COUNTIF，COUNTIFS，COVARIANCE.P，COVARIANCE.S，DEVSQ，EXPON.DIST，F.DIST，F.DIST.RT，F.INV，F.INV.RT，F.TEST，FISHER，FISHERINV，FORECAST，FORECAST.ETS，FORECAST.ETS.CONFINT，FORECAST.ETS.SEASONALITY，FORECAST.ETS.STAT，FORECAST.LINEAR，FREQUENCY，GAMMA，GAMMA.DIST，GAMMA.INV，GAMMALN，GAMMALN.PRECISE，GAUSS，GEOMEAN，GROWTH，HARMEAN，HYPGEOM.DIST，INTERCEPT，KURT，LARGE，LINEST，LOGEST，LOGNORM.DIST，LOGNORM.INV，MAX，MAXA，MAXIFS，MEDIAN，MIN，MINA，MINIFS，MODE.MULT，MODE.SNGL，NEGBINOM.DIST，NORM.DIST，NORM.S.DIST，NORM.S.INV，NORMINV，PEARSON，PERCENTILE.EXC，PERCENTILE.INC，PERCENTRANK.EXC，PERCENTRANK.INC，PERMUT，PERMUTATIONA，PHI，POISSON.DIST，PROB，QUARTILE.EXC，QUARTILE.INC，RANK.AVG，RANK.EQ，RSQ，SKEW，SKEW.P，SLOPE，SMALL，STANDARDIZE，STDEV.P，STDEV.S，STDEVA，STDEVPA，STEYX，T.DIST，T.DIST.2T，T.DIST.RT，T.INV，T.INV.2T，T.TEST，TREND，TRIMMEAN，VAR.P，VAR.S，VARA，VARPA，WEIBULL.DIST，Z.TEST111 数学与三角ABS，ACOS，ACOSH，ACOT，ACOTH，AGGREGATE，ARABIC，ASIN，ASINH，ATAN，ATAN2，ATANH，BASE，CEILING.MATH，CEILING.PRECISE，COMBIN，COMBINA，COS，COSH，COT，COTH，CSC，CSCH，DECIMAL，DEGREES，EVEN，EXP，FACT，FACTDOUBLE，FLOOR.MATH，FLOOR.PRECISE，GCD，INT，ISO.CEILING，LCM，LET，LN，LOG，LOG10，MDETERM，MINVERSE，MMULT，MOD，MROUND，MULTINOMIAL，MUNIT，ODD，PI，POWER，PRODUCT，QUOTIENT，RADIANS，RAND，RANDARRAY，RANDBETWEEN，ROMAN，ROUND，ROUNDDOWN，ROUNDUP，SEC，SECH，SEQUENCE，SERIESSUM，SIGN，SIN，SINH，SQRT，SQRTPI，SUBTOTAL，SUM，SUMIF，SUMIFS，SUMPRODUCT，SUMSQ，SUMX2MY2，SUMX2PY2，SUMXMY2，TAN，TANH，TRUNC80 财务ACCRINT，ACCRINTM，AMORDEGRC，AMORLINC，COUPDAYBS，COUPDAYS，COUPDAYSNC，COUPNCD，COUPNUM，COUPPCD，CUMIPMT，CUMPRINC，DB，DDB，DISC，DOLLARDE，DOLLARFR，DURATION，EFFECT，FV，FVSCHEDULE，INTRATE，IPMT，IRR，ISPMT，MDURATION，MIRR，NOMINAL，NPER，NPV，ODDFPRICE，ODDFYIELD，ODDLPRICE，ODDLYIELD，PDURATION，PMT，PPMT，PRICE，PRICEDISC，PRICEMAT，PV，RATE，RECEIVED，RRI，SLN，SYD，TBILLEQ，TBILLPRICE，TBILLYIELD，VDB，XIRR，XNPV，YIELD，YIELDDISC，YIELDMAT56 工程BESSELI，BESSELJ，BESSELK，BESSELY，BIN2DEC，BIN2HEX，BIN2OCT，BITAND，BITLSHIFT，BITOR，BITRSHIFT，BITXOR，COMPLEX，CONVERT，DEC2BIN，DEC2HEX，DEC2OCT，DELTA，ERF，ERF.PRECISE，ERFC，ERFC.PRECISE，GESTEP，HEX2BIN，HEX2DEC，HEX2OCT，IMABS，IMAGINARY，IMARGUMENT，IMCONJUGATE，IMCOS，IMCOSH，IMCOT，IMCSC，IMCSCH，IMDIV，IMEXP，IMLN，IMLOG10，IMLOG2，IMPOWER，IMPRODUCT，IMREAL，IMSEC，IMSECH，IMSIN，IMSINH，IMSQRT，IMSUB，IMSUM，IMTAN，OCT2BIN，OCT2DEC，OCT2HEX54 兼容性BETADIST，BETAINV，BINOMDIST，CEILING，CHIDIST，CHIINV，CHITEST，CONFIDENCE，COVAR，CRITBINOM，EXPONDIST，FDIST，FINV，FLOOR，FTEST，GAMMADIST，GAMMAINV，HYPGEOMDIST，LOGINV，LOGNORMDIST，MODE，NEGBINOMDIST，NORM.INV，NORMDIST，NORMSDIST，NORMSINV，PERCENTILE，PERCENTRANK，POISSON，QUARTILE，RANK，STDEV，STDEVP，TDIST，TINV，TTEST，VAR，VARP，WEIBULL，ZTEST40 文本ARRAYTOTEXT，ASC，BAHTTEXT，CHAR，CLEAN，CODE，CONCAT，CONCATENATE，DBCS，DOLLAR，EXACT，FIND，FINDB，FIXED，JIS，LEFT、LEFTB，LEN、LENB，LOWER，MID、MIDB，NUMBERVALUE，PHONETIC，PROPER，REPLACE、REPLACEB，REPT，RIGHT、RIGHTB，SEARCH、SEARCHB，SUBSTITUTE，T，TEXT，TEXTJOIN，TRIM，UNICHAR，UNICODE，UPPER，VALUE，VALUETOTEXT35 查找与引用ADDRESS，AREAS，CHOOSE，COLUMN，COLUMNS，FILTER，FORMULATEXT，GETPIVOTDATA，HLOOKUP，HYPERLINK，INDEX，INDIRECT，LOOKUP，MATCH，OFFSET，ROW，ROWS，RTD，SORT，SORTBY，TRANSPOSE，UNIQUE，VLOOKUP，XLOOKUP，XMATCH25 日期与时间DATE，DATEDIF，DATEVALUE，DAY，DAYS，DAYS360，EDATE，EOMONTH，HOUR，ISOWEEKNUM，MINUTE，MONTH，NETWORKDAYS，NETWORKDAYS.INTL，NOW，SECOND，TIME，TIMEVALUE，TODAY，WEEKDAY，WEEKNUM，WORKDAY，WORKDAY.INTL，YEAR，YEARFRAC25 信息CELL，ERROR.TYPE，INFO，ISBLANK，ISERR，ISERROR，ISEVEN，ISFORMULA，ISLOGICAL，ISNA，ISNONTEXT，ISNUMBER，ISODD，ISREF，ISTEXT，N，NA，SHEET，SHEETS，TYPE20 DatabaseDAVERAGE，DCOUNT，DCOUNTA，DGET，DMAX，DMIN，DPRODUCT，DSTDEV，DSTDEVP，DSUM，DVAR，DVARP12 逻辑AND，FALSE，IF，IFERROR，IFNA，IFS，NOT，OR，SWITCH，TRUE，XOR11 多维数据集CUBEKPIMEMBER，CUBEMEMBER，CUBEMEMBERPROPERTY， CUBERANKEDMEMBER，CUBESET，CUBESETCOUNT，CUBEVALUE7 WebENCODEURL，FILTERXML，WEBSERVICE3 加载项与自动化CALL，EUROCONVERT，REGISTER.ID3 统计表31中的所有Excel函数的个数，共计481个，其中常用的10个函数为SUM()、IF()、LOOKUP()、VLOOKUP()、MATCH()、CHOOSE()、DATE()、DATES()、FIND()、INDEX()。 回顾1.2.1节，在Excel中的Power Query的M语言函数为808个。由此可见M语言函数的数量远多于Excel函数的数量，而且通过后续的深入学习，读者会越来越清晰地发现： 除M语言中的容器类(Table、List、Record)、数据库类(Sql、Sap、Access等)、时间智能函数，其余大量的M语言函数其实是与Excel函数语法与功能极其类似的。M语言真正学习的重点在于数据类型及数据结构的转换，在这808个函数中，常用的函数不到30个。 基于以上的发现： 读者完全可以采取“求同存异”的原则，“同”是指一些具备共性、共通的知识点。先消化Excel与M语言共通的函数(例如，文本类函数、数学与三角类函数、日期与时间函数等)，然后掌握M语言的一些共性知识(例如，复杂函数名称的关键字构成法、常用参数的数值化含义)，再进阶容器类(Table、List、Record)及数据库类(Sql、Access等)函数的学习，从而降低学习曲线的陡峭度。 2. Excel函数的构成 在Excel中运用饼图，统计表31中的数据，如图35所示。 图35Excel函数(按类别统计) 3. Excel函数与M语言函数 很多M语言函数与Excel函数的语法与功能是极其类似的，如表32所示。 表32Excel函数与M语言函数的类别对应(举例) Excel函数的类别Excel函数M语言函数M语言函数的类别 文本LenText.Length()Text 数学与三角RoundNumber.Round()Number 信息IsevenNumber.IsEven()Number 逻辑ifif关键字 日期与时间Date#date()Date NowDateTime.LocalNow()DateTime 统计CountRecord.FieldCount()Record List.Count()List Table.RowCount()Table 查找与引用Match List.MatchesAll()List Table.MatchesAllRows()Table 从表32的对应情况来看，Excel中的“文本”类别的函数多对应于M语言中的Text类别的函数； “数学与三角、信息”类别的函数多对应于M语言中的Number类别的函数； “日期与时间”类别的函数多对应于M语言中的Date、DateTime等类别的函数； “统计、查找与引用”类别的函数多对应于M语言中的List、Record、Table等类别的函数。 3.2M语言函数 3.2.1M语言函数简介 1. M语言函数(按类别列出) 以下是如何查看Power Query M语言函数的类别。打开浏览器，在搜索中输入“Power Query M函数”(大小写不敏感)，单击“百度一下”按钮，便可显示微软官方相应网页，如图36所示。 图36Power Query M函数搜索 在微软“Power Query M函数参考”网页，M语言函数被划分为24类，如图37所示。 图37M语言函数(按类别列出) 2. M语言函数的框架性理解 从理论上来讲，很多计算机语言的程序都可理解为程序=数据+方法。Power Query的M语言也可以这样理解。 在“程序=数据+方法”理论中，“数据”有“数据结构”与“数据类型”之分。在Power Query中，“数据结构”从狭义范围来讲，主要是指“表、记录、列表”三大容器； 从广义范围来讲，它还包括参数中的“合并器、拆分器、比较器、替换器”等。“数据类别”从狭义范围来讲，主要是指“文本、数字、日期时间、逻辑”等； 从广义范围来讲，它还涵盖“表、记录、列表”等数据结构，如表11中所列举的Table、Record、List、Text、Number、Date、Logical等类别。 “方法”(method)主要是指“M语言函数”及“语法规则”，它涉及“逻辑运算、增、删、改、查、分组聚合、数据处理、报错兼容”等方面。以“文本”类别的函数为例，它有“合并、拆分、移除、替换、重复”等功能，对应的函数有Text.Combine()、Text.Split()、Text.Remove()、Text.Replace()、Text.Repeat()等。在很多函数中，读者可以通过指定“区间范围、位置、分隔符”等方式进行更多复杂场景的应用。为加深读者对M语言的知识体系的框架性理解，本书将围绕图38中的内容进行展开。 图38Power Query M语言的体系 3. M语言函数名称的共性总结 常有使用者认为“M语言类别众多、函数冗长、参数复杂、易忘难学”，简化记忆、降低理解的难度是学习M语言之初首先要考虑的问题。 以下是可以降低M语言学习难度的几种方法： 参照图38，从整体上了解M语言的结构体系； 参照图37，从整体上了解M语言的函数类别； 参照表32，从整体上了解M语言函数与Excel函数类别间的对应关系。 既然“会写M语言函数的鄙视会改M语言函数的，会改M语言函数的鄙视只会界面操作的”，那么何不先熟悉M语言界面操作后自动生成的代码，然后对其改写； 改写的程度由浅入深，在不断熟记与掌握M语言函数语法的基础上，逐步深入理解M语言的核心。 在改写Power Query高级编辑器自动生成的代码的过程中，参照图125中的关键字，对所有函数的构成进行共性总结，找出M语言的命名规律。参照图38，找出三大容器(Table、Record、List)间的结构转换规律，以及各容器内数据类型的转换(To、From)的规律，不断地对代码量进行压缩、压缩、再压缩，最终实现化繁为简。 参照图125，采用“函数 按字母”的方式，对图125中的单词进行函数命名的拼接，最终完成知识的转化过程。经对比图125后发现，以下单词在常用的M语言函数中使用的频率较高，如表33所示。 表33M语言函数中名称的关键字 首字母关键字 AAdd、Alternate、Any、All SSelect、Split、Skip、Sort、Start TTable、To、Transform、Trim、Transpose、Type、Text、Time RRange、Record、Remove、Replace、Repeat、Rows、Reorder、Rename、Reverse CCombine、Columns、Contains、Count、Clean MMatching、Matches PPivot、Promote、Position、 Proper、Pad IIs、Index、Insert、Item 通过表33，细心的读者会发现： 在M语言中，60%以上的常用函数是由这些单词组成的。例如，Table.AddColumn()、Table.AlternateRows()、Table.SelectRows()、Table.SplitColumn()、Table.Skip()、Table.ToRows()、Table.TransformRows()、Table.TransformColumns()…… 通过以上的单词组合后，细心的读者不难发现： 所谓冗长的M语言函数名称，其实是由一些最常见、高频的单词依据一定的规则拼接而成的，而且含有某些特定单词的函数其参数中往往有其一定的特征。 例如，函数中含有Is单词的函数有55个，其返回值为true或false，常用于条件判断； 函数中含有Contains单词的函数有7个，其返回值为true或false，常用于条件判断。 例如，函数中含Combine单词的函数，大概率有一个参数指定数据为list数据结构。 而对于那些函数名称拼写简单的函数，有可能在添加M语言的类别后，其函数的名称与用法同Excel中的名称与用法完全一致或类似。例如，Text.Replace()、Text.Repeat()、Text.Trim()。 由此可见，Power Query M语言虽然函数众多，但其构建规则仍有迹可循，稍做共性总结后立即能找出其中的规律。 4. 可常量化参数(0、1、2) 在Power Query M语言中，0、1、2常用作某些函数参数的别名。在别名化过程中这3个参数所代表的意义如下： 0代表默认值。例如，升降序中的升序、表查询时的内连接、分组时的全局分组、位置查询时的首次出现位置、查询无结果时系统的自动报错功能等。 1代表常见方式。例如，升降序中的降序、表查询时的左外部连接、分组时的局部分组、位置查询时的末次出现位置、查询无结果时系统的忽略错误功能等。 2代表使用空值或所有位置显示之类的功能，即全有或全无之类的。 以下是一些常用参数与对应的0、1、2别名，如表34所示。 表34常量值参数及其代表的意义(1) 适 用 函 数适 用 场 景0代表的意义1代表的意义2代表的意义 List.Sort, Table.SortSort orderOrder.AscendingOrder.Descending List.DateTimes， List.Dates， List.DateTimeZones， List.Durations， List.Generate， List.Numbers， List.RandomOccurrence specificationOccurrence.FirstOccurrence.LastOccurrence.All Record.ToTable， Record.FromTable， Record.ToTableMissingFieldMissingField.ErrorMissingField.IgnoreMissingField.UseNull Table.SplitColumn, Table.FromListSplitterExtraValues.ListExtraValues.ErrorExtraValues.Ignore Table.GroupGroupKindGroupKind.LocalGroupKind.Global 5. 可常量化参数(0~6) 常量值参数及其代表的意义如表35所示。 6. 不可常量化的参数 Compare的3种比较方式： Compare.Ordinal 区分大小写，Compare.OrdinalIgnoreCase 不区分大小写，Compare.FromCulture 区域语言选项。 Replacer有两种替换方式: Replacer.ReplaceText 替换局部字符串(不支持数值)， Replacer.ReplaceValue 替换完整的值(匹配字符串或数值)。 表35常量值参数及其代表的意义（2） 可常量化参数表的连接方式表的连接算法 0JoinKind.InnerJoinAlgorithm.Dynamic 1JoinKind.LeftOuterJoinAlgorithm.PairwiseHash 2JoinKind.RightOuterJoinAlgorithm.SortMerge 3JoinKind.FullOuterJoinAlgorithm.LeftHash 4JoinKind.LeftAntiJoinAlgorithm.RightHash 5JoinKind.RightAntiJoinAlgorithm.LeftIndex 6JoinAlgorithm.RightIndex 3.2.2语法差异 在学习新语言的过程中，由于对语法的理解不到位，遇到报错提示是常有的事情。读者完全可以利用这些语法报错的机会借以历练、总结与成长。以下是一些在Excel与M语言使用过程中常见的语法差异及对应的报错提示。 1. 表达式错误 在Excel中，文本与数值可以直接进行文本拼接。在单元格中，表达式=3&"A"的返回值为"3A"，而在Power Query中，表达式=3&"A"返回的错误提示如下： Expression.Error: 无法将运算符 & 应用于类型 Number 和 Text。 详细信息: Operator=& Left=3 Right=A 出错原因： 表达式是在给定环境中的运算。在M语言中，文本与数值不能直接运算，所以报错提示。 解决办法： 将数值类型转换为文本类型(Text.From(3))，然后与文本拼接。 在Excel中，日期函数或日期文本与数值可直接相加。表达式=DATE(2021,8,21)+3或表达式 ="2021/8/21"+3返回的值为2021/8/24，而在Power Query中，表达式=#date(2021,8,21)+3返回的错误提示如下： Expression.Error: 无法将运算符 + 应用于类型 Date 和 Number。 详细信息: Operator=+ Left=2021/8/21 Right=3 出错原因： 日期类型与数值类型不能直接相加。 解决办法： =#date(2021,8,21)+ #duration(3,0,0,0)。 在Excel中，Date()函数中的year、month、day 3个参数中，参数的数据类型为数值或数值文本都不会影响返回的值。表达式=DATE(2021,8,"21")返回的值为2021/8/21，而在Power Query中，表达式=#date(2021,8,"21")返回的错误提示如下： Expression.Error: 无法将值 "21" 转换为类型 Number。 详细信息: Value=21 Type=［Type］ 出错原因： #date()函数内的3个参数的数据类型都必须是数值类型，不可以为文本或数值型文本。 图39报错提示 解决办法： 将数值型文本转换为文本或直接用数值，如=#date(2021,8,Number.From("21"))。 在Excel中，当必选参数不足时，会弹出警示框。例如，在单元格输入表达式=DATE(2021,8.21)后会弹出警示框，如图39所示。 在Power Query中，表达式=#date(2021,08.21) 返回的错误提示如下： Expression.Error: 2 参数传递到了一个函数，该函数应为 3。 详细信息: Pattern= Arguments=［List］ 出错原因： 该函数共3个必选参数，目前仅提供了两个。 解决办法： 补全所缺的参数。 在Excel中的所有函数，无论采用全部大写、全部小写、大小写相结合的任一方式，均不影响返回的结果。表达式=UPPER("excel")、表达式=upper("excel") 或表达式=uPpEr("excel")返回的值均为"EXCEL"，而在Power Query中，表达式=Text.upper("excel")返回的错误提示如下： Expression.Error: 无法识别名称"Text.upper"。需要确保其拼写正确。 出错原因： Text.upper() 中的upper首字母未大写。 解决办法： 正确书写Text.Upper()。 在Power Query中，当函数名的大小写书写不正确时会报错，单词拼写不正确时也会报错。例如，表达式=Text.Uppers("excel")返回的错误提示如下： Expression.Error: 无法识别名称"Text.Uppers"。需要确保其拼写正确。 出错原因： Text.Uppers()中多了一个字母s。 解决办法： 正确书写Text.Upper()。 在Excel中，find()函数的语法为FIND(find_text, within_text, ［start_num］)。前两个参数为必选参数，如果在单元格内输入Find("excel")，则会报错，如图39所示。 而在Power Query中，Text.PositionOf函数的语法为Text.PositionOf(text, substring,optional occurrence,optional compare)。前两个参数为必选参数，输入表达式=Text.PositionOf("excel")返回的错误提示如下： Expression.Error: 1 参数传递到了一个函数，该函数应介于 2 和 4 之间。 详细信息: Pattern= Arguments=［List］ 出错的原因： Text.PositionOf()的参数介于2和4之间(共4个参数，其中有两个是必选参数)，目前仅输入了1个参数。 解决办法： 补全参数，符合最少输入两个参数的要求。 尽管Text.PositionOf()函数返回的值为Number，但其函数的类别不是Number。如果不小心将Text.PositionOf()函数写成Number.PositionOf()。例如，表达式=Number.PositionOf("excel","e")，返回的错误提示如下： Expression.Error: 无法识别名称"Number.PositionOf"。需要确保其拼写正确。 对于可选参数，特别是默认可选参数，写与不写都不影响返回的值。例如，表达式=Text.PositionOf("excel","e",0)与表达式=Text.PositionOf("excel","e")返回的值是完全一致的。 另外，Excel中的Find()函数与M语言中Text.PositionOf()函数的参数中文本字符串的位置及查找后返回的值也略有所区别，如表36所示。 表36位置查找函数 函 数 类 别函数返 回 的 值 ExcelFIND("e","excel")1 M语言Text.PositionOf("excel","e")0 2. 语法错误 在Excel中，对于任何类型的语法错误，系统都会预警提示。例如，在单元格输入UPPER(("excel")，系统会弹出提示框,如图310所示。 单击“是(Y)”按钮，单元格返回值EXCEL，而在Power Query中，表达式=Text.Upper(("excel")返回的错误提示，如图311所示。 图310语法错误(1) 图311语法错误(2) 在图311中，RightParen是右括号的意思，Paren是parenthesis(括号)的缩写。当出现语法报错时，读者一定要明白系统在具体指什么。 在Excel中，在单元格输入表达式=UPPER( ("excel",)，系统会弹出提示框，如图312所示。 在Power Query中，表达式=Text.Upper(("excel",)返回的错误提示如图313所示。 图312语法错误(3) 图313语法错误(4) 同理，表达式=Text.Upper("excel".)返回的错误提示如图314所示。 在图314中，Comma是逗号的意思，“^”符号所指的位置即错误所在的位置。 再举例，表达式=Text.Upper("excel"］返回的错误提示如图315所示。 同理，在图315中， “^”符号所指的位置即错误所在的位置。通过以上几例的对比不难发现： “ ^”符号所指的位置一般为错误所在的位置，但提示语“应为令牌Comma”则未必准确。 当文本函数中最基本的""(双引号)未成对出现时，则会报错“文字无效”，相关文本内容都被“^”符号标识。例如，表达式=Text.Upper("excel］)的报错如图316所示。 图314语法错误(5) 图315语法错误(6) 图316语法错误(7) 3. 结论推导 M语言函数和Excel函数公式的主要共同点如下： 括号成对。对于存在多个参数的函数，很多函数有必选参数与可选参数； 可选参数可以省略，但必选参数不可缺少； 必选参数一旦不足，系统马上报错提示。 M语言函数和Excel函数公式的主要区别如下： M语言函数对大小写敏感且第1个字母都是大写的，Excel对大小写不敏感； M语言函数对数据类型有严格的要求，Excel中数据转换是隐式的； PQ行号以0为基数，Excel行号以1为基数。 3.2.3函数及语法备忘 M语言中函数的数量实在太多，并且有严格的大小写要求。很多情况下，忘记函数的拼写或语法是常有的事，找到一套备忘的方法同样很重要。 1. 借助图形化界面 在刚接触M语言的过程中，一次性掌握或记住所有函数及语法是有难度的。在想好解题思路的前提下，可以借助Power Query编辑器的图形化界面来降低学习的难度。例如，如果打算对某一列或某几列进行转换，则可先选中需转换的列，然后单击“转换”→“格式”→“小写”，最后对编辑栏生成的代码进行修改，如图317所示。 图317列表转换 接下来只需对编辑栏中的代码更改。例如，将编辑栏中的代码更改如下： = Table.TransformColumns(源,{{"城市", each Text.Split(_,"、"){0}}}) 2. 借助#shared 如果对某个函数只有个模糊的记忆，则完全可以在编辑栏中输入“=#shared”,然后将Record转换为表，最后在表中进行查询即可。以上前两个步骤可合并为一个嵌套语句，代码如下： = Record.ToTable(#shared) 如果所需查询的函数中含有单词Transform，则接下来可以在Power Query编辑器中采用图形化界面完成操作。操作步骤如下： 单击Name列右侧的“下拉”按钮(倒三角符号)，选择下拉菜单中“文本筛选器”的“包含”，在弹出的“筛选行”对话框中，填入包含的值Transform，单击“确定”按钮，如图318所示。 图318筛选行 在编辑栏显示的代码如下： = Table.SelectRows(源, each Text.Contains(［Name］, "Transform")) 在编辑区显示的数据如图319所示。 图319筛选的行 3. 借助编辑栏 通过图319的筛选，如果确定需寻找的函数为Table.TransformColumns()，则可以在编辑栏直接输入表达式=Table.TransformColumns查找该函数的语法。语法查询结果如图320所示。 图320语法查询 注意： 在编辑栏查找某函数的语法时，函数的后面不能加括号。 3.3M语言词法 以下内容为M语言最重要的基础知识，很重要但也很枯燥。希望读者能静下心来，多动手、多记忆、多练习，并举一反三。 3.3.1值 在M语言中，值是通过计算表达式所生成的数据。值有原始值(Primitive Values，或称基元值)和结构型值(Structured Values)之分。举例： 原始值(1,true, 3.1415,"abc"); 结构化值({1,2,3}，{［A=1］,{1,2,3}}, ［a=1,b=a+1,c=a+b+2］)。当值为文本时，需加双引号； 当值为数值时，不需加引号。 注意： 尽管许多值可以按字面形式写成表达式(例如，let A=1 in A，表达式1的计算结果为值1)，但值不是表达式。因为表达式是计算的方法，值是计算的结果。这种区别很细微，但很重要。 1. 值的种类 以下是M语言对值的分类，如表37所示。 表37值的分类 种类英文应 用 举 例 NullNullnull.1 逻辑Logicaltrue false 续表 种类英文应 用 举 例 数字Number0 1 -1 1.5 2.3e-5 时间Time#time(09,15,00) 日期Date#date(2013,02,26) 日期时间DateTime#datetime(2013,02,26, 09,15,00) 日期时区时间DateTimeZone#datetimezone(2013,02,26, 09,15,00, 09,00) 持续时间Duration#duration(0,1,30,0) 文本Text"hello" 二进制Binary#binary("AQID") 列表List{1, 2, 3} 记录Record［ A = 1, B = 2 ］ 表格Table#table({"X","Y"},{{0,1},{1,0}}) 函数Function(x) => x + 1 类型Typetype { number } type table ［ A = any, B = text ］ 2. 运算符 在M语言中，主要有以下运算符： = < <= > >= <> + - * / & ( ) ［ ］ { } @ ? => .. ... 在M语言中： 表为Table，每行的内容为一个Record，每列的内容为一个List； 行标用大括号{ }，初始值为0； 列标用中括号［ ］，［］内为字段名，列标名不用加引号。例如，源{0}［Data］，用于获取源表中第一行Data列的内容，其中{}、［］为表级运算符。 注意： 运算符是有优先级的。例如，*/(乘除)优先于+-(加减)。 3. 值的运算 M语言是强类型的，不同类型的值之间不可以进行运算； 相同类型的值之间并非可以进行各类运算(例如，对比运算、算术运算、逻辑运算等)，各类相同数据类型间可进行的运算如表38所示。 表38常用表达式及可进行的运算 表达式及运算符(M语言中常用)数据类型 常用表达式 的种类表达式运算符中文含义null逻 辑数 字时 间日 期日 期 时 间日 期 时 区 时 间持 续 时 间文 本二 进 制列 表记 录表 格 相等 表达式 x=y=等于 x<>y<>不等于 续表 表达式及运算符(M语言中常用)数据类型 常用表达式 的种类表达式运算符中文含义null逻 辑数 字时 间日 期日 期 时 间日 期 时 区 时 间持 续 时 间文 本二 进 制列 表记 录表 格 关系 表达式 x>=y>=大于或等于x>y>大于x、>、>=、<、<=)的。表达式= {1,2,3}<>{1,3,2}返回的值为true。 3.3.2变量 变量是对值的命名。在let…in…语句结构中，变量是步骤名称，当变量中存在空格时，应对字段加引号，然后在引号前面加#(井号)，例如，#"A B"。 在Record结构中，变量是字段名，即使变量中存在空格并能正常使用，即无须对字段加引号(及在引号前加#)； 当然，如果加上引号及#号也不会报错，例如，［A B=1］与［#"A B"=1］都是允许的，并且二者是等效的。 3.3.3环境 环境是由let…in…或Record结构中的所有变量组成。在每个let…in…结构中，由所有变量组成一个let…in…环境； 在每个Record结构中，由所有字段名(变量)组成Record环境。 在同一环境中，每个变量都必须是唯一的，所以变量也可以称为“唯一标识符”或“标识符”。如果打算在同一环境中命名两个相同的变量(或称应用步骤的名称、标识符)，则系统会报错提示。例如，表达式= let A=1,A=2 in A或表达式 = ［A=1,A=2］的错误提示均为 Expression.Error: 名称"A"被定义多次。 在let…in…结构中，由所有变量构成了环境，let表达式使用包含所有变量的环境来计算后面的表达式。应用举例，代码如下： let x=1,y=x+3,z=x+y in z 返回的值为5。代码中，x的环境为y、z； y的环境为x、z； z的环境为x、z。 在Record结构中，由所有字段构成了环境，初始字段表达式使用修改后的环境计算每个字段的子表达式。应用举例，代码如下： ［x=1,y=x+3,z=x+y］ 返回的值为Record。代码中，x的环境为y、z； y的环境为x、z； z的环境为x、z。 在let…in…结构中嵌套Record结构或在Record结构中嵌套let…in…结构也是允许的。let结构应用举例，代码如下： //ch302-002 let x= 1, y = ［x=1,y=x+3,z=5］［y］, z = x+y in z Record结构应用举例，代码如下： ［x=1,y=let x=1,y=x+3 in y, z=x+y］［z］ 以上代码返回的值均为5。在以上两个嵌套代码中，每个代码中的let表达式中的y与Record中的字段y的所处环境均不相同，所以不会产生标识符或变量冲突，但不易于新手理解或未来的代码维护，所以不建议这样命名。以此Record结构为例，修改后的代码如下： ［x=1,y=let a=1,b=a+3 in b, z=x+y］［z］ 以上代码相比［x=1,y=let x=1,y=x+3 in y, z=x+y］［z］更易于理解。 在实际代码编写过程中，当运算过程中存在某个或某些复杂的变量重复调用时，采用let…in…结构嵌套let…in…或Record结构是一种高效的处理方式，并且代码易于识别、易于修改。 3.3.4令牌 在M语言中，令牌(token)是指标识符(identifier)、关键字(keyword)、文字(literal)、运算符(operator)或标点符号(punctuator),但用于分隔标记的空白和注释不是令牌。 3.3.5标识符 在M语言中，标识符有通用化标识符(例如，let…in…语句中的步骤名称，record结构中的字段名称)和带引号的标识符(例如，let…in…语句中存在空格号的步骤名称，需对其加上#"")。 在M语言中，关键字是保留的类似标识符的字符序列，不能直接用作常规标识符，但可以用作带引号的标识符。 注意： 在M语言的Record结构中，当字段名称中存在关键字或空白时，不必使用带引号的标识符，可以直接用常规标识符。 3.3.6关键字 在M语言中，以下是系统内置的关键字。 and、as、each、else、error、false、if、in、is、let、meta、not、null、or、otherwise、section、shared、then、true、try、type、#binary、#date、#datetime、#datetimezone、#duration、#infinity、#nan、#sections、#shared、#table、#time。 所有关键字都必须小写，部分关键字前面需加上转义字符(#)。以is和as关键字为例，对关键字的作用进行简单说明，is运算符用于确定值的类型是否与给定类型兼容； as运算符用于检查该值是否与给定类型兼容，如果不兼容则会引发错误。否则将返回原始值。 以as关键字指定数据类型，代码如下： = let AB=(A as number, B as text) => Text.From(A)&B in AB 图321输入参数 运行此代码，返回的结果如图321所示。 注意： is和as运算符仅接受初始类型(Primitive)作为其正确的操作数。M语言不提供用于检查值是否符合自定义类型的方法。 另外，M语言的关键字里面没有for、while这样的关键字。在M语言中，实现循环是通过特定的函数或者运算符实现的，按照实现原理的不同，可以初步地分为遍历、迭代与递归三大类，例如，List.Transform()、List.TransformMany()、List,Accumulate()、List.Generate()等。 3.3.7标点符号 标点符号用于分组和分隔。在M语言中，常见的标点符号的中英文对照如表39所示。 表39常用标点符号 符号英 文 提 示中 文 含 义 ［ ］Bracket方括号 :Colon冒号 ,Comma逗号 =Equals sign 等号 ()Paren圆括号 +Plus加号 ""Quote 引号 ;Semicolon分号 Space空隔号 3.3.8空白分隔符 空格(空白分隔符)用于分隔 M 语言中的注释和令牌。空格包括空格字符(它是 Unicode 字符类的一部分)及水平和垂直制表符(#(tab))、换行符序列(包括回车符#(cr)、换行符#(lf)、后跟换行符的回车符#(lf,cr))等。制表符#(tab)、回车符#(cr)、换行符#(lf)等是较常用的字符转义序列。 转义序列也可以包含短(4个十六进制数字)或长(8个十六进制数字)Unicode 码位值。 例如，以下3个转义序列是等效的： #(000D) 短(4个十六进制数字)、#(0000000D) 长(8个十六进制数字)Unicode 码位值、#(cr) 字符转义序列。 单个转义序列中可以包含多个转义码，它们之间用逗号分隔。例如，#(cr)#(lf) 和 #(cr,lf)这两个序列是等效的。 3.4M语言表达式 表达式是由运算符和运算对象组成的，它用于构造值的公式。单独的一个运算对象(常量、变量或算术)也可作为表达式，它是最简单的一种表达式。常见的表达式有逻辑表达式、算术表达式、文本字符串表达式。 表达式可以是简单的表达式，也可以是复杂的表达式，复杂的表达式是建立在众多子表式的基础之上的。例如，［A=3, B={2}, C={if A>2 then 3 else null}&B］是父表达式，而3、{2}、{if A>2 then 3 else null}&B是子表达式。 3.4.1表达式 M语言中的各类表达式如表310所示。 表310M语言的表达式 序号表达式英 文 描 述运算符或关键字 1逻辑表达式logicalorexpression and、or、is、as、相等、关系、+、-、*、/、一元表达式(+、-、not) 2if表达式ifexpressionif…then…else… 3let表达式letexpressionlet…in… 4each表达式eachexpressioneach _ 5函数表达式functionexpressionfx=(x,y)=> 6主表达式 primaryexpression如表311所示 7报错表达式errorraisingexpressionerror expression 8错误处理表达式errorhandlingexpressiontry…otherwise… 在M语言中，常用主表达式如表311所示。 表311主表达式 序号主 表 达 式英 文 描 述主 运 算 符 1列表表达式 listexpression{x,y,…} 2项访问表达式 itemaccessexpressionx{y} 3记录表达式 recordexpression［i=x,…］ 4字段访问表达式 fieldaccessexpressionx［i］ 5标识符表达式 identifierexpressioni、@i 6带圆括号表达式 parenthesizedexpression(x) 7调用表达式 invokeexpressionx(…) 8文本表达式literalexpression "A123" 3.4.2逻辑表达式 1. and、or and和or是较常用的逻辑运算符。and运算符在两个条件都满足时，返回值为true； 只有一个条件满足时，返回值为false。应用举例，代码如下： = 2>3 and 5>4//false = 2>3 or 5>4　//true or运算符在两个条件中只要一个条件满足时就返回值true； 如果两个条件都不满足，则返回值为false。应用举例，代码如下： = 2>3 or 5>4//true = 2>3 or 4>5　//false 如果条件参数结果为true，则返回值为false。同理，如果条件参数结果为false，则返回值为true。应用举例，代码如下： = not false//true = not true //false 2. is、as is运算符并不真正执行转换，它只是检查指定的对象与给定的类型是否兼容。应用举例，判断数值3是否为文本值，代码如下： = 3 is text 返回的值为false。在M语言中，函数中包含Is的均为信息类函数，返回值为true或false。 as运算符只适用于引用类型或可以为null的类型，而无法执行其他的转换。应用举例，将自定义函数fn的a参数的数据类型定义为数值，代码如下： = let fn=(a as number)=>a in fn 当a值不是指定数据类型(数值类型)时，参数将无法被调用。 3. 相等运算符 比较运算符可用于相等(=、<>)或关系(>、>=、<、<=)的比较。以下是一些相等比较，代码如下： = 1=1,//true = 1.0=1 //true = "a" <> 2 //true = 2=1 //false = #nan=#nan //false = #nan<>#nan //true 返回的值为true或false。 参照表39，列表间比较是允许的。只要项相等、顺序相同，返回值就为true，代码如下： = {1,2}={1,2}//true = {2,1}={1,2} //false = {1,2,3}={1,2} //false 参照表39，对记录进行比较也是允许的。只有当字段数相同、字段名称相同时，返回值才为true，代码如下： = ［A=1,B=2］=［A=1,B=2］//true = ［B=2,A=1］=［A=1,B=2］ //true = ［A=1,B=2,C=3］=［A=1,B=2］ //false = ［A=1］=［A=1,B=2］ //false 参照表39，表与表之间的比较也是允许的。应用举例，代码如下： = #table({"A","B"},{{1,2},{3,4}}) =#table({"B","A"},{{4,3},{2,1} }) 返回的值为false。 如果列的顺序不同，但行列值能一一对应，则返回的值为true，代码如下： = #table({"A","B"},{{1,2},{3,4}}) =#table({"B","A"},{{2,1}, {4,3}}) 4. 关系表达式 关系表达式(>、>=、<、<=)返回的值为true或false，代码如下： = "a" > "A"　//true = "c" > "a" //true = "a" > "一"//false = "ab" >= "ac"　//false = 3>=2 //true = true > false//true = true < false//false 3.4.3if表达式 if表达式的语法结构如下： if if-condition then true-expression else false-expression if…then…else…的简单应用举例，代码如下： = if 5>4 then "ok" else "error" //ok if…then…else…的多条件应用举例，代码如下： =if 5>12 then "ok" else if 5>6 then "ok" else "no"//no 在刚接触M语言的多条件应用时，如果对语法不熟悉，则可以通过Power Query的图形化界面来操作。单击“添加列”→“条件列”，弹出的窗口如图322所示。 图322添加条件列 当需要添加多条件时，可通过单击“添加子句”获得条件行，在“Else IF、Then”中选择“列名、运算符”然后输入“值、输出”实现。 3.4.4let表达式 let…in…语句结构用于创建一个多步骤的综合查询，每个综合查询都以let开头、以in结尾； let、in是M语言中的关键字，只能是小写。在let..in..结构中，let用于计算，并对结果命名； in用于显示结果。 let之后所连接的每个步骤都有一个步骤名称，称为“标识符”“步骤名称”或“变量”。标识符用于引用值的名称，当标识符(步骤名称、变量)中存在空格时，需要用#标识符来包含空格(名称在引号中，例如，#"A B"，也可称为带引号的标识符)； 不带空格的标识符称为常规标识符(例如，A、B)。 在let…in…结构中，in之前不能有逗号，其他的每个步骤都以逗号结尾； in后面所接的是语句的输出，代码举例如下： //ch304-010 let 源 = 2, #"A B" = 源+3*(源+1) //#"A B"，用#标识符来包含空格(名称在引号中) in #"A B" 在以上代码中，源、#"A B"是对标识符的直接调用； //是注释符，用于单行注释，//后面的语句不执行任何操作。以上代码返回的值为11。 在let…in…结构中，in表达式返回的值可以是标识符(let…in…中的任一步骤)，也可以是标识符调用的结果(例如，A+B)，代码如下： //ch304-011 let A = 1, B = 2 in A+B 在以上代码中，A和B为标识符(或称步骤名称、变量)，A+B为标识符(或称步骤名称、变量)的调用。返回的值为3。 在let…in…结构中，如果in的前面存在逗号(，)，系统则会报错如下： Expression.SyntaxError: 逗号不能位于 In 之前。 以M语言中，/*……*/为多行注释，在“/*”与“*/”之间所编写的任何内容都不会被运行。在let…in…结构中，任何形式对标识符的调用其原理都是一样的，代码如下： //ch304-012 let A = 1, B = 2, C = A+B /* 语法注释： (1) A、B、C、A+B+C是标识符，也可称为"变量""步骤名称"。 (2) A = 1，1是表达式,A是变量； 变量是对值的命名。 (3) C=A+B, A+B是表达式,A和B是子表达式，C是变量。 (4) 在let…in…语句中，所有的这些变量构成了let表达式的环境。 (5) 环境中的每个变量在环境中都有一个唯一的名称，称为标识符。 (6) 如果尝试在同一环境中定义两个相同的变量，则系统会自动报错。 */ in A+B+C 以上代码返回的值为6。 在let…in…结构中，某let…in…结构可以将整个过程当成一个步骤进行嵌套，代码如下： //ch304-013 let C= let A = 1, B = 2 in A+B in C 返回的值为3。 在let…in…结构中，当某复杂变量需要在其他变量中反复调用时，相比let…in…中let…in…的嵌套，在let…in…结构中嵌套record结构是一种高效的处理方式，值得花时间去了解与掌握。 3.4.5each表达式 each表达式是M语言中的一种简写形式，声明一个名为_(下画线)的单形式参数的无类型函数，通常用于提高函数的可读性; 当_被调用时，什么类型的参数传递给了它，那么它就代表什么数据类型。在实际使用过程中，当多个each _被嵌套使用在同一表达式中时，为避免上下文冲突，有时会采用(x)=>x等形式来取代each _。 each是M语言中的关键字(只能小写)，代码如下： //ch304-014 let 源 = #table({"a","b"},{{1,3},{2,6}}), A =Table.AddColumn(源, "EACH", each _ ), B = Table.AddColumn(A, "小计", each ［a］+［b］) in B 图323添加列 在M语言中，字段前面的下画线是可以省略的。以上代码中each ［a］+［b］相当于each _［a］+_［b］，返回的值如图323所示。 注意： List中的下画线不能省略。 3.4.6函数表达式 在M语言中，函数主要有以下几种： (1) 内置函数，例如，Text.From()，它是系统自带的标准库函数。 (2) 自定义函数，自定义函数的基本语法为函数名=(参数1,参数2,参数3...)=>表达式。例如，= let fn = (x,y)=>x+y in fn，参数与表达式之间用=>隔开，这是固定组合。 (3) 参数函数，即函数内参数的类型为function，function的中文意思是“函数”。例如，函数List.Generate()的语法如下： List.Generate( initial as function, condition as function, next as function, optional selector asnullable function ) as list List.Generate()函数内的4个参数类型均为function。 3.4.7主表达式 1. 列表表达式 “列表”值是值的有序序列，代码如下： = {1..3} //{1,2,3} = List.Sum({1..3}) //6 = List.Transform({1..3}, each _*3)　//{3,6,9} = List.Select(List.Transform({1..3}, each _*3), each _>4) //{6,9} 列表表达式经常被放置于let…in…结构中。在以下代码中，标识符右侧的(=……)均为列表表达式，代码如下： //ch304-016 let A = {1..3}, B = {2..4}, C = {List.Sum(A)}&List.Select(B,each _>3) in A&B&C 返回的值为{1,2,3,2,3,4,64}。 2. 项访问表达式 项访问(itemaccess)是通过“位置索引运算符” ({ }) 按其数字索引访问列表中的项目，从列表或表中选择对应的值。“深化”是“项访问”的通俗说法，位置索引是从0开始的。从列表中深化第1个值，代码如下： = {1,3}{0} //从列表中深化第1个值 返回的值为1。 如果索引号大于列表长度(列表中的元素的个数)，则会返回错误，代码如下： = {1,3}{3} 返回的错误提示如下： Expression.Error: 枚举中没有足够的元素来完成该操作。 详细信息: ［List］ 对x{y}项访问求值时，为避免因为x或y的各类原因而引起的表达式错误(Expression.Error)，可采用x{y}？形式，将列表或表中x不存在的位置(或匹配项)y而引起的错误值转换为null值； 当然，如果存在多个y匹配项，仍会导致错误。x{y}?项访问应用举例，代码如下： = {1,3}{3}?//null = {"城市","排名","得分"}{0}?//"城市" = {1,［排名=2］,3}{1}? //［排名=2］ = {true,false}{2}? //null 在M语言中，值有原始值(Primitive Value，或称“基元值”)与结构化值(Structured Values)之分，列表中允许结构化值(列表、记录、表格)存在。从列表中选择对应的值，并且在列表中嵌套Record数据结构，代码如下： = {{1..6},3}{0}//从列表中深化第1个值 结果为{1,2,3,4,5,6}。如果需要深化该列表中的第3个值也是可以的，代码如下： = {{1..6},3}{0}{2}//多层深化 返回的值为3。 在Excel中，通过“数据→新建查询→从文件→从工作簿”获取AR005.xlsx工作簿，在弹出的“导航器”中选择“转换数据”，获取的数据如图324所示。 图324获取数据 从表中选择对应的值(例如，［Name］= "2020_3")，代码如下： = 源{2}//源，查询引用 通过项访问后返回的值为Record，如图325所示。 图325深化引用 表可以来自于工作簿，也可以来自于手动创建的表。对于手动创建的表的项访问也是可以的，代码如下： = #table( {"城市","排名","得分"}, { {"北京",1,95}, {"上海",2,93} } ){0} 关于表的创建原理可参见第9章。以上代码返回的值如图326所示。 图326行的深化引用 3. 记录表达式 “记录”是一组字段。字段是名称/值对，其中名称是字段记录中唯一的文本值。记录值的文本语法允许将名称写成不带引号的形式，这种形式称为“标识符”。 在以下代码中，A、B是记录中的字段，也可称为记录的标识符。应用举例，代码如下： ［ A = 1, //A字段 B = 2+3 //B字段 ］ 返回的值为Record。 在下面的代码中，字段B存在对字段A的引用，代码如下： ［ A = 1, B = A+3 ］ 返回的值为Record。假如A是一个复杂的表达式，采用以上Record结构的写法可以瞬间让代码变得简洁。 在M语言中，变量间依据依赖关系进行计算，应用举例： ［ A=B*2, B=C+3, C=1 ］ 返回的值为Record，与大多数读者的习惯写法［A=1,B=A+3, C=B*2］的返回值相同。 在Record记录中，字段字符间可以存在空格，但不能存在运算符。对于存在运算符的字段，必须采用引用标识符的方式，代码如下： ［ #"A+B" = A+B, A=1, B=2 ］ 返回的值为Record。 字段名称相同的两个记录是允许连接的。连接之后的结果为新值替换旧值，代码如下： ［ A=1 ］ & ［A=2］ 返回的值为［A=2］，其遵循的是数据处理过程中“无则新增、有则更改”的原则。 字段名称不同的两个记录也是允许连接的。连接的结果相当于记录的追加，代码如下： ［ A=1 ］ & ［B=2］ 返回的值为字段追加后的Record。 记录中嵌套记录或其他数据结构(列表、表格)都是允许的，代码如下： ［ A= ［x=1, y =2, z = x+y］, B= 2 ］ 返回的值为存在嵌套Record的Record。 4. 字段访问表达式 字段访问是对某一列的深化。使用运算符 x［y］ 按字段名称在记录中查找字段，代码如下： ［A=1,B=2］［B］ //2 ［A=1,B=2］［C］ //error ［A=1,B=2］［C］? //null 通过对记录中对应字段的访问返回字段对应的值； 计数从0开始，如果索引号大于列表长度(列表中元素的个数)，则会返回错误； 如果不想返回错误，则可采用x{y}？形式，将返回的错误值转换为null值。 运算符支持对多个字段进行集体访问，代码如下： ［A=1,B=2］［［B］］//［B=2］ ［A=1,B=2］［［C］］ //error ［A=1,B=2］［［B］,［C］］? //［B=2,C=null］ 在let…in…结构中，当某变量存在反复调用时，将其先定义好再反复调用不失是一个好方法。相比let…in…嵌套的方式，采用Record结构将会更为高效，代码如下： ［ A = ［j=11,k=1.1］, B = A［j］+A［k］ ］［B］ 返回的值为12.1。此用法使用频率较高，需重点掌握。 采用Record结构作为let表达式中的变量，然后供其他步骤的深化调用，代码如下： //ch304-029 let A = ［y=1,m=2,d=3］, B = ［j=2,k=3,l=4］ in A［y］+B［j］ 返回的值为3。 继续举例Record结构供其他步骤的深化调用，代码如下： //ch304-030 let A= ［x=1, y =2, z = x+y］, B= 2 in A［z］+B 返回的值为5。 为了形成子表达式的环境，新变量会与父环境中的变量进行“合并”，代码如下： ［ A = ［a=1,b=3,c = a+b］, B = 2, C = A［c］+B ］［C］ 返回的值为6。 对于列表型嵌套表达式，先对行索列深化引用再对列字段进行深化是允许的，代码如下： {［a=1］,3}{0}［a］ 返回的值为1。 对于记录嵌套表达式，先用字段对其他字段的值进行列表深化和字段深化，最后对记录的最后一个字段进行深化，代码如下： ［A = { ［a=1］ ,［a=3］}, c= A{0}［a］ + A{1}［a］ ］［c］ //［A = { ［a=1］ ,［b=3］}, c= A{0}［a］ + A{1}［b］ ］［c］ 列表索引值0和1分别代表列表中的第一项和第二项； 在M语言中，系统默认的索引值都是从0开始的，找不到的索引值均显示为-1。以上代码的返回值为4。 记录中的字段表达式为复杂型表达式也是允许的，代码如下： ［ A = { ［a=1］ , ［a=3］}, c= if A{0}［a］ >2 then A{0}［a］ else 0 + A{1}［a］ ］［c］ 返回的值为3。 以上代码可以进行简化，代码如下： ［ A = { ［a=1］ , ［a=3］}, c = A{0}［a］, d= if c >2 then c else 0 + A{1}［a］ ］［d］ 返回的值为3。 {［］}结构： 用于获取指定列的条件所在行的整行记录。例如，{［城市="北京"］}是以下代码中{0}的具体化，代码如下： = #table( {"城市","排名","得分"}, { {"北京",1,95}, {"上海",2,93}} ){［城市="北京"］} 返回的值为Record。 更多举例，代码如下： = #table({"城市","排名","得分"}, {{"北京",1,95},{"上海",2,93}}){［排名=1］} = #table({"城市","排名","得分"}, {{"北京",1,95},{"上海",2,93}}){［排名=2］} 以上两个表达式返回的值均为Record。 如果存在以下情形，则代码将报错提示，代码如下： = #table({"城市","排名","得分"}, {{"北京",1,95},{"上海",2,93}}){［排名=3］} //null,不存在y = #table({"城市","排名"}, {{"北京",1},{"上海",1}}){［排名=1］} //error，存在多个y 在查询中,直接字段访问用于获取表的整列或记录中字段的值，在项访问中嵌套的字段访问(获取指定列的条件所在行的整行记录)在表查询时可实现类似于Excel的vlookup功能，即“先项访问再列访问”或“先列访问再项访问”获取条件行与列交叉的记录。这些都是使用频率较高的，后续章节会继续举例说明。 5. 标识符表达式 标识符表达式用于引用环境中的变量，有两种方式： 专属标识符引用、包含标识符引用。最简单的方式是专属标识符引用，即在其他步骤上直接输入标识符(变量)的名称，达到引用的目的。另一种方式是包含标识符引用，采用“@标识符”方式。 “包含标识符引用”应用举例，代码如下： = ［fn =(x)=> if x<=1 then 1 else x*@fn(x-1),y=fn(5)］［y］ 代码中@是范围操作符，fn是标识符，@fn用于递归运算。返回的值为120。 以上代码采用let…in…结构也是允许的，代码如下： //ch304-039 let fn =(x)=> if x<=1 then 1 else x*@fn(x-1), y = fn(5) in y 运行代码，返回的值也为120。 6. 带圆括号表达式 当表达式中有用到and或or多条件判断时，可以带圆括号进行分组运算，通过圆括号分组及确定逻辑的优先级。应用举例，代码如下： = ("a" is text and 3>2) or (Number.From("12")>6 or Logical.From(2) = true)//true 带圆括号表达式可用于if表达式的条件中。应用举例，代码如下： = if ("a" is text and 3>2) or (Number.From("12")>6 or Logical.From(2) = true) then "Excel2016" else "2016"//Excel2016 或者用于四则运算中，用圆括号来确定计算的优先组。应用举例，代码如下： = (2+3)*4 //20 7. 调用表达式 “函数”是一个值，当带着参数进行调用时，将生成一个新值。应用举例，代码如下： = let fn = (x, y) => (x + y) / 2 in fn 运行代码，结果如图327所示。 图327输入参数 如果在x与y中不输入任何值就单击“调用”按钮，则生成的值为null； 如果在x中输入3，在y中输入9，单击“调用”按钮，则生成的值为6。同时在Power Query查询区会生成一个“调用的函数”，在编辑栏出现的代码为 = 查询1(3, 9)，如图328所示。 图328调用自定义函数 3.4.8报错表达式 错误是由运算符和函数遇到错误条件或使用了错误表达式导致的，可以使用try表达式来处理错误，也可以用error指示错误发生的原因。案例应用，代码如下： //ch304-042 let 一 = ［ A=1, B=16, C= if B<0 then error "错误提示： B为负值" else A+B ］, 二 = try Number.ToText(一［C］), 三 = "C值: = " & (if 二［HasError］ then 二［Error］［Message］ else 二［Value］ ) in 三 以上代码返回的值为“C值: = 17”。如果将变量“一”中的B值更改为-16，则返回的值为“C值: = 错误提示： B为负值”。 3.4.9报错处理表达式 try…otherwise…是较为常用的一个语句结构，类似于Excel中的iferror()函数。案例应用，代码如下： let A = 12, B="AB",C= try A+B otherwise null in C 在上述代码中，数值与文本是不能直接进行四则运算的，其结果一定会报错； 通过try发现错误后，通过otherwise将值指定为null，最终以上代码返回的值为null。