Forum Discussion
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.
Does the attached file do what you are looking for?
7 Replies
- OliverScheurichGold Contributor
- SergeiBaklanDiamond Contributor
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"
- SimunovicSCopper ContributorThank you. This question turned out to be very educational for me.
- SimunovicSCopper 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?
- OliverScheurichGold Contributor
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.