Split text in 2 columns but the info in the columns are related

[PakAli]

I am out of idea, hence seeking the wisdom from the expert

Got a table look like this

ID	Status	Date
1	status1 by userA; status2 by userB; status3 by userC	date1; date2; date3

I wanna re arrange it in this format

ID	Status	User	Date
1	status1	userA	date1
1	status2	userB	date2
1	status3	userC	date3

I have been trying multiple way, but not able to achieve the result that I want
Oh, I can not change the source data format, the output is in that format

[mdaatifraza5556 · Accepted solution]

Hi @PakAli 

Can you please try the below M code adjust it in your table.

let
Source = Table.FromRows({
{1, "status1 by userA; status2 by userB; status3 by userC", "date1; date2; date3"}
}, {"ID", "Status", "Date"}),

// Split into lists
AddStatusList = Table.AddColumn(Source, "StatusList", each Text.Split([Status], ";")),
AddDateList = Table.AddColumn(AddStatusList, "DateList", each Text.Split([Date], ";")),

// Trim spaces
Trimmed = Table.TransformColumns(AddDateList, {{"StatusList", each List.Transform(_, Text.Trim)}, {"DateList", each List.Transform(_, Text.Trim)}}),

// Extract Status and User
Parsed = Table.AddColumn(Trimmed, "Combined", each List.Transform(List.Zip({[StatusList], [DateList]}),
each [
Status = Text.BeforeDelimiter(_{0}, "by"),
User = Text.AfterDelimiter(_{0}, "by"),
Date = _{1}
])),

// Expand into rows
Expanded = Table.ExpandListColumn(Parsed, "Combined"),
#"Expanded Combined" = Table.ExpandRecordColumn(Expanded, "Combined", {"Status", "User", "Date"}, {"Combined.Status", "Combined.User", "Combined.Date"})
in
#"Expanded Combined"

--------------------------------------------------------------------------------------------------------------------------------

Then expand it.

Result

If this answers your questions, kindly accept it as a solution and give kudos.

[PakAli]

Cool.. it works
I need to study List.Transform + List.Zip
I am blurred, but it works

[AlienSx · Accepted solution]

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], 
    lst = List.TransformMany(
        Table.ToList(Source, (x) => x), 
        (x) => List.Zip({Text.Split(x{1}, "; "), Text.Split(x{2}, "; ")}),
        (x, y) => {x{0}} & y
    ), 
    tbl = Table.FromList(lst, (x) => {x{0}} & Text.Split(x{1}, " by ") & {x{2}}, {"ID", "Status", "User", "Date"})
in
    tbl

[PakAli]

TQVM
your proposal looks elegant,

now sure why Text.Split(x{1}, " |") not working
but changing it to Text.Split(x{1}, "|").. it works (maybe " " is not a space, it is specil char

anyway, thanks.. it works

[mdaatifraza5556 · Accepted solution]

Hi @PakAli 

Can you please try the below M code adjust it in your table.

let
Source = Table.FromRows({
{1, "status1 by userA; status2 by userB; status3 by userC", "date1; date2; date3"}
}, {"ID", "Status", "Date"}),

// Split into lists
AddStatusList = Table.AddColumn(Source, "StatusList", each Text.Split([Status], ";")),
AddDateList = Table.AddColumn(AddStatusList, "DateList", each Text.Split([Date], ";")),

// Trim spaces
Trimmed = Table.TransformColumns(AddDateList, {{"StatusList", each List.Transform(_, Text.Trim)}, {"DateList", each List.Transform(_, Text.Trim)}}),

// Extract Status and User
Parsed = Table.AddColumn(Trimmed, "Combined", each List.Transform(List.Zip({[StatusList], [DateList]}),
each [
Status = Text.BeforeDelimiter(_{0}, "by"),
User = Text.AfterDelimiter(_{0}, "by"),
Date = _{1}
])),

// Expand into rows
Expanded = Table.ExpandListColumn(Parsed, "Combined"),
#"Expanded Combined" = Table.ExpandRecordColumn(Expanded, "Combined", {"Status", "User", "Date"}, {"Combined.Status", "Combined.User", "Combined.Date"})
in
#"Expanded Combined"

--------------------------------------------------------------------------------------------------------------------------------

Then expand it.

Result

If this answers your questions, kindly accept it as a solution and give kudos.