如何将嵌套 JSON 有效负载的最深层元素转换为 Power Query 中的单独行?

本文介绍了如何将嵌套 JSON 有效负载的最深层元素转换为 Power Query 中的单独行?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

目标:我有一个 JSON 有效负载，格式如下:

Goal: I have a JSON payload with the following format:

{ "Values": [ { "Details": { "14342": { "2016-06-07T00:00:00": { "Value": 99.62, "Count": 7186 }, "2016-06-08T00:00:00": { "Value": 99.73, "Count": 7492 } }, "14362": { "2016-06-07T00:00:00": { "Value": 97.55, "Count": 1879 }, "2016-06-08T00:00:00": { "Value": 92.68, "Count": 355 } } }, "Key": "query5570027", "Total": 0.0 }, { "Details": { "14342": { "2016-06-07T00:00:00": { "Value": 0.0, "Count": 1018 }, "2016-06-08T00:00:00": { "Value": 0.0, "Count": 1227 } } }, "Key": "query4004194", "Total": 0.0 } ], "LatencyInMinute": 0.0 }

我想在 PowerBI 中加载它并生成如下表格:

I want to load this in PowerBI and produce a table like so:

请注意每个 Value + Count 对都有自己的行，并且一些元素是重复的.

Notice how each Value + Count pair has its own row and some elements are repeated.

问题:当我尝试在 Power BI 中执行此操作(通过 Power Query)时，我得到三个初始列，其中之一是详细信息.麻烦的是我可以展开详细信息，但我只是得到更多的列，我真正想要的是行.我尝试了转置、旋转列等，但没有任何帮助.Power Query 将嵌套数据元素视为列名，这加剧了我的麻烦.

Problem: When I try to do this in Power BI (via Power Query), I get three initial columns, one of which is Details. Trouble is that I can expand Details, but I just get more columns, where what I really want is rows. I tried transpose, pivoting columns, and such but nothing helped. My troubles are exacerbated by Power Query treating the nested data elements as column names.

问题:有没有办法在 M 中将此嵌套的 JSON 负载转换为我上面说明的表格示例?

Question: Is there a way, in M, to convert this nested JSON payload to the table example I illustrated above?

推荐答案

Chris Webb 编写了一个递归函数来扩展所有表类型的列 - 我已经成功地将它克隆为记录类型的列:

Chris Webb wrote a recursive function to expand all table-type columns - I've managed to clone it for record-type columns:

gist.github/Mike-Honey/0a252edf66c3c486b69b