Field names from multiple columns containing record

[Sharkybu]

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.

[dufoq3 · Accepted solution]

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]

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

[Sharkybu]

Hi. Thank you for responding.

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

[dufoq3]

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]

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]

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]

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]

[Sharkybu]

Sorry for the misunderstanding.

[dufoq3 · Accepted solution]

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

[Sharkybu]

I do need the nulls. 

Thank you so much.

[dufoq3 · Accepted solution]

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]

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

[dufoq3]

You're welcome 😉

[Sharkybu]

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. 

[Sharkybu]

I got the nulls.

Ichanged part of the code.

= Table.AddColumn(RecordList, "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(List.RemoveNulls(b))
][c], type table )

Thank you so much. you're a life saver.