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 ...
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
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
