Início / computer / Perguntas / 1810937
Accepted
Dave
Asked: 2023-10-02 23:50:17 +0800 CST

Power Query: divisão de células multiníveis

  • 772

Estou usando o Power Query no Excel para Microsoft 365.

Eu tenho os seguintes dados de origem:

Dados de origem

Estou tentando determinar as transformações necessárias para obter o seguinte resultado desejado:

Resultado desejado

Os dados de origem possuem uma hierarquia de dados nas células de Col2 . Gostaria de manter os dados no primeiro nível de hierarquia em Col2 e gostaria de criar colunas separadas ( Col2.1 , Col2.2 , ..., Col2.N ) para o segundo nível de hierarquia e subsequentes.

Os valores individuais na célula são separados por um avanço de linha ( #(lf) ), e o nível de hierarquia de um determinado valor é determinado pelo seu nível de recuo (cada nível de recuo é representado por três caracteres de espaço).

O pai de um determinado valor na hierarquia é determinado por ordem. Por exemplo, String1.2está no segundo nível de recuo. Com base apenas no nível de indentação, ele poderia, portanto, ser colocado hierarquicamente em String1ou String2. Porém, como aparece antes String1e depois String2, deve ser colocado hierarquicamente abaixo String1.

Observe que os nomes String * que escolhi são nomes higienizados e foram escolhidos para ilustrar o que estou tentando realizar. Meus dados reais não possuem prefixos iniciais previsíveis e não implicam uma hierarquia por seu conteúdo lexical. Portanto, a lógica de divisão não deve basear-se nos próprios valores. Deve basear-se apenas no nível de recuo de um valor e na ordem relativa a outros valores e seus níveis de recuo.

Aqui está o código da linguagem M que tenho até agora:

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"

Este código da linguagem M produz este resultado real:

Resultado atual

Exemplos adicionais de dados de origem/resultados desejados são mostrados a seguir.

Exemplo 1 Dados de origem
Fonte 1

Exemplo 1 Resultado Desejado
Desejado 1

Exemplo 2 Dados de origem
Fonte 2

Exemplo 2 Resultado Desejado
Desejado 2

Exemplo 3 Dados de origem
Fonte 3

Exemplo 3 Resultado Desejado
Desejado 3

Exemplo 4 Dados de origem
Fonte 4

Exemplo 4 Resultado Desejado
Desejado 4

Exemplo 5 Dados de origem
Fonte 5

Exemplo 5 Resultado Desejado
Desejado 5

Exemplo 6 Dados de origem
Fonte 6

Exemplo 6 Resultado Desejado
Desejado 6

Exemplo 7 Dados de origem
Fonte 7

Exemplo 7 Resultado Desejado
Desejado 7

Exemplo 8 Dados de origem
Fonte 8

Exemplo 8 Resultado Desejado
Desejado 8

Exemplo 9 Dados de origem
Fonte 9

Exemplo 9 Resultado Desejado
Desejado 9

Exemplo 9 Resultado Real
Real 9

Que etapas aplicadas adicionais devo adicionar ao meu código da linguagem M para transformar os exemplos de dados de origem fornecidos nos resultados desejados mostrados acima?

microsoft-excel

1 respostas

  1. Best Answer

    Editado de acordo com suas declarações de problemas atualizadas.

    Código 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"

    Amostra de dados
    insira a descrição da imagem aqui

    Resultado
    insira a descrição da imagem aqui

    • 1

relate perguntas