How to Concate two columns based on unique id and another column

[P_P2595]

Hi,

I've an input as below and want to concate the two columns measure and description and find the record is completed or not by picking up the earliest date of start and end if the id has more than one measure with same value

id	Measure	description	date
1	Tea	start	16/08/2023
1	par	start	15/09/2023
1	par	end	13/12/2023
2	par	start	14/08/2023
2	par	end	12/09/2023
3	par	start	12/01/2023
3	par	end	04/05/2023
3	Par	start	10/08/2023

Expected output

id	Measure	description	Merged	date	Completed
1	Tea	start	Tea:start	16/08/2023	N
1	par	start	par: start, par: end	15/09/2023	Y
1	par	end	par: start, par: end	13/12/2023	Y
2	par	start	par: start, par: end	14/08/2023	Y
2	par	end	par: start, par: end	12/09/2023	Y
3	Par	start	Par:start, Par:end	12/01/2023	Y
3	Par	end	Par:start, Par:end	04/05/2023	Y
3	Par	start	par: start	10/08/2023	N

 Thank you in advance

[Ashish_Mathur · Accepted solution]

Hi,

Write these calculated column formulas

Column = Data[Measure]&":"&Data[description]

Column 2 = CALCULATE(CONCATENATEX(Data,Data[Column],","),FILTER(Data,Data[id]=EARLIER(Data[id])&&Data[Measure]=EARLIER(Data[Measure])))

Hope this helps.

[P_P2595]

Hi Ashish,

It's working fine and thanks for the quick reply
Now I've edited the question as I noticed the column is not working for some cases. and we need to use the date column as earlist date to check whether the record is completed or not.

Thank you so much

[Ashish_Mathur]

You are welcome. I do not understand the new requirement at all.

[P_P2595]

Sorry to create confusion here. inshort I need one more column that can give me the pair description

ex. id 3 has 4rows but out of those rows only two records are completed with start and end other 2 has just start only not the end so not completed. Exclude the record if pair is not there 
Is this doable?

[Ashish_Mathur]

Try these calculated column formulas

End date of ID and measure = CALCULATE(min(Data[date]),FILTER(Data,Data[id]=EARLIER(Data[id])&&Data[Measure]=EARLIER(Data[Measure])&&Data[date]>EARLIER(Data[date])&&Data[description]="end"))

Start date of ID and measure = CALCULATE(max(Data[date]),FILTER(Data,Data[id]=EARLIER(Data[id])&&Data[Measure]=EARLIER(Data[Measure])&&Data[date]<EARLIER(Data[date])&&Data[End date of ID and measure]=EARLIER(Data[date])&&Data[description]="start"))

Completed = if(COALESCE(Data[End date of ID and measure],Data[Start date of ID and measure]),"Y","N")

Hope this helps.

[P_P2595]

Hi Ashish,

Calculated column formulas are working fine but it looks like in some scenarios it is not working.

Ex. In the below case episode started two times and the episode ended after the 2nd episode started but in the episode end it is still picked the 2nd episode started date not the first.

Expecting something like this

MRN	measure_date	reason_for_collection_description	Measure	End date of ID and measure	Start date of ID and measure	Completed_SDQ
25719	16/08/2023 0:00	Episode Start	Teacher			N
25719	15/09/2023 0:00	Episode Start	Parent	13/12/2023 0:00		Y
25719	13/12/2023 0:00	Episode End	Parent		15/09/2023 0:00	Y
28549	12/01/2023 0:00	Episode Start	Parent	10/08/2023 0:00		Y
28549	04/05/2023 0:00	Episode Start	Parent			N
28549	10/08/2023 0:00	Episode End	Parent		12/01/2023 0:00	Y

Thank you so much😊

[Ashish_Mathur]

What should the answer be for MRN 28549?

[P_P2595]

Form started on 12/01/2023 completed on 10/08/2023. 2nd form started on 04/05/2023 which is still incomplete

[Ashish_Mathur]

Someone else will help you with this.

[P_P2595]

No problem, thank you so much for your time

[v-shex-msft]

Hi @P_P2595 ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng