How to prepare multiple rows with two different colums

[Coby]

Hy, 
I would kindly ask your help. I have a table with many columns from SQL database. This database contain one colum which contain two ore more ID spearated with " ; " (semicolons). I can split column to separate columns. However I do not know how can I prepare multiple rows (other column data should be the same but the ID column sholud be different in Power Query?

Original table:

ID	Data 1	Data 2
1000; 1001	a	b
1002	c	d
1003; 1004; 1005	e	f

My actual stage: (with split colum)

ID1	ID2	ID3	Data 1	Data 2
1000	1001		a	b
1002			c	d
1003	1004	1005	e	f

Neaded table:

ID	Data 1	Data 2
1000	a	b
1001	a	b
1002	c	d
1003	e	f
1004	e	f
1005	e	f

Best regards,

Coby

[Subash_Govind]

Below are the results code...

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lFySSxJVDCEMYyUYnWilQwNDAysFYAkSDwRiJNgwkZATjIQp8AEjMHqTMCkKVAiFYjTlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", type text}, {"Data 1", type text}, {"Data 2", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "ID", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"ID.1", "ID.2", "ID.3"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ID.1", Int64.Type}, {"ID.2", Int64.Type}, {"ID.3", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {"Data 1", "Data 2"}, "Attribute", "ID"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ID", "Data 1", "Data 2"})
in
#"Reordered Columns"

[Coby]

Thanks. It is work.

Could you please define the column where should only split the data by ";" . I have other columns which contain ";" as well, but I would not split those. 

[wdx223_Daniel]

this code only split the first column

[wdx223_Daniel]

=#table(Table.ColumnNames(OriginalTable),List.TransformMany(Table.ToRows(OriginalTable),each Text.Split(_{0},";"),(x,y)=>{y}&List.Skip(x)))