时间戳

首页 > 解决方案 > Power BI：将来自无序 JSON 对象的查找值放在正确的列中

json - Power BI：将来自无序 JSON 对象的查找值放在正确的列中

问题描述

在 Power BI 中，我有一列包含 JSON。每行包含 3 个 key:value 对，格式为 3-digits:4-digits，如下例所示

表格1

{"567":"1259","568":"1535","570":"1264"}
{"393":"9521","392":"5351","394":"4621"}

每个 key:value 可用于在另一个表中查找条目：

表 2

Key | Value | Entry
-------------------
567 | 1259  | Apple
568 | 1535  | Large
570 | 1264  | Red 
393 | 9521  | Small
392 | 5351  | Pear
394 | 4621  | Green

我想创建 3 个新列附加到表 1，填充从表 2 中查找的条目，如下所示：

Fruit | Size  | Colour
----------------------
Apple | Large | Red
Pear  | Small | Green

我的首选解决方案是split通过分隔符将 JSON 列转换为 3 ,，然后merge将其与表 2 通过Key. 但是，由于 JSON 对象没有排序，因此第 1 列中的第一个键可能代表例如水果，而第 2 列中的第一个键可能代表例如大小。在此插图中，这将导致水果列中的大小值，这不是本意：

Fruit | Size  | Colour
----------------------
Apple | Large | Red
Small | Pear  | Green

值得庆幸的是，键值的顺序和它在我的表 2 数据中所代表的内容具有一致的模式。换句话说，具有最低值的键代表水果，具有次高值的键代表大小，具有最高值的键代表颜色。

因此，我认为一种解决方案可能是在 Power BI 中找到一种方法，以编程方式获取 JSON 列并通过升序键值对 JSON 内容进行排序。

示例输入：

{"393":"1259","392":"1535","394":"1264"}

期望的输出：

{"392": "1535","393":"1259","394": "1264"}

正如其他人指出的那样，这可能不是解决这个问题的最佳方法，所以我愿意接受建议。

我也知道这归因于糟糕的数据库设计，但我在这里！

标签： jsonpowerbidax

解决方案

感谢 mahoneypat在Power BI 社区论坛上回答了这个问题。我可以确认它有效。

我将他的答案粘贴在下面，因此 Stackoverflow 上的任何人都可以：

这是在查询编辑器中执行此操作的一种方法。要查看它是如何工作的，只需创建一个空白查询，打开高级编辑器并将那里的文本替换为下面的 M 代码。

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcvLEYAgDEXRXt6aheSHsRVCJY69C0G3Z+7tHXdArQWuQCX1QFlwblDWDe34CpPAg1FyZOdkV6rZsVPC/H6QBLFVzHG8", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [JsonData = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"JsonData", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Result", each let 
thisrecord = Json.Document([JsonData]),
sortedrecord = Record.ReorderFields(thisrecord, List.Sort(Record.FieldNames(thisrecord))),
renamedrecord = Record.RenameFields(sortedrecord, List.Zip({Record.FieldNames(sortedrecord), {"Fruit", "Size", "Color"}}))
in 
renamedrecord),
    #"Expanded Result" = Table.ExpandRecordColumn(#"Added Custom1", "Result", {"Fruit", "Size", "Color"}, {"Fruit", "Size", "Color"})
in
    #"Expanded Result"

关键步骤是使用此表达式的自定义列。将 [JsonData] 替换为带有 Json 字符串的实际列名。

let
thisrecord = Json.Document([JsonData]),
sortedrecord = Record.ReorderFields(thisrecord, List.Sort(Record.FieldNames(thisrecord))),
renamedrecord = Record.RenameFields(sortedrecord, List.Zip({Record.FieldNames(sortedrecord), {"Fruit", "Size", "Color"}}))
in
renamedrecord

推荐阅读