Power Query基础知识

目录

M函数表达式

M函数基本变量类型 

​自定义函数

If表达式

​​​​​结构化数据 – 列表 

结构化数据 – 记录

结构化数据 – 表（1）

结构化数据 – 表（2）

​其他数据结构（1）

计算方式（1）

计算方式（2）

四、获取网页信息

获取网页信息思维过程

---

M函数表达式

M 函数的注释：

• 单行注释符为//
• 多行注释符为/*…..*/

M 函数基本表达式：

• let…in…结构：
• let用于封装计算结果，并为计算结果命名。
• in用于显示结果

例：

let

        Source = Text.Proper("hello world")

in

        Source

解说： 通过 let 语句将 Text.Proper(“hello world”) 函数的计算结

果封装到名叫 Source 的变量中，再通过 in 语句显示 Source 变

量的内容“ hello world ”。

M函数基本变量类型 

自定义函数

自定义函数：

( 参数 1 ，参数 2 ，参数 3……) => 函数运算表达式

※ 通过调用函数得到的函数返回值称为函数值

例：

(parameter1 as number, parameter2 as number) =>

let

        Final = (parameter1 + parameter2) / 2

in

        Final

 点击转换——到表中，就可以排序了

 

If表达式

if表达式通过对逻辑条件进行判断来对两个表达式进行选择

例：

if 2 > 1 then

        2 + 2

else if 2 = 1 then

        2 + 1

else

        1 + 1

(a as number)=>
let
    源 = if a>0 then "正数"
        else if a=0 then "零"
        else "负数"
in
    源

思考题

• 用自定义函数与判断语句，创建由三个参数构成的自定义函数,自定义函数用来选择做四则
• 运算中的哪种运算，并返回计算结果。
• 提示：前两个参数用来参与四则运算，第三个参数用来判断做哪种类型的计算
• 例：当第三个参数为1是做加法，为2时做减法，为3时做乘法，为4时做除法运算。除此之外
• 的参数值时返回“参数错误”提示

(a as number,b as number,c as number)=>
let
    源 = if c=1 then a+b
        else if c=2 then a-b
        else if c=3 then a*b
        else if c=4 then a/b
        else "参数错误"
in
    源

​​​​​结构化数据 – 列表 

列表 (List):

列表是扩在花括号中的一组数据，列表中每个数据都有属于自己的序号以便自己能够被检

索到，列表中的数据序号从 0 开始按照排列顺序依次整数递增，大列表内还可以嵌套子列表、

记录等。花括号除了用来括起列表内的所有数据还用来指定列表内数据的序号，通过指定数

据序号可以从列表内找到并获取所需的数据值。

 

结构化数据 – 记录

记录 (Record):

记录用来定义字段和给字段赋值，一个字段由字段名以及字段内的值组成，字段名是唯一

的文本值，是字段的标识符。字段名可以不用引号引用，字段名有两种表达形式：

> 不加 "" 的表达形式、例如 OrderID

> 加 # 和 "" 的表达形式、例如 #"Today's data is:"

记录中的内容写在 [] 括号内， [] 括号同样用于在记录中取特定字段的值。

例 1 ：定义字以及显示记录内所有内容

let Source =

        [

                OrderID = 1,

                #"CustomerID" = 1,

                Item = "Fishing rod",

                Price = 100.00

        ]

in Source

例 2 ：定义字以及显示记录内 [Item] 字段的值

let Source =

        [

                OrderID = 1,

                #"CustomerID" = 1,

                Item = "Fishing rod",

                Price = 100.00

           ]

in Source[#"CustomerID"] //equals 1

结构化数据 – 表（1）

表(Table):

表是由行列数据构成的，可以使用隐式或显示方式定义字段（列）的数据类型。使用

#table建表时，可以使用列表或者记录来定义列名，并使用嵌套列表来定义表中的数据，嵌

套列表的大列表内包含所有定义单行用的子列表，而每个子列表则用来定义一行数据。花括

号{}可以用来索引查找指定行的数据。

例 1 ：隐式字段表

let

Source = #table(

        {"OrderID", "CustomerID", "Item", "Price"},

        {

                {1, 1, "Fishing rod", 100.00},

                {2, 1, "1 lb. worms", 5.00}

        })

