Início / computer / Perguntas / 1810276
Accepted
Dave
Asked: 2023-09-27 23:35:34 +0800 CST

Consulta avançada: dividindo células com vários valores em linhas sem criar todas as combinações possíveis

  • 772

Estou usando o Power Query no Excel para Microsoft 365.

Algumas das minhas colunas possuem células com vários valores. Preciso dividir essas células em linhas. Considere estes dados de origem:

Dados de origem

O código da linguagem M para meu Power Query é o seguinte;

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pet Type", type text}, {"Items Needed", type text}, {"Item Purpose", type text}, {"Typical Name", type text}}),
    #"Split Items Needed" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Items Needed", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Items Needed"),
    #"Split Item Purpose" = Table.ExpandListColumn(Table.TransformColumns(#"Split Items Needed", {{"Item Purpose", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Item Purpose")
in
    #"Split Item Purpose"

Isso resulta neste resultado real:

Resultado atual

Aqui está o resultado desejado:

Resultado desejado

Como devo ajustar meu Power Query para obter o resultado desejado?

microsoft-excel

1 respostas

  1. Best Answer

    O seguinte código da linguagem M resolverá este problema:

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type of All to Text" = Table.TransformColumnTypes(Source,{{"Pet Type", type text}, {"Items Needed", type text}, {"Item Purpose", type text}, {"Typical Name", type text}}),
    #"Split Items Needed" = Table.AddColumn(#"Changed Type of All to Text", "Split Items Needed", each Text.Split([Items Needed],"#(lf)")),
    #"Split Item Purpose" = Table.AddColumn(#"Split Items Needed", "Split Item Purpose", each Text.Split([Item Purpose],"#(lf)")),
    #"Match Splits Step 1" = Table.AddColumn(#"Split Item Purpose", "Split Items", each Table.FromColumns({[Split Items Needed],[Split Item Purpose]})),
    #"Match Splits Step 2" = Table.ExpandTableColumn(#"Match Splits Step 1", "Split Items", {"Column1", "Column2"}, {"Items Needed.1", "Item Purpose.1"}),
    #"Changed New Columns to Text" = Table.TransformColumnTypes(#"Match Splits Step 2",{{"Items Needed.1", type text}, {"Item Purpose.1", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed New Columns to Text",{"Pet Type", "Typical Name", "Items Needed.1", "Item Purpose.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Items Needed.1", "Items Needed"}, {"Item Purpose.1", "Item Purpose"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Pet Type", "Items Needed", "Item Purpose", "Typical Name"})
in #"Reordered Columns"

    Fonte:
    https://stackoverflow.com/questions/62798233/splitting-multiple-multi-valued-cells-in-excel-into-rows

    Crédito e agradecimento a @horseyride pela resposta no link acima.

    Editar para adicionar a segunda solução

    A solução a seguir, também com crédito à pergunta vinculada acima, é bastante elegante porque funciona em qualquer número de colunas:

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source, List.Transform(Table.ColumnNames(Source), each {_, type text})),
    #"TableTransform" = Table.Combine(List.Transform(List.Transform(Table.ToRecords(#"Changed Type"), (x) => List.Transform(Record.ToList(x), each Text.Split(_, "#(lf)"))), each Table.FromColumns(_, Table.ColumnNames(#"Changed Type")))),
    #"Changed Type1" = Table.TransformColumnTypes(#"TableTransform", List.Transform(Table.ColumnNames(#"TableTransform"), each {_, type text})),
    #"Filled Down" = Table.FillDown(#"Changed Type1", Table.ColumnNames(#"Changed Type1"))
in #"Filled Down"
    • 0

relate perguntas