Splitting rows from one column to 2 columns that sit in the same row

[SfraserMM26]

Hi 

I have some data and I want to be able to calculate the average time someone attended a group but this is how the data presents.

UniqueID	JournalDateTime	ItemName
1234	12/5/2023	Added to group: A
1234	15/8/2023	Removed from group: A
4321	20/8/2023	Added to group: A
4321	17/11/2023	Removed from group: A

what I think I need is to turn the above into something like this so I can calculate the difference between the dates

UniqueID	StartDate	AddedItem	GroupName	EndDate	RemovedItem	GroupName2
1234	12/5/2023	Added to group:	A	15/8/2023	Removed from group:	A
4321	20/8/2023	Added to group:	A	17/11/2023	Removed from group:	A

unless there is a calculation or easier way to calculate this to then turn into a visual.  Just to note there are lots of different group names.

Thank you

[Anonymous]

Hi @SfraserMM26 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTLVNzTSNzIwMgayHVNSUlMUSvIV0ovySwusFByVYnXgCi30DU1hCoNSc/PLgErTivJzURWbGBsZghUbGeA3FarQ0FDf0JywsQg3mOEy1glZIchYvK4Fqo4FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UniqueID = _t, JournalDateTime = _t, ItemName = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UniqueID", Int64.Type}, {"JournalDateTime", type date}, {"ItemName", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "ItemName", Splitter.SplitTextByDelimiter(": ", QuoteStyle.Csv), {"ItemName", "Group"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ItemName", type text}, {"Group", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[ItemName]), "ItemName", "JournalDateTime")
in
    #"Pivoted Column"

Best Regards

[lbendlin]

I would recommend against re-pivoting your data.  If you can, leave that up to the visuals or the DAX code.

Please confirm your requirement, and provide some more sample data.

[SfraserMM26]

Thakn you for your help, i have since realised that I was wrong and there isn't a unique identifyer to link the added to group and removed from group so i don't think it can be done now.