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
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
Solved! Go to 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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
68 | |
25 | |
18 | |
12 |