Take a list of values and space data 10 cells apart ?

[mikemck]

Can power query take a list of 24 values in a column and move the values 10 cells apart in the same or a seperate column?

 

[Ron Coderre]

See if this example helps...
Using this sample data in an Excel Table named Table1:

Name     Mth     Amount
Alpha     Jan     188
Alpha     Feb     272
Alpha     Mar     151
Alpha     Apr     268
Bravo     May     294
Bravo     Jun     108
Charlie   Jul     246
Charlie   Aug     163
Charlie   Sep     260
Charlie   Oct     203
Charlie   Nov     110

This Power Query code inserts 3 blank rows between each
group of Names:
QueryName: InsBlankRowBtwnGrps
QueryCode:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

            ColHdgs         = Record.FieldNames(Source{0}),
            ColCount        = Record.FieldCount(Source{0}),
            NumOfNewRows    = 3,
            NewRowVals      = List.Repeat({null}, ColCount),
            NewRowToAdd     = Record.FromList(NewRowVals,ColHdgs),
            NewRowsStaging  = Record.AddField(NewRowToAdd,"RowSeq",{1..NumOfNewRows}),
            
    fnInsertNullRow = (TblRef as table) as table =>
        let     
            InsertionPoint  = Table.RowCount(TblRef),
            TblWithBlankRow = Table.InsertRows(TblRef, 
                                InsertionPoint, 
                                {NewRowsStaging})
            in
            TblWithBlankRow,

    SourceWithList = Table.AddColumn(Source,"RowSeq", each null),
    Grp  = Table.Group(SourceWithList, {"Name", "RowSeq"}, {{"Tbl", each _, type table}}, GroupKind.Global),

    TransformCol = Table.TransformColumns(Table.SelectColumns(Grp, {"Tbl"}), {{"Tbl", fnInsertNullRow}}),

    ExpandTblCol = Table.ExpandTableColumn(TransformCol, "Tbl", Table.ColumnNames(TransformCol{0}[Tbl])),
    ExpandRowSeqCol = Table.ExpandListColumn(ExpandTblCol, "RowSeq"),
    RemoveRowSeqCol = Table.RemoveColumns(ExpandRowSeqCol,{"RowSeq"}),
    FinalResult = RemoveRowSeqCol

in
    FinalResult

This is the end result:

Name         Mth        Amount
Alpha        Jan        188
Alpha        Feb        272
Alpha        Mar        151
Alpha        Apr        268
                
                
                
Bravo        May        294
Bravo        Jun        108
                
                
                
Charlie      Jul        246
Charlie      Aug        163
Charlie      Sep        260
Charlie      Oct        203
Charlie      Nov        110

Is that something you can work with?

 

[Ron Coderre]

Better approach....
Billzsys pointed out to me that several steps could be saved by incorporating a list of nulls.


Here's the new code:

let

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],



    NumOfBlankRowsToMake = 3,



    // Create a list of blanks
    ListOfBlanks = List.Repeat({null}, NumOfBlankRowsToMake),    


    // Convert the list of blanks to a table
    BlankRowsToInsert = Table.FromColumns({ListOfBlanks}, {"Name"}),



    // Group each row...Appending the blank rows to each record
    GroupRowsAndAddBlanks = Table.Group(Source, {"Name"}, {{"All", each Table.Combine({_, BlankRowsToInsert}), type table}}),


    // Combine all of the records by expanding the "All" column, omitting the duplicate "Name" column
    ExpandAndCombineRecords = Table.ExpandTableColumn(GroupRowsAndAddBlanks, "All", {"Mth", "Amount"}, {"Mth", "Amount"})
in
    ExpandAndCombineRecords

 

[mikemck]

These are great, and much appreciated. I should be able to work with these solutions and get to where I need to be to automate the whole process now in power query.

Thank you guys.