Forum Discussion
Splitting columns into multiple rows
To achive the desired result you can use index column: before appending datasets you create an index in each of them. After you append queries as new they will be listed one under another (so Dataset A and then Dataset B). However, because you index column, you can sort them in a desired way (once you're done, you can delete index column in the final dataset in case you don't need it anymore):
I also don't remember whether you start with 2 separate datasets or you have just one, which you first need to split. In attached pbix you'll find both option (when you select one of them, just delete the second)
Is it what you've been looking for? 🙂
Hi Ry009, I think Kishore_KVNhas provided great explanation for your problem. Let me try to provide some more details on why it works 🙂
First you split the initial dataset in 2 (those that you need to append later on), once they "Dataset A" and "Dataset B" are ready (steps #2 and #3 from the previous reply you've got), you need to combine them.
In this case you don't need join, because join is a subset of initial datasets. What you want to do is to append them (i.e. place one under another). When you ask Power Query to do so, it will first search for columns with the same names and, if found, place rows from "Dataset B" under those of "Dataset A". If Power Query fails to find columns with the same names, it will add new columns (those of "Dataset B" to those present in "Dataset A"), which is what you've asked! Now, because you used append, Power Query will not try to place them on the same row but simply put one under another and because columns from "Dataset B" were not there in "Dataset A", you get blank values in new columns (from Dataset B) for existing rows of "Dataset A" and vice versa.
I hope this explanation makes its clearer now 🙂
Assuming what I've suggested isn't possible?