问题:
在最近的工作中,遇到要处理一批比较大的数据,使用了powerquery,但是由于原始数据量大,无法放到一个excel文件中,被分成了多个,在处理的时候选择了从文件夹获取数据的方式,处理后通过powerpivot进行了一系列的计算,发给同事后,发现修改文件夹路径是比较麻烦,通过百度,找到一些方法,本文做下记录,以便后期查看。
解决步骤:
1、新建查询-从文件-从文件夹
2、选择原始数据文件夹所在位置
3、加载后选择编辑
4、添加一个自定义列,列名随意,公式使用Excel.Workbook([Content]),注意严格区分大小写
5、选择新加的列,点击右键,删除其他列
6、点击新列的右边双向箭头,选择扩展Data
7、点击Data列的右上角,选择扩展你需要的列(如果数据量不大可以全部扩展)
8、将第一行提升为标题。
9、选择一列设置数据类型(多个文件合并会 把表头放到了内容里面,需要选择一列设置格式后通过删除错误值来删除表头数据,具体选择哪列,根据实际数据情况而定)
10、新建一个文件路径的表,转换为表后添加到查询中,注意表的名称
11、最关键的一步要创建一个函数,因为只有函数才会拥有动态变化的值。直接点击“新建查询-从其他源-空白查询”,创建一个空白查询,点击“高级编辑器”,删除里边的所有代码,把下面代码粘贴进去然后保存,并把该查询命名为“fnGetParameter”
//括号和等号加右箭头是函数的标志,括号里的是函数的参数,或者叫变量。如果把第一行拿掉,其实就是一个完整的查询。加上第一行就把这个查询封装成为一个函数了。
(ParameterName as text) =>
let
//获取刚才创建的表格
ParamSource = Excel.CurrentWorkbook(){[Name="文件位置"]}[Content],
//这一步其实是让参数表格的行可以无限扩充,不再局限于本例中的三行。
ParamRow = Table.SelectRows(ParamSource, each ([文件] = ParameterName)),
Value=
if Table.IsEmpty(ParamRow)=true
then null
else Record.Field(ParamRow{0},"路径")
in
Value
11、用高级编辑器查看查询,将
源 = Folder.Files("E:\*************\callList"),
替换为
源 = Folder.Files(fnGetParameter("callList")),
接下来就是一路保存和操作,最后把文件发给同事,同事只用在文件位置表格里面修改他电脑上路径即可使用。
对于从其他文件获取的数据,也可以使用同样的方法调用。
另外也可以使用powerquery的参数来定义文件路径
只是这种方法需要同时进去powerquery修改参数。
不知道“Parameter Table”确切翻译是什么,我直译为“参数表格”。参数表格的意思是:PowerQuery的参数可以根据用户输入来实现动态化。
目前我最常用到的场景是:当我将PowerQuery数据模板做好之后,其他不熟悉PowerQuery的伙伴只需要提供简单的输入——比如文件或文件夹路径——就可以自动生成结果。
场景再实例化一下:我做了一个学习报告模板,有各种复杂的计算。这个模板需要从某个系统导出三个原始表:学习报表、所有学员名单和课程信息表。如果这个模板只供我一个人使用,毫无问题;当我需要把这个模板提交给其他伙伴使用时,他们就需要自己从系统中导出三个表然后修改模板的PowerQuery代码,这将是一场灾难。避免灾难的方法是尽可能将模板代码封装,只让其他伙伴导出三个文件,将文件路径填写到Excel表格即可。这就是参数表格发挥作用的地方。
以下是操作步骤和代码:
1.首先在Excel中创建参数表格,这个表格只有两列:参数和值。因为这整个思路是我抄的英文资源的,所以我偷懒照搬英文列名:“Parameter”和“Value”。然后用插入表格或套用表格的方式,将区域转化成表格,记得将表格命名为“Parameters”【图1】。注意左上角的“表名称”。Value列就是需要用户输入的地方,在这个例子中我的同事只需要将Value列相应值修改为自己导出的三个文件的全路径就可以了。
2.然后新建一个查询,选择“从表格”创建【图2】。这一步是创建包含参数值的查询,便于后面从这个查询里引用参数值。因为PowerQuery是没法直接从表格引用值的。创建过程比较简单,一路默认和确定即可,不再截图演示。因为是从已经命名的表格创建的,所以查询名自动继承了表格名字“Parameters”。
3.现在要创建一个函数,因为只有函数才会拥有动态变化的值。直接点击“新建查询-从其他源-空白查询”,创建一个空白查询,点击“高级编辑器”,删除里边的所有代码,把下面代码粘贴进去然后保存,并把该查询命名为“fnGetParameter”(这也是直接照搬的)。
12345678910111213141516171819202122232425262728 | //括号和等号加右箭头是函数的标志,括号里的是函数的参数,或者叫变量。//如果把第一行拿掉,其实就是一个完整的查询。加上第一行就把这个查询封装成为一个函数了。 (ParameterName as text) => let //获取刚才创建的Parameters表格 ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content], //这一步其实是让参数表格的行可以无限扩充,不再局限于本例中的三行。//注意在“in”前的都是一条语句,并且有缩进,我这里偷懒没缩进。//整条语句蛮抽象,我自己也还没完全弄懂其内部的运作原理,直接照搬了。 ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)), Value= if Table.IsEmpty(ParamRow)=true then null else Record.Field(ParamRow{0},"Value") in Value |
//括号和等号加右箭头是函数的标志,括号里的是函数的参数,或者叫变量。//如果把第一行拿掉,其实就是一个完整的查询。加上第一行就把这个查询封装成为一个函数了。(ParameterName as text) =>let//获取刚才创建的Parameters表格ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],//这一步其实是让参数表格的行可以无限扩充,不再局限于本例中的三行。//注意在“in”前的都是一条语句,并且有缩进,我这里偷懒没缩进。//整条语句蛮抽象,我自己也还没完全弄懂其内部的运作原理,直接照搬了。ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)),Value=if Table.IsEmpty(ParamRow)=truethen nullelse Record.Field(ParamRow{0},"Value")inValue
4.接下来创建三个查询,分别是“学习报表”、“人员信息”和“课程信息”,然后对这三个查询进行各种复杂的Shaping也好,Calculating也好,然后加载到数据模型,然后根据需要添加计算列、计算字段,创建数据透视表、数据透视图乃至PowerView。十八般武艺样样上吧,做出一个真实的报告来就ok。具体步骤不赘述。
5.报告创建好之后,我要想让我的同事能够使用我的牛逼报告模板的关键一步来了:将上一步写死的源文件位置,替换成参数表格里的值。
将“学习报表”查询“let”后面的“源=……”替换成:
1 | 源 = Excel.Workbook(File.Contents(fnGetParameter("学习报表文件位置")), null, true), |
源 = Excel.Workbook(File.Contents(fnGetParameter("学习报表文件位置")), null, true),
将“人员信息”查询“let”后面的“源=……”替换成:
1 | 源 = Excel.Workbook(File.Contents(fnGetParameter("人员信息文件位置")), null, true), |
源 = Excel.Workbook(File.Contents(fnGetParameter("人员信息文件位置")), null, true),
将“课程信息”查询“let”后面的“源=……”替换成:
1 | 源 = Excel.Workbook(File.Contents(fnGetParameter("课程信息文件位置")), null, true), |
源 = Excel.Workbook(File.Contents(fnGetParameter("课程信息文件位置")), null, true),
6.为了便于同事识别,我将Parameters表所在的sheet名称重命名为“请修改”,并在表格区域外加上了使用这个模板的说明,这样他们一看就知道自己要干嘛【图3】。
7.保存该Excel文件,然后发给同事。
8.同事只需要导出三个对应的文件,保存在本地,然后打开此模板,分别填写三个文件的全路径信息,然后点击“刷新”,就获取到最新的数据了,保险起见,在数据透视表、数据透视图也刷新下吧。