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 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:
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.
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 ExpandedSubfieldsSorry for the misunderstanding.
I do need the nulls.
Thank you so much.
Thank you so much, you're a life saver!
You're welcome 😉
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.