Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Sharkybu
Helper II
Helper II

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

PersonMain.jpg

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.

personname.jpg

 

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.

2 ACCEPTED SOLUTIONS

I've removed [Sub fields] = null. If you want to preserve them - let me know.

 

Result

dufoq3_0-1721811562293.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

Result

dufoq3_0-1721814261627.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

15 REPLIES 15
dufoq3
Super User
Super User

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


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Hi. Thank you for responding.

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

Check this:

 

dufoq3_1-1721741834082.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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.

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.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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:

Final result.jpg

dufoq3_0-1721809066062.png

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Sorry for the misunderstanding.

Final result example.jpg

I've removed [Sub fields] = null. If you want to preserve them - let me know.

 

Result

dufoq3_0-1721811562293.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

I do need the nulls. 

Thank you so much.

Result

dufoq3_0-1721814261627.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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

You're welcome 😉


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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:

 

Sharkybu_0-1721821060327.png

 Thank you again so much. 

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.