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

    • dufoq3's avatar
      dufoq3
      Community Champion
      2 years ago

      Just remove List.Combine and you get separate lists inside a list. If you want to get something else - provide expected result based on my sample data please.

      • Sharkybu's avatar
        Sharkybu
        Helper II
        2 years ago

        Thank you so much again, you have no idea how much you are helping me.

        I still need to figure out how to separate by the different records. Cause right now when I expand, I get the full list without knowing which field belongs to which record. 

        This is the ultimate end result i'm looking for: