主页 / dba / 问题 / 232767
Accepted
Moses
Asked: 2019-03-22 09:01:04 +0800 CST

如何将多个键值对提取到不同的列中

  • 772

我有一个不受我控制的数据库，并且正在使用 Power BI 从中提取数据以进行报告。数据库是网站上的页面列表，其中每个页面都是一个新行，并且列都有关于页面的元数据（创建者、创建者等）。除了并非所有元数据都存储在不同的列中外，还有一个“主要”和“次要”列存储多个键值对。例如：

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

元数据甚至比上面更复杂，其中包含可能出现在主列或辅助列中的几个其他键。但根本问题是，如何使用 Power BI 提取这些键值对，以便我的最终表将每个键作为唯一列，并将其值的所有实例跨 Primary/Secondary 作为列表，类似于以下内容：

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

1 个回答

  1. Best Answer

    这可以使用几行 Power Query 来实现：

    1. 使用 Table.ReplaceValue 删除外括号（“[”和“]”）
    2. 使用 Table.AddColumns & Text.Combine 合并 [Primary] & [Secondary] 列
    3. 使用 Table.ExpandListColumn、Table.TransformColumns 和 Splitter.SplitTextByDelimiter 使用键值对分隔符 (",") 将新合并列拆分为行
    4. 使用 Table.SplitColumn 使用键值分隔符 (":") 将新的 [KeyValuePairs] 列拆分为单独的列
    5. 通过 [PageName]、[CreatedBy] 和 [Key] 创建组，同时使用 Table.Group 和 Text.Combine 连接 [Value] 列
    6. 使用 Table.Pivot 通过 [Key] 为 [KeyValues] 透视表

    这是我使用此方法构建的 Power Query：

    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

相关问题