另一个Power Query问答提供了一种解决方案,可以根据列字符计数宽度将字符分隔的文本文件拆分为列。
但它没有考虑到null。当遇到null值时,它会在右边的一列中显示错误。我不太清楚到底发生了什么。错误为
An error occurred in the ‘SplitText’ query. Expression.Error: The 'count' argument is out of range.
拆分功能的代码为:
let
SplitText = (text, lengths) =>
let
LengthsCount = List.Count(lengths),
// Keep track of the index in the lengths list and the position in the text to take the next characters from. Use this information to get the next segment and put it into a list.
Split = List.Generate(() => {0, 0}, each _{0} < LengthsCount, each {_{0} + 1, _{1} + lengths{_{0}}}, each Text.Range(text, _{1}, lengths{_{0}}))
in
Split,
// Convert the list to a record to
ListToRecord = (text, lengths) =>
let
List = SplitText(text, lengths),
Record = Record.FromList(List, List.Transform({1 .. List.Count(List)}, each Number.ToText(_)))
in
Record
in
ListToRecord
然后,在您的表中,添加一个使用以下公式的自定义列:
each SplitText([Column1], {4, 2, 5, 3})
我使用的是Excel 2010 64位和Power Query版本:2.29.4217.1861
如何修改此项以考虑null?
Split = List.Generate(() => {0, 0}, each _{0} < LengthsCount, each {_{0} + 1, _{1} + lengths{_{0}}}, each try Text.Range(text, _{1}, lengths{_{0}}) otherwise null)