March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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.
Solved! Go to 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
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
Hi. Thank you for responding.
I edited the post for more information, I don't have a sample data I can send.
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
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.
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:
Sorry for the misunderstanding.
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
I do need the nulls.
Thank you so much.
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
Thank you so much, you're a life saver!
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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
20 | |
12 | |
10 | |
10 | |
7 |
User | Count |
---|---|
42 | |
25 | |
16 | |
16 | |
11 |