Forum Discussion
Power Query: Control destination rows of the query
- Mar 31, 2022
Does the attached file do what you are looking for?
Does the attached file do what you are looking for?
- SergeiBaklanApr 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
- SimunovicSApr 01, 2022Copper 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?
- OliverScheurichApr 01, 2022Gold 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.
- SimunovicSApr 01, 2022Copper ContributorIt does! Thank you.