Forum Discussion
Field names from multiple columns containing record
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 ExpandedSubfieldsResult
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, 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.
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: