Power Query扩展记录列表

huangapple go评论72阅读模式
英文:

Power Query expand list of records

问题

这是一个非常基本的问题,但我找不到答案。我正在使用Power Query编辑器,但其中大部分字段都是灰色的,我无法按照我想要的方式编辑表格。请参见下面的截图:

Power Query扩展记录列表

我的目标是展开各个记录,我已经看过一些YouTube视频,可以通过添加新列来实现,但显然该字段已经变成灰色。我在Power Query中输入的链接是:

https://api.sportsdata.io/v3/mlb/scores/json/TeamSeasonStats/2018?key=fa737459090340c0a435e6bf25b15baf

英文:

This is a very basic question but can't find an answer to this. I am using the power query editor but most of the fields in it are greyed out and I cannot edit the table as I want to. See below:

Power Query扩展记录列表

My objective here is to expand the individual records and I have already seen some youtube videos where this can be achieved by adding a new column but apparently that field has been greyed out. The link that I am entering in power query is this:

https://api.sportsdata.io/v3/mlb/scores/json/TeamSeasonStats/2018?key=fa737459090340c0a435e6bf25b15baf

答案1

得分: 1

你需要将“..”转换为表格以取消大多数选项的灰色,然后你可以展开。

以下是可以尝试粘贴到主页高级编辑器中的示例通用代码:

let Source = {[ a = 1, b = 2 ] , [ c = 3 ] , [ a = 1, b = 2, c = 3 ]},
    "Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error) ,
    ExpandList= List.Distinct(List.Combine(List.Transform(Table.Column( "Converted to Table", "Column1"), each if _ is record then Record.FieldNames(_) else {}))),
    Expand= Table.ExpandRecordColumn( "Converted to Table", "Column1", ExpandList,ExpandList)
in Expand

或者在你的具体情况下:

let Source = Json.Document(Web.Contents("https://api.sportsdata.io/v3/mlb/scores/json/TeamSeasonStats/2018?key=fa737459090340c0a435e6bf25b15baf")),
    "Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error) ,
    ExpandList= List.Distinct(List.Combine(List.Transform(Table.Column( "Converted to Table", "Column1"), each if _ is record then Record.FieldNames(_) else {}))),
    Expand= Table.ExpandRecordColumn( "Converted to Table", "Column1", ExpandList,ExpandList)
in Expand
英文:

You need to Transform .. To Table ... to ungrey most of the options
Then you can expand.

Sample generic code you can try out by pasting into home ... advanced editor...

let Source = {[ a = 1, b = 2 ] , [ c = 3 ] , [ a = 1, b = 2, c = 3 ]},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error) ,
ExpandList= List.Distinct(List.Combine(List.Transform(Table.Column( #"Converted to Table", "Column1"), each if _ is record then Record.FieldNames(_) else {}))),
Expand= Table.ExpandRecordColumn( #"Converted to Table", "Column1", ExpandList,ExpandList)
in Expand

Power Query扩展记录列表

Or in your specific case

let Source = Json.Document(Web.Contents("https://api.sportsdata.io/v3/mlb/scores/json/TeamSeasonStats/2018?key=fa737459090340c0a435e6bf25b15baf")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error) ,
ExpandList= List.Distinct(List.Combine(List.Transform(Table.Column( #"Converted to Table", "Column1"), each if _ is record then Record.FieldNames(_) else {}))),
Expand= Table.ExpandRecordColumn( #"Converted to Table", "Column1", ExpandList,ExpandList)
in Expand

Power Query扩展记录列表

huangapple
  • 本文由 发表于 2023年2月14日 04:34:13
  • 转载请务必保留本文链接:https://go.coder-hub.com/75440895.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定