Forum Discussion
Solved
Splitting 2 columns data into rows without delimiter
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
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
DataStéphane
2 Replies
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
DataStéphane
