Forum Discussion

Sharkybu's avatar
Sharkybu
Helper II
2 years ago
Solved

Field names from multiple columns containing record

Hello amazing people of the Power Bi forum.

 

I'm trying to get field names of multiple record column.

I'm currently using this formula:

 

Table.AddColumn(Custom2, current, each let
New= try Record.FromList([Column1][ID],{"NewC"}) otherwise null
in try Record.FieldNames(New[NewC])otherwise null))

 

Which works perfectly, but I have to make one for each column. 

I want to make the [ID] part more dynamic.

I have a list of all the possible column names so I tried to use this formula:

 

= List.Accumulate (Custom1[Field names], Custom2, (state,current) =>
Table.AddColumn(Custom2, current, each let
New= try Record.FromList([Column1]{current},{"NewC"}) otherwise null
in try Record.FieldNames(New[NewC])otherwise null))

 

this is one of the many options I tried to use the "current" in the formula, non worked.

 

Please save me. 

Thank you.

 

Editing for more info.

I'm working with an API. 

When I input it to power query, this is what i get:

as you can see I have 5 fields containing records. I want to create for each one of them a list of just the field names of the records.

Here is an exmple using the names field.

 

I don't want to manually do it for each field with a record beacuse I also know that there other fields that are currently not showing but will in the future. 

 

Thank you.

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    I've removed [Sub fields] = null. If you want to preserve them - let me know.

     

    Result

    let
    Source = #table(null, {{[a=[value=10, description="qqq"], b=[gender="male", age=30], c={"test1", "test2"}, d="Some Text"]}, {[x=1, created by=[Name="Martin", Surname="Green"], identifiers={1,10,100}]}} ),
    Ad_Helper = Table.AddColumn(Source, "Helper", each 
        [ a = Record.FieldNames([Column1]),
          b = List.Transform(a, (x)=> if Record.Field([Column1], x) is record then Table.FromRows({{x, Record.FieldNames(Record.Field([Column1], x))}}, {"Field Names", "Subfields"}) else null),
          c = Table.Combine(List.RemoveNulls(b))
        ][c], type table ),
    ExpandedHelper = Table.ExpandTableColumn(Ad_Helper, "Helper", {"Field Names", "Subfields"}, {"Field Names", "Subfields"}),
    ExpandedSubfields = Table.ExpandListColumn(ExpandedHelper, "Subfields")
in
    ExpandedSubfields
  • dufoq3's avatar
    dufoq3
    2 years ago

    Result

    let
    Source = #table(null, {{[a=[value=10, description="qqq"], b=[gender="male", age=30], c={"test1", "test2"}, d="Some Text"]}, {[x=1, created by=[Name="Martin", Surname="Green"], identifiers={1,10,100}]}} ),
    Ad_Helper = Table.AddColumn(Source, "Helper", each 
        [ a = Record.FieldNames([Column1]),
          b = List.Transform(a, (x)=> if Record.Field([Column1], x) is record
                                      then Table.FromRows({{x, Record.FieldNames(Record.Field([Column1], x))}}, {"Field Names", "Subfields"})
                                      else Table.FromRows({{x, null}}, {"Field Names", "Subfields"}) ),
          c = Table.Combine(b)
        ][c], type table ),
    ExpandedHelper = Table.ExpandTableColumn(Ad_Helper, "Helper", {"Field Names", "Subfields"}, {"Field Names", "Subfields"}),
    ExpandedSubfields = Table.ExpandListColumn(ExpandedHelper, "Subfields")
in
    ExpandedSubfields

15 Replies

    • Sharkybu's avatar
      Sharkybu
      Helper II
      2 years ago

      Hi. Thank you for responding.

      I edited the post for more information, I don't have a sample data I can send.

      • dufoq3's avatar
        dufoq3
        Community Champion
        2 years ago

        Check this:

         

         

        let
    Source = #table(null, {{[a=[value=10, description="qqq"], b=[gender="male", age=30], c={"test1", "test2"}, d="Some Text"]}, {[x=1, created by=[Name="Martin", Surname="Green"], identifiers={1,10,100}]}} ),
    Ad_RecordFieldNames = Table.AddColumn(Source, "Record Field Names", each List.Combine(List.Transform(List.Select(Record.ToList([Column1]), (x)=> x is record), (y)=> Record.FieldNames(y))), type list)
in
    Ad_RecordFieldNames