How to extract all the column names from a 'Table' data Type Column ?

[Sayri]

Hi all,
Would it be possible to extract all the column names + 1st row, from each Table listed in 'Data' Column below please ?

The goal would be to have a list like:

Index0.Column01, 1st row Value
Index0.Column02, 1st row Value
Index0.Column03, 1st row Value
Index1.Column1, 1st row Value
Index1.Column2, 1st row Value
Index1.Column3, 1st row Value
Index2.ColumnA, 1st row Value
Index2.ColumnB, 1st row Value
Index2.ColumnC, 1st row Value
Index2.ColumnD, 1st row Value
Index3.Column1a, 1st row Value
Index3.Column2b, 1st row Value
Etc...

[CNENFRNL · Accepted solution]

Hi, @Sayri , it's easy to extract all columns' names from each table in your scenario; you might add applied steps like this,

#"Column Names" = Table.TransformColumns(#"Previous Step", {{"Data", Table.ColumnNames}}),
#"Expanded Data" = Table.ExpandListColumn(#"Column Names", "Data")

[Jimmy801 · Accepted solution]

Hello @Sayri 

you can transform you tables first to get the first row of your tables as new table with Table.First and Record.ToTable.

Then expand the newly created table. here an easy example

let
    Source = #table({"Index", "Data"}, {{"Index0", #table({"ColumnA", "ColumnB"}, {{"A", "B"}, {"C", "D"}})}, {"Index1", #table({"Column1", "Column2"}, {{"E", "F"}, {"G", "H"}})}}),
    GetFirstRowOfTable = Table.TransformColumns
    (
        Source,
        {
            {
                "Data",
                each Record.ToTable(Table.First(_))
            }
        }
    ),
    #"Expanded Data" = Table.ExpandTableColumn(GetFirstRowOfTable, "Data", {"Name", "Value"}, {"Column name", "First row value"})
in
    #"Expanded Data"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Sayri]

Actually I've done a mix between your solution and the previous from @CNENFRNL  and it works perfectly
Thank you very much to both of you

[wdx223_Daniel]

=#table(2,List.Combine(Table.ToList(PreviousStep,each List.Transform(Table.ColumnNames(_{1}),(x)=>Number.ToText(_{0},"Index0.")&x&","&Record.FieldOfDefault(_{1}{0},x,null)))))

[CNENFRNL · Accepted solution]

Hi, @Sayri , it's easy to extract all columns' names from each table in your scenario; you might add applied steps like this,

#"Column Names" = Table.TransformColumns(#"Previous Step", {{"Data", Table.ColumnNames}}),
#"Expanded Data" = Table.ExpandListColumn(#"Column Names", "Data")

[Sayri]

Thanks a lot @CNENFRNL , your solution works perfectly to list the column names 🙂
Is there any possibility to extract the 1st value of each colomn for each table please ?