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

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

Thank you so much, you're a life saver!

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    You're welcome 😉

  • Sharkybu's avatar
    Sharkybu
    Helper II
    2 years ago

    Thank you again so much, but I kinda have a follow up question if it's okay and allowed.

    Now that I can extract the data dynamically, I am stuck in cases where the sub fields have records in them.

    I changed the source code you gave me:

    = #table(null, {{[a=[value=10, description=[type="qqq",term="qqq"]], b=[gender="male", age=30], c={"test1", "test2"}, d="Some Text"]}, {[x=1, created by=[Name="Martin", Surname="Green"], identifiers={1,10,100}]}} )

    and the end result i need is:

     

     Thank you again so much.