Expand Record contains different values

[Anonymous]

Hi,

After Expand the result.answers field i have diferent record results (the fields can be different for the records per item)

How can i expand these records.

let
    Bron = Json.Document(Web.Contents("https://api.aanmelder.nl/v1/events/" & EventID & "/regform/registrations/" & RegistrationID & "?limit=1000")),
    #"Geconverteerd naar tabel" = Table.FromRecords({Bron}),
    #"result uitgevouwen" = Table.ExpandRecordColumn(#"Geconverteerd naar tabel", "result", {"self", "uuid", "created", "last_modified", "email", "metadata", "registration_state", "registration_timestamp", "checkin_qr_code", "checkin_timestamp", "invoices", "answers"}, {"result.self", "result.uuid", "result.created", "result.last_modified", "result.email", "result.metadata", "result.registration_state", "result.registration_timestamp", "result.checkin_qr_code", "result.checkin_timestamp", "result.invoices", "result.answers"}),
    #"result.answers uitgevouwen" = Table.ExpandListColumn(#"result uitgevouwen", "result.answers"),
    #"result.answers uitgevouwen1" = Table.ExpandRecordColumn(#"result.answers uitgevouwen", "result.answers", {"id", "type", "intent", "metadata", "label", "label_text", "value"}, {"result.answers.id", "result.answers.type", "result.answers.intent", "result.answers.metadata", "result.answers.label", "result.answers.label_text", "result.answers.value"}),
    #"Fouten verwijderd" = Table.RemoveRowsWithErrors(#"result.answers uitgevouwen1", {"result.answers.value"})
in
    #"Fouten verwijderd"

Result = 4 columnsResult = 3 columns

thanks 

[v-jingzhang]

Hi @Anonymous 

Since each record item may have different fields, how do you want to expand them? Expand into multiple columns or multiple rows? No matter which there will be many null values generated into the table. If you only want some values from some records, you could expand specific record items to get the values you want, then combine them into a table.

Regards,
Community Support Team _ Jing

If this post helps, please Accept it as the solution to help other members find it.

[AlB]

Hi @Anonymous 

Can you provide the values for EventID and  RegistrationID so that the data loads through the API and a couple of tests can be run?

One option based on some random sample data. Place the following M code in a blank query to see the steps.

let
    rec1 = [field1 = 1, field2=2],  rec2 = [field3 = "x", field4="y"], list_ = {null, rec1, rec2, true}, //To build example input table 
    
    #"Example input table" = Table.FromList(list_, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
    
    #"Added Custom" = Table.AddColumn(#"Example input table", "Custom", each if Value.Is([Column1], type record) then [Column1] else [field0 = [Column1]]),
    #"Output table" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"field0", "field1", "field2", "field3", "field4"}, {"val0", "field1", "field2", "field3", "field4"})
in
    #"Output table"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.