Transform A List of Records into a Table

[matheusmelillo]

Hello,

Im kinda new to API sources in PowerBi and I'm sutck in the following situation:

I get from a API a JsonDocument with list of records with many lines:

Each of this records looks like this when opened:

I need to create a Final Table that has as columns the rows from Record column 1 ("codigo_associado", "nome_associado", ...) and and rows of that Final Table I need to get the corresponding value from all the Records from image 1.

Example of final table:

codigo_associado	nome_asscociado	...
185	name hidden	...
value from record 2	value from record 2	...

I've been trying different thing from the past 4 hours without success. Someone point me in the right direction for my M code, please!

[jgeddes · Accepted solution]

Have you tried Table.FromRecords?
With a 'Source' similar to 

Table.FromRecords(Source)

gets you...

If you only want specific fields from the records you can add a step after the source.

As an example choosing the first and third columns would end up with this example code...

let
    Source = 
    {
        [First="value from row 1 column 1", Second="value from row 1 column 2", Third="value from row 1 column 3", Forth="value from row 1 column 4"],
        [First="value from row 2 column 1", Second="value from row 2 column 2", Third="value from row 2 column 3", Forth="value from row 2 column 4"],
        [First="value from row 3 column 1", Second="value from row 3 column 2", Third="value from row 3 column 3", Forth="value from row 3 column 4"],
        [First="value from row 4 column 1", Second="value from row 4 column 2", Third="value from row 4 column 3", Forth="value from row 4 column 4"]
    },
    select_fields = List.Transform(Source, each Record.SelectFields(_, {"First", "Third"})),
    convert_to_table = Table.FromRecords(select_fields)
in
   convert_to_table

 end a result of...

[v-nmadadi-msft]

Hi @matheusmelillo,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Thanks and regards

[v-nmadadi-msft]

Hi @matheusmelillo,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-nmadadi-msft]

Hi @matheusmelillo,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[wdx223_Daniel · Accepted solution]

=Table.FromRecords(yourRecordList,Record.FieldNames(yourRecordList{0}),2)

[jgeddes · Accepted solution]

Have you tried Table.FromRecords?
With a 'Source' similar to 

Table.FromRecords(Source)

gets you...

If you only want specific fields from the records you can add a step after the source.

As an example choosing the first and third columns would end up with this example code...

let
    Source = 
    {
        [First="value from row 1 column 1", Second="value from row 1 column 2", Third="value from row 1 column 3", Forth="value from row 1 column 4"],
        [First="value from row 2 column 1", Second="value from row 2 column 2", Third="value from row 2 column 3", Forth="value from row 2 column 4"],
        [First="value from row 3 column 1", Second="value from row 3 column 2", Third="value from row 3 column 3", Forth="value from row 3 column 4"],
        [First="value from row 4 column 1", Second="value from row 4 column 2", Third="value from row 4 column 3", Forth="value from row 4 column 4"]
    },
    select_fields = List.Transform(Source, each Record.SelectFields(_, {"First", "Third"})),
    convert_to_table = Table.FromRecords(select_fields)
in
   convert_to_table

 end a result of...