Insert 8 blanks rows after each row

[Ashish_Mathur]

Hi,

I have a dataset with 21 column and 6 rows.  Using the Query Editor, I would like to insert 8 blank rows after each row.  How can this be done?

Thank you.

[MFelix · Accepted solution]

Hi @Ashish_Mathur,

Using this post and making some change to the custom function I was abble to achieve the result you need.

Check below the revised function with indication of where you need to add fields for empty rows.

let
    AddEmptyRows = (Tbl as table, HeadersToGroup as list, groupKind as number) as table =>
    let 
        FirstRecord = Tbl{0},
 
        // create an empty record
        EmptyRecord = 
            let 
                FieldNames = Record.FieldNames(FirstRecord),
                EmptyValues = List.Repeat({""},Record.FieldCount(FirstRecord))
            in
                Record.FromList(EmptyValues,FieldNames),
 
        // group table
        GrpTable = Table.Group(
                        Tbl, 
                        HeadersToGroup, 
                        {"Tbl", each _, type table}, 
                        groupKind ),
       // transform table column of grouped table adding 1 empty row at the bottom
        TransformTblCol = Table.TransformColumns(
                              GrpTable,
                              {"Tbl", each Table.InsertRows(_, Table.RowCount(_), {EmptyRecord , EmptyRecord  , EmptyRecord , EmptyRecord , EmptyRecord ,EmptyRecord ,EmptyRecord ,EmptyRecord   })} 
                                                                    // add EmptyRecord for each additional line you would like
                           ),
        // Select the tbl column and expand it
        ExpandColumns = Table.ExpandTableColumn(
                            Table.SelectColumns(
                                TransformTblCol,
                                {"Tbl"}
                                ),
                        "Tbl",
                        Record.FieldNames(FirstRecord)
                        ),
 
       // build a list containing the header name and the Type of each column of Source 
       ColTypes = List.Accumulate(
                     Record.FieldNames(FirstRecord),
                     {},
                     (state, current) => state & {{current, Value.Type(Record.Field(FirstRecord, current))}}
                  )
 
    in
      Table.TransformColumnTypes(ExpandColumns ,ColTypes)
in
    AddEmptyRows

See also atach PBIX file with example.

Regards,

MFelix

[Ashish_Mathur]

Thank you.

[MFelix]

Hi @Ashish_Mathur, If it works please mark the answer as correct. Regards, MFelix