in Source

例 2 ：显示字段表

let

        Source = #table(

        type table [OrderID = number, CustomerID = number, Item =

        text, Price = number],

        {

                {1, 1, "Fishing rod", 100.00},

                {2, 1, "1 lb. worms", 5.00}

        }

)

in Source

结构化数据 – 表（2）

表(Table):

表是由行列数据构成的，可以使用隐式或显示方式定义字段（列）的数据类型。使用

#table建表时，可以使用列表或者记录来定义列名，并使用嵌套列表来定义表中的数据，嵌

套列表的大列表内包含所有定义单行用的子列表，而每个子列表则用来定义一行数据。花括

号{}可以用来索引查找指定行的数据。

例 3 ：建表后取表中第一行数据

let

        Source = #table(

        type table [OrderID = number, CustomerID = number, Item =

        text, Price = number],

        {

                {1, 1, "Fishing rod", 100.00},

                {2, 1, "1 lb. worms", 5.00}

        }

        )

in Source{1}

• Source{1} 取出来的是 {2, 1, "1 lb. worms", 5.00}

其他数据结构（1）

 数据结构中可以包含任意M值

let

Source =

{

        1, //数值

        "Bob", //文本

        DateTime.ToText(DateTime.LocalNow(), "yyyy-MM-dd"), //函数值

        [OrderID = 1, CustomerID = 1, Item = "Fishing rod", Price = 100.0] //记录

}

in  Source

例 2 ：包含子列表的记录

let

Source = [CustomerID = 1, Name = "Bob", Phone = "123-4567", Orders =

        {

                [OrderID = 1, CustomerID = 1, Item = "Fishing rod", Price = 100.0],

                [OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0]

        }]

in Source

 

 

•  该例子中要查出price=100的价格的代码

let
Source = [CustomerID = 1, Name = "Bob", Phone = "123-4567", Orders =
        {
                [OrderID = 1, CustomerID = 1, Item = "Fishing rod", Price = 100.0],
                [OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0]
        }],
    Orders = Source[Orders]

in
    Orders{0}[Price]

练习：建立如下表格

let
    Source = #table(type table[订单ID=number,销售人员Id=text,订单金额=number,销售人员表=table],
    {
        {1,"a",100,{"a","赵大"}},
        {2,"b",200,{"b","王二"}},
        {3,"c",300,{"c","张三"}}

    }

    )
in
    Source

let
    源 = [
        订单ID=2,销售人员ID="b",订单金额=200,
        销售人员表=#table(
            {"销售人员ID","销售人员姓名"},
            {
                {"b","王二"}
            }
        )
    ]
in
    源

计算方式（1）

M 函数的计算方式与 Excel 单元格相互间引用计算的方式类似，其计算顺序是根据计算单元中

每个元素间的依赖关系决定的。例如类似下边 Excel 单元格间的相互引用计算：

在 M 函数中便可以写成如下形式，它与 Excel 单元格的区别在于 M 函数中的 A1 、 A2 、 A3 是记录

中的元素，而 Excel 中的 A1 、 A2 、 A3 是单元格地址。

例 1 ：记录中各元素间相互引用计算

[

        A1 = A2 * 2,

        A2 = A3 + 1,

        A3 = 1

]

计算方式（2）

在 M 函数中还可以进行更为复杂的元素间的引用计算。例如下边例子中的 #"Total Sales" 就是

引用同一个记录中嵌套的列表内的第一个记录内的 Total 字段，以及第二个记录内的 Total 字段，

将这两个值相加得到的。

例 2 ：复合嵌套记录内元素间的引用计算

