Forum Discussion

SimunovicS's avatar
SimunovicS
Copper Contributor
Mar 31, 2022
Solved

Power Query: Control destination rows of the query

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.

7 Replies

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor

      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"
      • SimunovicS's avatar
        SimunovicS
        Copper Contributor
        Thank you. This question turned out to be very educational for me.
    • SimunovicS's avatar
      SimunovicS
      Copper Contributor

      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?

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor

        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.

Resources