我正在 Microsoft 365 的 Excel 中使用 Power Query。
我有以下源数据：
我正在尝试确定所需的转换以获得以下期望结果：
源数据在单元格中具有数据层次结构Col2。我想将数据保留在第一个层次结构中Col2，我想创建单独的列（Col2.1，Col2.2，...，列2.N) 用于第二层及后续层级。
单元格中的各个值由换行符 (＃（如果）)，给定值的层次级别由其缩进级别决定（每个缩进级别由三个空格字符表示）。
给定值在层次结构中的父级由顺序决定。例如，String1.2位于第二缩进级别。因此，仅基于缩进级别，它可以按层次结构放置在 或 下String1。String2但是，由于它出现在 之后String1和 之前String2，因此必须按层次结构放置在 下String1。
请注意细绳* 我选择的名称是经过净化的名称，是为了说明我想要实现的目标而挑选的。我的实际数据没有可预测的前导前缀，并且其词汇内容不暗示层次结构。因此，拆分逻辑不能基于值本身。它必须仅基于值的缩进级别和相对于其他值及其缩进级别的顺序。
这是我目前拥有的 M 语言代码：
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Col2", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Col2")
in
#"Split Column by Delimiter"
此 M 语言代码产生以下实际结果：
接下来显示源数据/预期结果的更多示例。
示例 1 源数据
示例 1 期望结果
示例 2 源数据
示例 2 期望结果
示例 3 源数据
示例 3 期望结果
示例 4 源数据
示例 4 期望结果
示例 5 源数据
示例 5 期望结果
示例 6 源数据
示例 6 期望结果
示例 7 源数据
示例 7 期望结果
示例 8 源数据
示例 8 期望结果
示例 9 源数据
示例 9 期望结果
示例 9 实际结果
我必须在 M 语言代码中添加哪些其他应用步骤才能将给定的源数据示例转换为上面显示的所需结果？
答案1
根据您更新的问题陈述进行编辑。
M 代码
let
#"Table to Process" = "Table" & Excel.CurrentWorkbook(){[Name="Table_to_Process"]}[Content][Column1]{0},
Source = Excel.CurrentWorkbook(){[Name=#"Table to Process"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}}),
//Special Case: Replace Nulls in Col1
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",null," ",Replacer.ReplaceValue,{"Col1"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "Segments",
each try Text.Split([Col2],"#(lf)")
otherwise null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Col2"}),
#"Expanded Segments" = Table.ExpandListColumn(#"Removed Columns", "Segments"),
//Determine Column by number of spaces at the start of each Segment name
//Add Offset Col to determine the row
// Trim the Segments
#"Add Col" = Table.AddColumn(#"Expanded Segments", "Col", each (Text.Length([Segments]) - Text.Length(Text.TrimStart([Segments]))) / 3 ?? 0),
#"Add Offset Col" = Table.FromColumns(
Table.ToColumns(#"Add Col") &
{{null} & List.RemoveLastN(#"Add Col"[Col])},
type table[Col1=text, Segments=text,Col=Int64.Type, Offset Col=Int64.Type]),
#"Trimmed Text" = Table.TransformColumns(#"Add Offset Col",{{"Segments", Text.Trim, type text}}),
//Determine Row
#"Added Index" = Table.AddIndexColumn(#"Trimmed Text", "Index", 0, 1, Int64.Type),
#"Add Row" = Table.AddColumn(#"Added Index", "Row", each
if [Offset Col] = null or [Offset Col] >= [Col]
then [Index] else null, Int64.Type),
#"Removed Columns1" = Table.RemoveColumns(#"Add Row",{"Offset Col", "Index"}),
#"Filled Down" = Table.FillDown(#"Removed Columns1",{"Row"}),
//Column Names for Segments
cn = List.Transform(List.Numbers(0, List.Max(#"Filled Down"[Col])+1), each
if _ = 0 then "Col2"
else Number.ToText(_,"'Col2.'0")),
//Group by Row
#"Group Rows" = Table.Group(#"Filled Down",{"Row"},{
{"rw", (t)=>
let
rr = Table.RemoveColumns(t,"Row"),
colNames = Table.TransformColumns(rr,{
{"Col", each if _ = 0
then "Col2"
else Number.ToText(_,"'Col2.'0"),
type text}}),
#"Convert to Row" = Record.FromList(colNames[Segments],colNames[Col]),
#"First Col" = Record.ReorderFields(
Record.AddField(#"Convert to Row","Col1",colNames[Col1]{0}),{"Col1"} & cn,2)
in
#"First Col", type record}
}),
#"Removed Row Index" = Table.RemoveColumns(#"Group Rows",{"Row"}),
//Fill in the blank entries
#"Offset Columns Count" = Table.RowCount(#"Removed Row Index")-1,
#"Add Offset rws" =
Table.FromColumns(
List.Accumulate(
List.Numbers(1,#"Offset Columns Count"),
Table.ToColumns(#"Removed Row Index"),
(s,c)=> s &
{List.Repeat({null},c) & List.RemoveLastN(#"Removed Row Index"[rw],c)})),
#"Fill Nulls" = Table.AddColumn(#"Add Offset rws", "Fill in Nulls", each
let
L = Record.ToList([Column1]),
lastNonNull = List.PositionOfAny(L,List.Select(L,each _ <> null),Occurrence.Last),
nullPos = List.PositionOfAny(L,List.Select(L,each _ = null),Occurrence.All),
nullPosToFill = List.Select(nullPos, each _ < lastNonNull),
fillCol = List.Accumulate(nullPosToFill,{},(s,c)=> s & {Record.FieldNames([Column1]){c}}),
fillRec = List.Accumulate(fillCol,[Column1],(s,c)=>
Record.TransformFields(s, {c,(x)=>
if Record.Field([Column2],c) <> null
then Record.Field([Column2],c)
else if Record.Field([Column3],c) <> null
then Record.Field([Column3],c)
else if Record.Field([Column4],c) <> null
then Record.Field([Column4],c)
else null
})
)
in
fillRec, type record),
#"Removed Columns2" = Table.RemoveColumns(#"Fill Nulls",List.Select(Table.ColumnNames(#"Fill Nulls"), each Text.StartsWith(_,"Column"))),
#"Expanded Fill in Nulls" = Table.ExpandRecordColumn(#"Removed Columns2", "Fill in Nulls", {"Col1"} & cn ),
//Set data types to text
#"Type As Text" =
Table.TransformColumnTypes(#"Expanded Fill in Nulls",
List.Transform({"Col1"} & cn, each {_, type text}))
in
#"Type As Text"
数据样本
结果