例2：复合嵌套记录内元素间的引用计算
[ 
    Sales = 
    { 
        [ 
            Year = 2007, 
            FirstHalf = 1000, 
            SecondHalf = 1100, 
            Total = FirstHalf + SecondHalf
        ], 
        [ 
            Year = 2008, 
            FirstHalf = 1200, 
            SecondHalf = 1300, 
            Total = FirstHalf + SecondHalf
        ] 
    }, 
#"Total Sales" = Sales{0}[Total] + Sales{1}[Total] 
]

得出结果为4600

运算符

一个完整的计算表达式由计算数与运算符两部分组成，例如在计算表达式 1+2 中， 1 和 2 是计

算数而 + 就是运算符。 M 函数中运算符的含义有时取决于参与计算的计算数的数据类型，下

边是主要运算符的案例列表供大家参考。

四、获取网页信息

获取网页信息思维过程

• 确定要获取信息所在网站

北京空气质量指数,北京PM2.5实时查询 - 空气知音北京空气质量指数及PM2.5实时数据查询，全国空气质量地图及全国重点城市空气质量指数(AQI)排名每小时更新，Air Level提供http://air-level.com/air/beijing/ • 解析网址

• 明确应使用什么函数爬数

• 明确所要数据在网址全部数据中的什么位置

• 创建参数表或自定义函数，批量或灵活爬取所要信息

(cityname as text)=>
let
    源 = Web.BrowserContents("http://air-level.com/air/"&cityname),
    #"从 Html 中提取的表" = Html.Table(源, {{"Column1", "TD:nth-child(1)"}, {"Column2", "TD:nth-child(2)"}, {"Column3", "TD:nth-child(3)"}, {"Column4", "TD:nth-child(4)"}, {"Column5", "TD:nth-child(5)"}, {"Column6", "TD:nth-child(6)"}, {"Column7", "TD:nth-child(4) *"}, {"Column8", "TD:nth-child(5) *"}}, [RowSelector="TR + *"]),
    更改的类型 = Table.TransformColumnTypes(#"从 Html 中提取的表",{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", Int64.Type}, {"Column8", Int64.Type}})
in
    更改的类型

就可以查询到对应城市的数据了 

北京空气质量指数： 北京空气质量指数,北京PM2.5实时查询 - 空气知音北京空气质量指数及PM2.5实时数据查询，全国空气质量地图及全国重点城市空气质量指数(AQI)排名每小时更新，Air Level提供http://air-level.com/air/beijing/

 思考题

用自定义函数爬取NBA指定球队名称下的球队常规赛历史数据

提示：用文本型参数指定球队英文简称

常规赛历史数据网址： https://china.nba.com/teamindex/

解析网址：

能获取球队赛场数据的网址https://china.nba.com/teams/schedule/#!/hawkshttps://china.nba.com/teams/schedule/#!/hawks 前面是固定部分，最后hawks是球队名

(teamname as text)=>
let
    源 = Web.BrowserContents("https://china.nba.com/teams/schedule/#!/"&teamname),
    #"从 Html 中提取的表" = Html.Table(源, {{"Column1", ".nba-stat-table__overflow .rank"}, {"Column2", ".rank + *"}, {"Column3", ".nobr:nth-child(4)"}, {"Column4", ".nobr:nth-child(6)"}, {"Column5", ".nobr:nth-child(7)"}, {"Column6", ".ng-hide.base"}, {"Column7", ".ng-scope.ng-isolate-scope"}, {"Column8", ".nobr:nth-child(5) .ng-binding:nth-child(1)"}, {"Column9", "SPAN SPAN + *"}, {"Column10", ".leaguepass-icon + *"}}, [RowSelector=".nba-stat-table__overflow .rank"]),
    提升的标题 = Table.PromoteHeaders(#"从 Html 中提取的表", [PromoteAllScalars=true]),
    更改的类型 = Table.TransformColumnTypes(提升的标题,{{"日期", type text}, {"时间", type text}, {"结果", type text}, {"地点", type text}, {"转播", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", Int64.Type}, {"Column9", Int64.Type}, {"Column10", type text}})
in
    更改的类型