Forum Discussion

matheusmelillo's avatar
matheusmelillo
New Member
1 year ago
Solved

Transform A List of Records into a Table

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_associadonome_asscociado...
185name hidden...
value from record 2value 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!



Need Help
  • jgeddes's avatar
    jgeddes
    1 year ago

    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...

     

5 Replies

  • jgeddes's avatar
    jgeddes
    Icon for Super User rankSuper User
    1 year ago

    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...

     

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    1 year ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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