Início / dba / Perguntas / 232767
Accepted
Moses
Asked: 2019-03-22 09:01:04 +0800 CST

Como extrair vários pares de valores-chave em colunas distintas

  • 772

Eu tenho um banco de dados que está fora do meu controle e estou extraindo dados dele usando o Power BI para geração de relatórios. O banco de dados é uma lista de páginas em um site, onde cada página é uma nova linha e todas as colunas possuem metadados referentes à página (criada por, criada por e assim por diante). Exceto que nem todos os metadados são armazenados em colunas distintas, há uma coluna "Primária" e "Secundária" que armazena vários pares de valores de chave. Por exemplo:

PageName | CreatedBy | Primary | Secondary 
page1    | Joe       | [owner:frank,topic:meals] | [topic:drinks]
page2    | Dale      | [owner:joe, topic:drinks, topic:meals] | [topic:appetizers]

Os metadados são ainda mais complexos do que acima com várias outras chaves que podem ocorrer na coluna Primária ou Secundária. Mas o problema principal é como posso extrair esses pares de valores de chave usando o Power BI para que minha tabela final tenha cada chave como uma coluna exclusiva com todas as instâncias de seus valores em Primário/Secundário como uma lista, semelhante a esta:

PageName | CreatedBy | Owner | Topic |
page1    | Joe       | frank | meals |
page2    | Dale      | joe   | drinks, meals, appetizers |
powerbi

1 respostas

  1. Best Answer

    Isso pode ser feito usando algumas linhas do Power Query:

    1. Remova os colchetes externos ("[" & "]") usando Table.ReplaceValue
    2. Mesclar colunas [Primária] e [Secundária] usando Table.AddColumns & Text.Combine
    3. Divida a nova coluna mesclada em linhas usando o delimitador de par de valores-chave ("",") usando Table.ExpandListColumn, Table.TransformColumns e Splitter.SplitTextByDelimiter
    4. Divida a nova coluna [KeyValuePairs] em colunas separadas usando o delimitador de valor de chave (":") usando Table.SplitColumn
    5. Crie grupos por [PageName], [CreatedBy] e [Key], enquanto concatena a coluna [Value] usando Table.Group & Text.Combine
    6. Gire a tabela por [Key] para os [KeyValues] usando Table.Pivot

    Aqui está o Power Query que construí usando este método:

    let
    Source = #table({"PageName", "CreatedBy", "Primary", "Secondary"}, {{"page1", "Joe", "[owner:frank,topic:meals]", "[topic:drinks]"}, {"page2", "Dale", "[owner:joe,topic:drinks,topic:meals]", "[topic:appetizers]"}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PageName", type text}, {"CreatedBy", type text}, {"Primary", type text}, {"Secondary", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","[","",Replacer.ReplaceText,{"Primary", "Secondary"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","]","",Replacer.ReplaceText,{"Primary", "Secondary"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Replaced Value1", "KeyValuePairs", each Text.Combine({[Primary], [Secondary]}, ","), type text),
    #"Removed Columns1" = Table.RemoveColumns(#"Inserted Merged Column",{"Primary", "Secondary"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Columns1", {{"KeyValuePairs", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "KeyValuePairs"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"KeyValuePairs", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "KeyValuePairs", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Key", "Value"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Key", type text}, {"Value", type text}}),
    //#"Grouped Rows" = Table.Group(#"Changed Type2", {"Index", "KeyValuePairs.1"}, {{"Values", each _, type table}})
    #"Grouped Rows" = Table.Group(#"Changed Type2", {"PageName", "CreatedBy", "Key"}, {{"KeyValues", each Text.Combine(_[Value], ", "), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Key]), "Key", "KeyValues")
in
    #"Pivoted Column"
    • 1

relate perguntas