Forum Discussion
Sharkybu
2 years agoHelper 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 Re...
- 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 - 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
2 years agoHelper II
I do need the nulls.
Thank you so much.
Sharkybu
2 years agoHelper II
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.