Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Expand Record contains different values

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 

Need Help

2 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    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.

     

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    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.