Split a column with items into rows when you don't know how many items there could be

[DebbieE]

I have the following column in some data (and at the moment I have to transform using Power Query Editor

Data ID       IDs

1                 1;44;55;66

2                  3;4;5;6;7;8;9;20;22

3                  2;21;23

4                  10

And you dont know how many there will be for each record. And I want to pivot to this

Data ID       IDs

1                 1

1                 44

1                 55

1                 66

2                  3

2                4

2                5

2                6

2                7

2                8

2                9

2                20

2                22

3                2

3                21

3               23

4              10

But I can't split rows because I dont know how many columns there could be so i dont know how many to pivot on. Has anyone got a good way of achieving this?

[Ahmedx · Accepted solution]

pls try this step

[KRISHP1234]

= Text.Split([IDs], ";")

Yes you got it. 

[DebbieE]

I think I have it.

create custom column 

= Text.Split([IDs], ";")

And then expand the list