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

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:

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

     

    • Sharkybu's avatar
      Sharkybu
      Helper II
      2 years ago

      Sorry for the misunderstanding.

      • dufoq3's avatar
        dufoq3
        Community Champion
        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