Splitting 2 columns data into rows without delimiter

[vnarayan]

Hello,

I have some data in an excel file that looks like this:

Program	Date	Moved to	Status	ID
Test1	2023-04-05 2023-04-18	123 Program Group Individual Program1	Completed	123
Test2	2023-03-06 2023-03-02	456 Program Group Individual Program2	Completed	456
Test3	2023-04-17 2023-05-26	789 Program Group Individual Program3	Completed	789
Test4	2023-05-26 2023-06-02	123 Program Group Individual Program4	Completed	145

I need to get the Date column and Moved to column split into individual rows and the Date corresponds to the Moved to. Here is what I am looking for:

Program	Date	Moved to	Status	ID
Test1	2023-04-05	123 Program Group	Completed	123
Test1	2023-04-18	Individual Program1	Completed	123
Test2	2023-03-06	456 Program Group	Completed	456
Test2	2023-03-02	Individual Program2	Completed	456
Test3	2023-04-17	789 Program Group	Completed	789
Test3	2023-05-26	Individual Program3	Completed	789
Test4	2023-05-26	123 Program Group	Completed	145
Test4	2023-06-02	Individual Program4	Completed	145

Appreciate the help.

Thanks

[slorin · Accepted solution]

Hi

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkktLjFU0lEyMNU3MNE3MjAyjskztICxgRKGRsYKAUX56UWJuQruRfmlBTF5nnkpmWWZKaWJOTAZkAnO+bkFOaklqSkQTUqxOhDTjUCmm+kbGENNNzCCsYESJqZmhE03QjMdqAluOtiJ5gi3GwFtMoWZbm5hSdh0YzTTgZrgppsA+QgTIW43IylkTNBDxsRUKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Program = _t, Date = _t, #"Moved to" = _t, Status = _t, #" ID" = _t]),
Split = Table.AddColumn(Source, "Data", each 
  Table.FillDown(
    Table.FromColumns(
     List.Transform(
      Record.ToList(_),
      each Text.Split(_,"#(lf)")),
    Table.ColumnNames(Source)),
  Table.ColumnNames(Source))),
Data = Table.Combine(Split[Data])
in
Data

Stéphane 

[vnarayan]

Thank you so much @slorin