Forum Discussion
SimunovicS
Mar 31, 2022Copper Contributor
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 row...
- Mar 31, 2022
Does the attached file do what you are looking for?
OliverScheurich
Mar 31, 2022Gold Contributor
Does the attached file do what you are looking for?
SergeiBaklan
Apr 02, 2022Diamond 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"
- SimunovicSApr 04, 2022Copper ContributorThank you. This question turned out to be very educational for me.
- SergeiBaklanApr 04, 2022Diamond Contributor
We all learn something every day