Splitting columns into multiple rows

[Ry009]

Will preface this by saying I've exhausted all research for this problem, both here and elsewhere online, I am not sure how else to present this data for help. 

I have no idea why the data is required in this format, but unfortunately, it is.

I need to split columns 6-12 onto a secondary row.

So it would be then presented like this;

Is this easily doable in Power Query?

[Anonymous]

Hi @Ry009 ,

Thanks to @Sergii24  and @Kishore_KVN  for the detailed explanations and steps. If you are still not clear, I have provided a pbix file for your reference, you can click "transform data" to enter the power query and then check the steps on the right side of the step column.

(1)Copy the original table to get Table 2, delete the redundant columns and then add customized columns.

(2) Copy the original table to get Table 3, delete the redundant columns, and then add customized columns.

(3) Append table 2 and table 3.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Kishore_KVN]

Hello @Ry009 , 

Yes in Power Query Editor its doable. follow below steps:

1. Duplicate this table twice in PQE

2. Select till Language Code column from first and remove other columns in first copy also add new custom column Information Type = "Dataset A".

3. Select till Language code column from first and remove selected columns in second copy also add new custom column Information Type = "Dataset B".

4. Select Append Queries as New in Append option and combine copy two and three also disable loading of copy one, two and three into Power BI Desktop. 

Attached similar activity performed in Excel for reference. 

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

[Ry009]

Thank you for responding.

Could you advise how you did the join and separate column rows on the next line?

[Sergii24]

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 🙂

[Ry009]

Assuming what I've suggested isn't possible?

[Ry009]

Thanks for the reply.

This is the only option I have for 'Append as New'. When I do this, all it does is put the entire table underneath the other, not alternating rows like my expected output above.

[Sergii24]

What are the columns of "Orange" and "Pink" tables? Are column names the same? You need to have different column names to achieve the desired result.

Let's summarize once again how "Append" works: first it searches for columns with the same names in the input tables. If found, rows of those columns are appended one under another. In the case when column names are different (so append can't find same names), columns that don't exist in the first table are added to the second one (rows of the first table for that new columns remain empty).

Check pbix attached for further details.

Good luck with your project and have a great day 🙂

[Ry009]

Hi Sergii

Thanks for getting back to me. I think I may not have explained correctly. I have multiple rows of data, not just one. And when I append, rather than alternating rows, its putting the two separate tables under one another, hope that makes sense?

So just to recap this is an example of what I start with

Want to achieve this

But append only does this;

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result very clearly.  It is very difficult to understand your requirement from small images.  Share data in a format that can be pasted in an MS Excel file.

[Sergii24 · Accepted solution]

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? 🙂

[Ry009]

Thank you so much Sergii!

This is what I was after (from single dataset) 🙂 

What I neglected to include the requirement that the primary id in column A needs to be represented in the row underneath it for the corresponding index.

After 30 mins of looking for a solution.....stupid me realised, just keep it in both tables so when it appends, it will be in both. 🙂

Thank you so much!