Power BI 系列 - M语言 -3 Excel自助分析限制行数量以及合并多个SQL 查询

场景介绍：

1.通过EXCEL 自助分析刷新数据时,需要限定用户行数,例如年月,  (注意点查询调优 见下方)

2.两个自定义T-SQL 需要合并,能不能在一个查询窗口能完成.

准备知识工作： 

编号	类别	内容	说明
1	Excel PowerBI	Power Pivot 模型 查询编辑	1.查询编辑就是Power BI 中查询窗口,即M语言编辑位置 2.Power Pivot 相当于PowerBI模型和数据窗口 3.Excel 把 Power Pivot 当成一个透视表进行分析,所以在Excel中可以插入本工作簿模型
2	M语言	加载本地Excel文件的表至模型中	M语言:   源 = Excel.CurrentWorkbook(){[Name="变量表"]}[Content]              其中Name="变量表" 指得是表名
3	M语言	合并两个数据源在一个查询里	源2= Table.Combine({源,源1}),  一般写t-sql 用union all , 但是可能存在两个不数据源时,需要合并时,并且不需要两个都查询都保存, 可以使用这样方法.
4	M语言	引用另一个查询作为过滤条件	例:each ([year] = 变量表[年月]{0}) and ([city] = 变量表[城市]{0} 其中 "变量表[年月]{0} " 相当于引用 查询表 "变量表" 字段年月 第一行

开始步聚:

1.在SQL server 创建事例表，可以相像这个表是数据量是比较大的表

select '2018' as year, '上海'  as city,1  as mount,'001' as OrderKey  into citysales
union all
select '2016' as year, '北京'  as city,1,'002'
union all
select '2017' as year, '上海'  as city,1  as mount,'003'
union all
select '2016' as year, '北京'  as city,2 ,'004'
union all
select '2018' as year, '北京'  as city,3,'005'

select * from citysales

２.在电子表格中创建表,命名是 "变量表", 菜单栏 切换至"Power Pivot"并且 点击"添加模型中"  至此本地表加载至模型中

 

 

重命名查询名称为 "变量表"

 

 此查询M语言

let
    源 = Excel.CurrentWorkbook(){[Name="变量表"]}[Content],
    更改的类型 = Table.TransformColumnTypes(源,{{"年月", type text}, {"城市", type text}})
in
    更改的类型

 3.新建一个SQL 查询,并且查询窗口中进行初步过滤  

 

在下方输 入T-SQL语句, 当然如果只需要一个表名,就不需要T-SQL, 这里演示作用,使用T-SQL. 语句  select * from citysales

对表进行筛选,M语句如下:  其中上海和 2018年是初步的一个筛选.

let
    源 = Sql.Database("localhost", "test", [Query="select * from citysales"]),
    筛选的行 = Table.SelectRows(源, each ([city] = "上海") and ([year] = "2018"))
in
    筛选的行

  

 

 

 

 4.替换过滤条件,使用表变量的值.

M语言如下

let
    源 = Sql.Database("localhost", "test", [Query="select * from citysales"]),
     /*筛选的行 = Table.SelectRows(源, each ([city] = "上海") and ([year] = "2018"))*/
    
     筛选的行 = Table.SelectRows(源, each ([city] = 变量表[城市]{0}) and ([year] = 变量表[年月]{0}))
in
    筛选的行

可以看到两个字段的值,被替换成表变量. 表变量又来自电子表格本身所以只要改变电子表格的数值,就能获得想要的数据.

 

电子表格中创建一个透视,使用本地模型,然后刷新数据

 

 

 

 

 5.对于刚才M语言 可能无法自动将它优为转换成  elect * from citysales where city=? and year=?  ,如果有直接from 表,有性能问题时,可以调整M语言

let

    电子表格 = Excel.CurrentWorkbook(){[Name="变量表"]}[Content],
    源 = Sql.Database("localhost", "test", [Query="select * from citysales
                                                    where city='"&电子表格[城市]{0}&"'  and year='"& Number.ToText(电子表格[年月]{0})&"'"

                                                 ])
in
    源

注意,接拼的时候, 不能直接   where city='"&表变量[城市]{0}&"'  因为这会导一个异常,PowerBI不允许这样做. 除非,你把电子表格源 写在M表达式,然后下一步再引用这源的第一行数据.

 

 7.对于在一个查询中如果要合并两个源,怎么弄,刚才那个例子说明,这时需要合并另一个数据集

M语言如下:

let

    电子表格 = Excel.CurrentWorkbook(){[Name="变量表"]}[Content],

    源 = Sql.Database("localhost", "test", [Query="select * from citysales
                                                    where city='"&电子表格[城市]{0}&"'  and year='"& Number.ToText(电子表格[年月]{0})&"'"

                                                 ]),

     
    源1 = Sql.Database("localhost", "test", [Query="select  top 1 * from citysales"]),                                              
                                                 
    源2= Table.Combine({源,源1})

in
    源2

 

使用Table.Combine 进行合并

结束:

下载地址 4-3M语言引用其它查询参数.zip

最终效果如下: