Creating list of all columns used/named

[johnny51981]

Hello:
I have found this query, which works perfectly at giving me the ability to look at all of the queries currently built.

let
    Source = #sections,
    Section1 = Source[Section1],
    convertTable = Record.ToTable(Section1)
in
    convertTable

Does anyone know how to update the convertTable step to also use headers as first row, and then delete everything but that first row?

The queries that I have built are linked to different Smartsheets and I'm trying to verify if each sheet is built the same.

 

[JEC]

This enough? Afterwards you could expand the "Value" column for all used headernames.

let
    Source = #sections,
    Section1 = Source[Section1],
    Section2 = Record.RemoveFields(Section1,{"Query1"}),  //filter out current query
    convertTable = Record.ToTable(Section2),
    result = Table.TransformColumns(convertTable,{{"Value", each Table.ColumnNames(Table.PromoteHeaders(_))}})
in
    result

 

[johnny51981]

This enough? Afterwards you could expand the "Value" column for all used headernames.

let
    Source = #sections,
    Section1 = Source[Section1],
    Section2 = Record.RemoveFields(Section1,{"Query1"}),  //filter out current query
    convertTable = Record.ToTable(Section2),
    result = Table.TransformColumns(convertTable,{{"Value", each Table.ColumnNames(Table.PromoteHeaders(_))}})
in
    result

Not enough. I'm looking to have something like this, which is the final result I was able to build...though not dynamic as I was hoping.

Here is the M Code for one sheet:

let
    Source = #"Smartsheet 1",
    #"Kept First Rows" = Table.FirstN(Source,1),
    #"Added Query Name" = Table.AddColumn(#"Kept First Rows", "Smartsheet", each "Smartsheet 1", type text),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Query Name",{"Smartsheet"}),
    #"Added Smartsheet Index" = Table.AddIndexColumn(#"Removed Other Columns", "Smartsheet Index", 0, 1, Int64.Type),
    #"Demoted Headers" = Table.DemoteHeaders(#"Kept First Rows"),
    #"Kept First Rows1" = Table.FirstN(#"Demoted Headers",1),
    #"Demoted Headers1" = Table.DemoteHeaders(#"Kept First Rows1"),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers1"),
    #"Sorted Rows" = Table.Sort(#"Transposed Table",{{"Column2", Order.Ascending}}),
    #"Added Column Index" = Table.AddIndexColumn(#"Sorted Rows", "Column Index", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Column Index", {"Column Index"}, #"Added Smartsheet Index", {"Smartsheet Index"}, "Added Column Index", JoinKind.FullOuter),
    #"Expanded Added Column Index" = Table.ExpandTableColumn(#"Merged Queries", "Added Column Index", {"Smartsheet", "Smartsheet Index"}, {"Smartsheet", "Smartsheet Index"}),
    #"Added Column Number" = Table.AddColumn(#"Expanded Added Column Index", "Column Number", each "Column"&Number.ToText(List.Max({[Column Index], [Smartsheet Index]})), type text),
    #"Added Column Header" = Table.AddColumn(#"Added Column Number", "Column Header", each if [Smartsheet Index] = 0 then "Smartsheet Name" else [Column2], type text),
    #"Added Used" = Table.AddColumn(#"Added Column Header", "Used", each if [Smartsheet Index] = 0 then [Smartsheet] else "Yes", type text),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Used",{"Column Number", "Column Header", "Used"}),
    #"Transposed Table1" = Table.Transpose(#"Removed Other Columns1"),
    #"Removed Top Rows" = Table.Skip(#"Transposed Table1",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])
in
    #"Promoted Headers"

And then Appended them all together in one query, which gave me the results I provided the picture of.

Any thoughts on streamlining it/simplifying it would be appreciated.