Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Fill Missing columns inside my records inside my list

Hello everyone, i didnt know how t owrite the title becasue i have a quite ocnfusing thing to ask

i have a function that extracts data from api

i return a json file and combined all data from each page to have one big table

after taht i get returned a list of records like that



however, each record doesnt have the same columns so when i transform this list to a table, i will get errors due to powerbi being confused on "missing fields"

how ccan i add those missing columns to each record with null without comprimising on speed

Need Help
  • m_dekorte's avatar
    m_dekorte
    2 years ago

    Hi Anonymous,

     

    You can use the MissingField type parameter, as demonstrated here:

    let
    Source = Table.FromRecords( 
        {
            [A = "a", B = "b"], 
            []
        }, {"A", "B"}, 
        MissingField.UseNull 
    )
in
    Source

     

    I hope this is helpful

3 Replies

  • m_dekorte's avatar
    m_dekorte
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    Hi Anonymous,

     

    You can use the MissingField type parameter, as demonstrated here:

    let
    Source = Table.FromRecords( 
        {
            [A = "a", B = "b"], 
            []
        }, {"A", "B"}, 
        MissingField.UseNull 
    )
in
    Source

     

    I hope this is helpful

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      is there a more dynamic way? i created alist of my distinct fields is there a way to incorporate it into the function step? especially since some records have 100 columns other have 50 and so on its a hassle to write all columns m_dekorte 

      • m_dekorte's avatar
        m_dekorte
        Icon for Resident Rockstar rankResident Rockstar
        2 years ago

        Hi Anonymous,

        Yes, of course but that's not free... You'll have to check if impact on performance remains acceptable. 
        Invoke this custom function on your list of records before transforming that into a table.

         

        let
    getFieldNames = (listOfRecords as list) as list => [
        noNulls = List.RemoveNulls( listOfRecords ),
        listFieldCount = List.Buffer( List.Transform(noNulls, Record.FieldCount)),
        maxCount = List.Max( listFieldCount),
        fieldNames = Record.FieldNames( listOfRecords{List.PositionOf(listFieldCount, maxCount)})
    ][fieldNames]
in
    getFieldNames

         

        You can insert this function into your query OR store it in a separate one, just name it: getFieldNames than the invocation can look like this:

        AllRecFieldNames =  getFieldNames( YourListOfRecords )

         

        This will function will return a list of field names for the record that contains most fields. Therefore I am assuming that all fields are like named but not always present.

         

        I hope this is helpful