如何將多個鍵值對提取到不同的列中

我有一個不受我控制的數據庫，並且正在使用 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 |

這可以使用幾行 Power Query 來實現：

1. 去掉外括號 ("$$ " & " $$") 使用 Table.ReplaceValue
2. 去$$ Primary $$&$$ Secondary $$使用 Table.AddColumns & Text.Combine 的列
3. 使用 Table.ExpandListColumn、Table.TransformColumns 和 Splitter.SplitTextByDelimiter 使用鍵值對分隔符 (",") 將新合併列拆分為行
4. 拆分新的$$ KeyValuePairs $$使用 Table.SplitColumn 使用鍵值分隔符 (":") 將列分成單獨的列
5. 通過以下方式創建群組$$ PageName $$,$$ CreatedBy $$, &$$ Key $$, 同時連接$$ Value $$使用 Table.Group 和 Text.Combine 的列
6. 將表格旋轉$$ Key $$為了$$ KeyValues $$使用 Table.Pivot

這是我使用此方法建構的 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"

引用自：https://dba.stackexchange.com/questions/232767