Text.SplitAny 分开的函数 只适合文本 不能用list
= Table.AddColumn(自定义1, “A”,each Table.FromRows({List.Combine(List.Zip(Record.ToList([a=List.Select(Text.SplitAny([产品信息],字母),each _<>""),b=List.Select(Text.SplitAny([产品信息],Text.Combine(a)),each _<>"")])))}))
深化与查询
深化引用查询
1.担当vlookup
=Table.AddColumn(源,“部门”,each 某表{[业务员=[业务员]}[部门]})
其中的表可以是不同的表 也可以是在一张表中的不同操作步骤
动态引用多工作簿内容
1.深化多个工作簿
2.选择各个表中的列的汇总值进行汇总
合并一个文件夹中多个表格
= Table.Combine(Table.Combine(List.Transform(源,each Excel.Workbook(_,true,true)))[Data])
合并多个表中的相同位置的值采用深化即可。
合并多个表中的不同位置的列的方式
Record 可以将1列进行提取对应的列转换为Record然后展开成多列
=Table.TransformColumns(“表”,{‘Data’,each [数量={[型号=“合计”]}[商品数量],金额={[型号=“合计”]}[商品金额]]})
自定义函数
查询 当let中的语句只有一句话的时候 就不用加LET
累计求和
添加自定义列进行求和
当用传递行数的时候可以 设置为参数
累计求和中创建的索引列应该从1开始;
=Table.AddColumn(已添加索引,“累计求和”,each List.Sum(List.FirstN(已添加索引[销售业绩],[索引])))
传递函数:
Table.AddColunmn(已添加索引,“累计求和”,(x)=> List.Sum(List.FirstN(已添加索引[销售业绩],x[索引])))
构建Record
=Record.Combine({查询1,查询2}) =Record.Combine({[姓名=“万金发”],[年龄=35],[性别=“男”]]}) 必须为List 格式
Table.ToList
Record.FromList(源,{“title1”,“title2”})
Record.ToList(Record)
通过一个record 来获得列表的时候 的列字段 名称 必须是 Name Value
Record.FromTable
Table.Range 重新获取数据
Table.FromRecords({})
Table.ToRecords 为将title与每一条明细变成Title
Record.FormTable 为原表中有一列为Name列 一列为Value列
=Table.FromRecords(List.Transform({0…11},each Record.FromTable(Table.range(重命名的列,_*间隔数(8),获取几行(6))) 8
注意Table.Range
构建List
列表是没有字段标题的
List列表中可以嵌套其他列表元素
英文 中文 拼音的列
={“a”…“Z”}
=Text.ToList()
12345678 1
2
3
4
5
6
7
8
List.Combine
= List.Combine({ {1,2},{3,4}})
LIst 中的每一个为List的 才客户
列表
List
List
List
List.Positions 获取List中的位置关系
根据初始值,总项目数,步进递增量,返回指定项数的list数字列表
List.Date List.Times,List.DateTime List.Durations
List.Repeat
List.Reverse
构建Table
=#table({字段名称},{ {行值},{行值}})
注意是小写
List当中是Table 就可以用Table.Combine
Table.ToRows 拆分成一行的list 无字段
Table.FromRows从多个Rows的List进展开
=Table.FromRows(源,{字段Title})
Table.ToRecord
Table.FromRecords
Table.ToCoLumns
Table.ToList 将表中的内容设置为文本类型,然后进行合并
Table.FromList 将一列List中以逗号隔开的内容,转置为表格
注意,只能是逗号
=Table.FromList(源,each Text.Split(_,"/"),{字段标题},备选值)
Table.ToColumns
Table.FromColumns
其中的List为每个Column的值
三大容器构建宇宙
指定的列进行分组
Table.Group 分组 是将表格中的所有内容进行分组,如果需要将表格中的某列进行合并需要深化出来
=Table.Group(表,{“年级”},{“姓名”,each Text.Combine(_[姓名]," ")})
List.Zip
支持结构
=List.Zip({ {1,2},{3,4}})
Output
{1,3},{2,4}
了解函数语法及常用聚合函数
Table.Group 局部分组 和 全局分组
=Table.Group(table,{分组字段},{
{分组的列},{分组的列}},全局,大小写)
0 局部
1或者缺省为全部
其中全局为根据分组依据,将表中所有行进行汇总
局部分组为根据表格的上下文,进行分组 ,不是统计全部的表,相邻进行统计。
区分大小写 Comparer.Ordinal
Comparer.OrdinalIgnoreCase
Comparer.FromCulture
可以制作多列
List 聚合函数
Text.Combine
List.Product 乘法
List.Count
List.NonNullCount
List.Mode 众数
List .Modes
1
3
2
4
3
5
实现乘法
扩展分组套路
统计不同班级 同类科目中的第一名
=Table.Group(源,{“班级”},{“a”,each Table.Max(_,“语文”)})
=Table.Group(源,{“班级”},{“a”,each Table.First(Table.Sort(_,{“语文”,1}))})
=Table.First(_)
=Table.Sort(表,{“排序的列”,1(升序or降序)})
扩展分组套路-纵向分组
=Table.Group(源,{“品名”},{“统计”,each “订单数量:”&Text.From(Table.RowCount())&"#(lf)"&“订单金额:”&Text.From(List.Sum([金额])})
& 连接符 前后应该为同一格式?文本格式?
横向分组:
=Table.Group(源,{“品名”},{“统计”,each [订单数量=Table.RowCount(),销售金额=List.Sum([金额])]})
拓展分组——模拟分类汇总
=Table.Group(源,{“品名”,{“品名”,each Table.FromRows(TableToRows(_)&{ {={0}[品名]&“小计”,null,List.Sum([数量]),List.Sum([成本金额],List.Sum([销售金额])}}.Table.ColumnNames(源)})
拓展分组 统计连