Inserting blank rows between queries in the table.

[Martin sherk]

I have Appended 4 query tables together in 1 table and loaded them into my excel. however, is there a way I can insert blank rows in the table between each query and type something in 1 of that empty rows or maybe insert a header for each query in that table?

 

[jdellasala]

Create a table with only one blank row (and the same column names), and append it to the first two tables before the appends.

 

[Martin sherk]

Create a table with only one blank row (and the same column names), and append it to the first two tables before the appends.

worked perfectly, is there a way to replace the blank rows with column headers >?

 

[jdellasala]

Sure. Create a blank query and use this:

let
    Source = Table.ColumnNames(Table1),
    ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    TransposedTable = Table.Transpose(ConvertedToTable),
    RenamedColumns = Table.RenameColumns(TransposedTable,{{"Column1", "Table1Col1Name"}, {"Column2", "Table1Col2Name"}})
in
    RenamedColumns

 

[Martin sherk]

Sure. Create a blank query and use this:

let
    Source = Table.ColumnNames(Table1),
    ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    TransposedTable = Table.Transpose(ConvertedToTable),
    RenamedColumns = Table.RenameColumns(TransposedTable,{{"Column1", "Table1Col1Name"}, {"Column2", "Table1Col2Name"}})
in
    RenamedColumns

I am facing this error:
Expression.Error: We cannot convert a value of type Table to type List.
Details:
Value=

Type=[Type]

let
    Source = #"All tables Together",
    ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    TransposedTable = Table.Transpose(ConvertedToTable),
    RenamedColumns = Table.RenameColumns(TransposedTable,{{"Column1", "Table1Col1Name"}, {"Column2", "Table1Col2Name"}})
in
    RenamedColumns

 

[jdellasala]

I am facing this error:
Expression.Error: We cannot convert a value of type Table to type List.
Details:
Value=

Type=[Type]

let
    Source = #"All tables Together",
    ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    TransposedTable = Table.Transpose(ConvertedToTable),
    RenamedColumns = Table.RenameColumns(TransposedTable,{{"Column1", "Table1Col1Name"}, {"Column2", "Table1Col2Name"}})
in
    RenamedColumns

Source is not a step, it's a Query Name. Everything I posted was done through the UI. I only took the spaces out of the step names to get rid of the #"" cluttrer.

 

[smozgur]

Perhaps looping through queries to be appended and insert a blank row at the beginning of each table with header text in the first column, and others null. Field names and field counts should be adapted in the code (field1, field2, etc.).
Something like this:

let

    // List of Queries already exist
    QueryTables = {Table1, Table2, Table3, Table4},

    TablesToBeAppended = List.Generate(
        () => [i = 0],
        each [i] < List.Count(QueryTables) ,
        each [i = [i] + 1],
        each Table.InsertRows(QueryTables{[i]}, 0, {
            [
                field1 = "Header " & Number.ToText([i] + 1),
                field2 = null,
                field3 = null
            ]
        })
    ),

    Source = Table.Combine(TablesToBeAppended)
in
    Source

 

[Martin sherk]

Perhaps looping through queries to be appended and insert a blank row at the beginning of each table with header text in the first column, and others null. Field names and field counts should be adapted in the code (field1, field2, etc.).
Something like this:

let

    // List of Queries already exist
    QueryTables = {Table1, Table2, Table3, Table4},

    TablesToBeAppended = List.Generate(
        () => [i = 0],
        each [i] < List.Count(QueryTables) ,
        each [i = [i] + 1],
        each Table.InsertRows(QueryTables{[i]}, 0, {
            [
                field1 = "Header " & Number.ToText([i] + 1),
                field2 = null,
                field3 = null
            ]
        })
    ),

    Source = Table.Combine(TablesToBeAppended)
in
    Source

Thank you sir for your time, sadly it's a bid advanced for me to use the M lang. .. i'll study your answer tho.