Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
vnarayan
Frequent Visitor

Splitting 2 columns data into rows without delimiter

Hello,

 

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

ProgramDateMoved toStatus ID
Test12023-04-05
2023-04-18
123 Program Group
Individual Program1
Completed123
Test22023-03-06
2023-03-02
456 Program Group
Individual Program2
Completed456
Test32023-04-17
2023-05-26
789 Program Group
Individual Program3
Completed789
Test42023-05-26
2023-06-02
123 Program Group
Individual Program4
Completed145

 

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:

ProgramDateMoved toStatus ID
Test12023-04-05123 Program GroupCompleted123
Test12023-04-18Individual Program1Completed123
Test22023-03-06456 Program GroupCompleted456
Test22023-03-02Individual Program2Completed456
Test32023-04-17789 Program GroupCompleted789
Test32023-05-26Individual Program3Completed789
Test42023-05-26123 Program GroupCompleted145
Test42023-06-02Individual Program4Completed145

 

Appreciate the help.

 

Thanks

1 ACCEPTED SOLUTION
slorin
Super User
Super User

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 

View solution in original post

2 REPLIES 2
slorin
Super User
Super User

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 

Thank you so much @slorin 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors