Power Query: Control destination rows of the query

[SimunovicS]

I would like to use Power Query to bring data from a table in another workbook and store it into alternate rows, e.g. rows 1,2,3 from source table should be placed by Power Query into destination rows 2, 4, 6. I tried formatting tab in the Power Query ribbon, but there is no option to do that and I could not find functions that explicitly define destination rows by a formula. Any leads suggestions will be appreciated.

[#2 · Accepted solution]

@SimunovicS 

Does the attached file do what you are looking for?

[#3]

@OliverScheurich Yes. Thank you. I need to go through all the steps, but the result is what I need. I ultimately need to combine several tables (e.g. one table to even, the other to odd rows, etc.), but his seems as a base approach. Any suggestions for a good book on Power Query?

[#4]

@SimunovicS 

You are welcome. Maybe the attached file does what you are looking for. I can't suggest a book on Power Query. I've learned the basics of Power Query by the solutions provided by the experts of the Microsoft Tech Community. Maybe an expert of the community can suggest a way to start with Power Query.

[#5]

It does! Thank you.

[#6]

@OliverScheurich , @SimunovicS 

That doesn't work if table has more than on column. Alternatively we may

- add Index column

- Group By it without aggregation

- add row to each grouped table (to simplify only with [Index=""], when row will be with errors

- remove all but above columns

- expand it

- replace errors on blanks

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(
        Source,
        "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(
        #"Added Index", {"Index"},
        {{"Count", each _, type table [A=number, V=text, Index=number]}}),
    
    // that adds the row with errors
    #"Added Custom" = Table.AddColumn(
        #"Grouped Rows",
        "Custom",
        each Table.InsertRows([Count], 0, {[Index=""]} )),
    #"Removed Other Columns" = Table.SelectColumns(
        #"Added Custom",
        {"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(
        #"Removed Other Columns",
        "Custom", {"A", "V"}, {"A", "V"}),
    #"Replaced Errors" = Table.ReplaceErrorValues(
        #"Expanded Custom", {{"A", ""}, {"V", ""}})
in
    #"Replaced Errors"

[#7]

Thank you. This question turned out to be very educational for me.

[#8]

@SimunovicS 

We all learn something every day