How to deliminate multiple (>2) columns to coincide when separated?

[Anonymous]

I have some poorly structured reports that have multiple pieces of information stored in the same cell, I.E. 

Column 1 Column 2 Column 3 Column 4

x;y;z          a;b;c          1;2;3         #ID

x;y             a;b            1;3            #ID2

x;z             a;c             1;2            #ID3

I need to separate each cell, but keep the same ID and have it hopefully coincide with the other columns as well. 

IE

Column 1 Column 2 Column 3 Column 4

x               a                1              #ID

y               b                2             #ID

z               c                3              #ID

x               a                1               #ID2

y               b                3              #ID2

Is this even possible? This data is also consistent as in if there are 3 separate values in Column 1, Column 2 & 3 will also have 3 separate values. 

[Zubair_Muhammad · Accepted solution]

@Anonymous 

You can add following custom column and then expand it to new rows

For null values you can use fill down

Please see attached file's Query Editor for the steps

=Table.FromColumns(
    {
    Text.Split([Column1],";"),
    Text.Split([Column2],";"),
    Text.Split([Column3],";"),
    Text.Split([Column4],";")
    }
                  )

[Nathaniel_C]

Hi @Anonymous ,

In Power Query go to the home page, and choose split column by delimiter, and under advanced options split into rows. I highlighted the first column and did this. It seemed to work as you needed.  However you can only do 1 column at a time.  Doing one after another does not work.  Therefore  you might try make 3 copies of the table. In each copy keep only 1 col and the id col. Next do as I described above on the three tablea. Then merge the tables back together.  Seems like it will work!

Let us know how it goes, I have never used that option!

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel