Forum Discussion
Sharkybu
Helper II
2 years agoField 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
Helper II
2 years agoSorry for the misunderstanding.
dufoq3
Community Champion
2 years agoI'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