主页 / computer / 问题 / 1810937
Accepted
Dave
Asked: 2023-10-02 23:50:17 +0800 CST

Power Query：拆分多层单元

  • 772

我在 Excel for Microsoft 365 中使用 Power Query。

我有以下源数据：

源数据

我正在尝试确定所需的转换以获得以下所需的结果：

期望的结果

源数据在Col2的单元格中具有数据层次结构。我想将数据保留在Col2的第一个层次结构级别，并且我想为第二个和后续层次结构级别创建单独的列（ Col2.1、Col2.2、...、Col2.N ）。

单元格中的各个值由换行符 ( #(lf) ) 分隔，给定值的层次结构级别由其缩进级别确定（每个缩进级别由三个空格字符表示）。

层次结构中给定值的父级由顺序确定。例如，String1.2位于第二个缩进级别。因此，仅基于缩进级别，它可以分层放置在 或String1下String2。但是，由于它出现在 之前String1和之前String2，因此必须按层次结构将其放置在 之下String1。

请注意，我选择的String * 名称是经过净化的名称，它们是为了说明我想要完成的任务而选择的。我的实际数据没有可预测的前导前缀，并且并不暗示其词汇内容的层次结构。因此，分裂逻辑不能基于值本身。它必须仅基于一个值的缩进级别以及相对于其他值及其缩进级别的顺序。

这是我到目前为止的 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

示例 1 期望的结果
所需 1

示例2 源数据
来源2

示例2 期望的结果
期望2

示例3 源数据
来源3

示例3期望的结果
期望 3

示例4 源数据
来源4

实施例4 期望的结果
期望 4

示例5 源数据
来源5

实施例5 期望的结果
期望 5

实施例6 源数据
来源6

实施例6 期望的结果
期望 6

实施例7 源数据
来源7

实施例7 期望的结果
期望 7

实施例8 源数据
来源8

实施例8 期望的结果
期望 8

实施例9 源数据
来源9

实施例9 期望的结果
期望 9

实施例9 实际结果
实际 9

我必须在 M 语言代码中添加哪些额外的应用步骤才能将给定的源数据示例转换为上面显示的所需结果？

microsoft-excel

1 个回答

  1. Best Answer

    根据您更新的问题陈述进行编辑。

    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"

    数据样本
    在此输入图像描述

    结果
    在此输入图像描述

    • 1

相关问题