Table.SingleRow()可以抽象地概括为function(table as table) as record,意思为如果作为参数的表格只有一行,该函数会使这个表格退化为记录。这个函数主要用于检测多对一模型的多边(many side)是否仅对应一边(one side)的一行(如果不是,这个模型应该理解为是多对多)。
接下来将会108式的模糊匹配说明Table.SingleRow()的用法,如果对该问题的背景不太了解,请自行回顾该文章。
使用108式的模糊匹配套路:
let AddCol= Table.AddColumn( Address, "Fee", each Table.SelectRows( Fee, ( x ) ⇒ Text.StartsWith( [地址], x[城市] ) ), type table ), Expansion= Table.ExpandTableColumn( AddCol, "Fee", {"运费"}, {"运费"} ) in Expansion
不难发现,上海外滩的运费出现了两行。如果出现这种情况是不正常的但又经常发生,在数据比较多不适宜人工勘误的情况下系统的报错就很重要。以下是最容易想到的方法:
let AddCol= Table.AddColumn( Address, "Fee", each Table.SelectRows( Fee, ( x ) ⇒ Text.StartsWith( [地址], x[城市] ) ), type table ), ERaiser= Table.TransformColumns( AddCol, { "Fee", each if Table.RowCount( _ ) = 1 then _[运费]{0} else error "Duplicated fees!" } ) in ERaiser
以上代码主要使用了Table.RowCount()对匹配得到的表格的行数进行检测,如果Table.RowCount()=1成立,通过两次深化取得运费,否则生成错误。
利用Table.SingleRow()以上代码可以化简为:
let AddCol= Table.AddColumn( Address, "Fee", each Table.SelectRows( Fee, ( x ) ⇒ Text.StartsWith( [地址], x[城市] ) ), type table ), ERaiser = Table.TransformColumns( AddCol, { "Fee", each Table.SingleRow( _ )[运费] } ) in ERaiser
如果通过模糊匹配得到的表格只有一行,只需要进行一次深化,否则直接报错。
如果重叠的区间是可能出现的, 使用只匹配下界的方法是无法通过使用Table.RowCount()或者Table.SingleRow()发现问题。假设现在需要根据参数表(ParaTable表格)为12个日期(Date表格)分类:
如果使用的是同时匹配上界和下界的方法,使用Table.SingleRow()是可以发现重叠的区间:
let Solution1= Table.AddColumn( Date, "MaturityBand", each Record.Field( Table.SingleRow( Table.SelectRows( ParaTable, ( x ) ⇒ x[StartDate] ⇐ [Date] and x[EndDate] ﹥= [Date] ) ), "MaturityBand" ), type text ) in Solution1
如果使用的是只匹配下界的思路,因为结果只会匹配到一行,Table.SingleRow()就没有办法发现问题:
let Solution4= Table.AddColumn( Date, "MaturityBand", each Record.Field( Table.SingleRow( Table.LastN( Table.RemoveLastN( ParaTable, ( x ) ⇒ x[StartDate] ﹥ [Date] ), 1 ) ), "MaturityBand" ), type text ) in Solution4
日期的区间匹配还有可能是先构造辅助表,然后再使用内连接进行匹配。如果是通过横向展开日期,辅助表可以这样构造:
let AddCol= Table.AddColumn( ParaTable, "TimeSeries", each List.Dates( [StartDate], Duration.TotalDays( [EndDate] - [StartDate] ) + 1, #duration(1,0,0,0) ), type list ), Expansion = Table.ExpandListColumn( AddCol, "TimeSeries" ), RemoveCols= Table.SelectColumns( Expansion, {"TimeSeries", "MaturityBand"} ), DataType= Table.TransformColumnTypes( RemoveCols, { {"TimeSeries", type date} } ) in DataType
然后使用以下代码进行匹配,
let NTable= Table.NestedJoin( Date, "Date", Mappingi, "TimeSeries", "NTable", JoinKind.Inner ), Outcome= Table.TransformColumns( NTable, { "NTable", each Record.Field( Table.SingleRow( _ ), "MaturityBand" ) } ) in Outcome
上图的结果说明,通过横向展开日期构造辅助表然后使用内连接的方法进行匹配的方法,是可以通过Table.SingleRow()发现重叠的区间。辅助表还可以纵向构造的,请阅读以下代码:
let DateList= Table.FromColumns( { List.Dates( ParaTable[StartDate]{0}, Duration.TotalDays( List.Last( ParaTable[EndDate] ) - ParaTable[StartDate]{0} ) + 1, #duration(1, 0, 0, 0) ) }, type table [ FullDate = date ] ), AddKey = Table.AddKey( DateList, {"FullDate"}, true ), LeftJoin= Table.NestedJoin( DateList, "FullDate", ParaTable, "StartDate", "NTable", JoinKind.LeftOuter ), Expansion= Table.ExpandTableColumn( LeftJoin, "NTable", {"MaturityBand"}, {"MaturityBand"} ), FillDown = Table.FillDown( Expansion,{"MaturityBand"} ) in FillDown
构造好辅助表后,就可以使用内连接进行匹配,以下为实现的代码:
let NTable= Table.NestedJoin( Date, "Date", Mappingii, "FullDate", "NTable", JoinKind.Inner ), Outcome= Table.TransformColumns( NTable, { "NTable", each Record.Field( Table.SingleRow( _ ), "MaturityBand" ) } ) in Outcome
使用这种方法与只使用下界进行匹配的结果是一样的,都是不能通过Table.SingleRow()发现重叠的区间,因为这两种方法都只考虑了下界。如果区间重叠的问题很可能出现,建议使用方法一和方法三完成匹配。
据说,使用Table.Combine()是比较消耗资源,如果需要上下合并的表格都只有一行,会不会通过Table.SingleRow()把这些表格退化为记录再使用Table.FromRecords()生产表格会得到更加好的效能?
使用108式的模糊匹配套路中筛选行的代码中 [城市] 前应加 x,附件里面带的有
谢谢你喜欢本案例,已纠错。