CODEWITHSUNDEEP
excelpowerquery
Haider Bukhari
Haider Bukhari

Reputation: 3

Power Query expand list of records

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:

enter image description here

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

Upvotes: 0

Views: 2462

Answers (1)

horseyride
horseyride

Reputation: 21318

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

enter image description here

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

enter image description here

Upvotes: 1

Related Questions