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 Re...
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
dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi Sharkybu, could you provide sample data and expected result based on that?

  • 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

       

      • Sharkybu's avatar
        Sharkybu
        Helper II
        2 years ago

        Thank you, it does work.

        But I need tocreate a seperate list for each record becase some fields appear in more then one record (like the type field) and when I do list combine I dont know which record has it and which doesn't.