Extract and assign a new record

[limaalpha]

In my table with a title, date and authors columns, each record had multiple authors separated by a semi colon.
I would like to extract the additional author names per row into their own unique records (along with the corresponding data from the original source record).
Can this be done and is it possible to do it in power query so new data extracted can be processed automatically?

 

[JEC]

Take a look at this

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    
    xMaxList =  List.Transform({
        1..List.Max(
         Table.AddColumn(Source, "Custom", each 
            List.Count(
                Text.PositionOfAny([authors], {";"}, Occurrence.All)
            ))[Custom]
        ) +1
    }, each "V" & Text.From(_)),

    sp = Table.SplitColumn(Source, "authors", Splitter.SplitTextByDelimiter("; ", QuoteStyle.Csv), xMaxList),
    unPiv = Table.UnpivotOtherColumns(sp, {"title", "date"}, "Attribute", "authors"),
    delCols = Table.RemoveColumns(unPiv,{"Attribute"})
in
    delCols

Book1
	A	B	C	D	E	F	G
1	title	date	authors		title	date	authors
2	marathon	3-4-2023	elaine; doug; mike		marathon	3-4-2023	elaine
3	test	4-4-2023	james; john		marathon	3-4-2023	doug
4					marathon	3-4-2023	mike
5					test	4-4-2023	james
6					test	4-4-2023	john
7
Sheet1

 

[limaalpha]

Take a look at this

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
   
    xMaxList =  List.Transform({
        1..List.Max(
         Table.AddColumn(Source, "Custom", each
            List.Count(
                Text.PositionOfAny([authors], {";"}, Occurrence.All)
            ))[Custom]
        ) +1
    }, each "V" & Text.From(_)),

    sp = Table.SplitColumn(Source, "authors", Splitter.SplitTextByDelimiter("; ", QuoteStyle.Csv), xMaxList),
    unPiv = Table.UnpivotOtherColumns(sp, {"title", "date"}, "Attribute", "authors"),
    delCols = Table.RemoveColumns(unPiv,{"Attribute"})
in
    delCols

Book1
	A	B	C	D	E	F	G
1	title	date	authors		title	date	authors
2	marathon	3-4-2023	elaine; doug; mike		marathon	3-4-2023	elaine
3	test	4-4-2023	james; john		marathon	3-4-2023	doug
4					marathon	3-4-2023	mike
5					test	4-4-2023	james
6					test	4-4-2023	john
7
Sheet1

Thank you, I'm nearly there but when I swapped in the real (and extra column names) in the advanced editor, there were no syntax errors but in the main Power Query window I'm getting an "expression. error" any idea where I might have veered off course? see below -

Expression.Error: We cannot convert the value null to type Text.
Details:
Value=
Type=[Type]

 

[JEC]

Maybe you could post some sample data from your original table

 

[limaalpha]

Publication Title	Publication Frequency	Date of Sending	Purpose of Content	Lima Office	Relevant Audience	Author and BD Contacts
Pub 205	Quarterly	06/04/2023	yet to be determined	Geneva	Internal	James; Joanne; Pete
Pub 302	Fortnightly	21/03/2023	generate new audience	Frankfurt	Clients	Christiana; Mike

 

[JGordon11]

try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Result = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Author and BD Contacts", each Text.Split(_, ";")}}), "Author and BD Contacts")
in
    Result

 

[limaalpha]

try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Result = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Author and BD Contacts", each Text.Split(_, ";")}}), "Author and BD Contacts")
in
    Result

Brilliant! This worked instantly Thank you very much
Are there any tutorials you can point me to that cover the transformation features you've utilized to solve this?

 

[limaalpha]

Brilliant! This worked instantly Thank you very much
Are there any tutorials you can point me to that cover the transformation features you've utilized to solve this?

Spoke too soon
This worked instantly within the Power Query editor window but when I "close and load to a new worksheet" I get a pop-up error:
[Expression.Error] We Cannot convert the value null to type Text

(ps: Are there any tutorials you can point me to that cover the transformation features you've utilized to solve this?)

 

[JGordon11]

Spoke too soon
This worked instantly within the Power Query editor window but when I "close and load to a new worksheet" I get a pop-up error:
[Expression.Error] We Cannot convert the value null to type Text

(ps: Are there any tutorials you can point me to that cover the transformation features you've utilized to solve this?)

Replace nulls with "" in the authors column before the Result step

 

[limaalpha]

Replace nulls with "" in the authors column before the Result step

This worked! I initially tried using the right-click menu "Replace Values..." to replace the string NULL with "". That didn't work but then, I clicked the "Remove Empty" filter option and this eliminated the error.

I realised this filtered out any records that don't have an author so in the source data set, I filtered for this and wrote "No Author" into the fields (copied it down with CTRL+D). Now it's working great! Thanks again.