After splitting, transpose the table and then Table.FromColumns.

See attached in the Table1 query.

Hi Riny,

thanks for your quick response,  my sample is only one record (original source got few hundred),   and how to handle multiple records using your Mcode

Note: added a second record in the attachment

thank you Sir

Then I would opt for creating two separate tables. One for Names and one for Departments and merge them on the basis of an index column. Perhaps not the most sophisticated solution, but it works. See the Merge1 query attached.

Added another possible solution. See Custom2 query in the attached file.

You could also use List.Zip and then Table.FromRows:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Custom1 = Table.TransformColumns( Source, {{"Name", each Text.Split(_, ",")}, {"Department", each Text.Split(_, ",")}}),
#"Added Custom" = Table.AddColumn(Custom1, "Custom", each List.Zip({[Name], [Department]})),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.FromRows([Custom])),
#"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"Column1", "Column2"}, {"Custom.1.Column1", "Custom.1.Column2"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.1",{"Name", "Department", "Custom"}),
#"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Custom.1.Column1", Text.Trim, type text}, {"Custom.1.Column2", Text.Trim, type text}})
in
#"Trimmed Text"

Hi Riny,

Thank you for your time,

how to distribute the ID correctly to each Name+Department ?

Sorry I forgotten about the unique identifier

Thank you !

HI Velouria

List.Zip is cool and it works even with ID.

Thanks

Hi Riny : -   Thank you for your solution !