如何在Power Query中将嵌套的JSON有效内容的最深层元素转换为单独的行? [英] How can I turn the deepest elements of nested JSON payload into individual rows in 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:
注意每个值+计数对如何有自己的行和某些元素重复。
Notice how each Value + Count pair has its own row and some elements are repeated.
问题:当我尝试在Power BI(通过Power Query)中执行此操作时,我得到了三个初始列,其中之一是详细信息 。麻烦的是我可以展开Details,但是我只会得到更多的列,而我真正想要的是行。我尝试转置,旋转列,但没有任何帮助。 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:
https://gist.github.com/ Mike-Honey / 0a252edf66c3c486b69b
这篇关于如何在Power Query中将嵌套的JSON有效内容的最深层元素转换为单独的行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!