- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi. Thank you for responding.
I edited the post for more information, I don't have a sample data I can send.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry for the misunderstanding.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I do need the nulls.
Thank you so much.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you so much, you're a life saver!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
01-11-2023 08:28 AM | |||
10-11-2024 03:15 AM | |||
02-20-2024 07:12 AM | |||
07-23-2024 03:41 AM | |||
08-06-2024 11:20 PM |