Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
P_P2595
Helper I
Helper I

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

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

idMeasuredescriptiondate
1Teastart16/08/2023
1parstart15/09/2023
1parend13/12/2023
2parstart14/08/2023
2parend12/09/2023
3parstart12/01/2023
3parend04/05/2023
3Parstart10/08/2023

 

Expected output

idMeasuredescriptionMergeddateCompleted
1TeastartTea:start16/08/2023N
1parstartpar: start, par: end15/09/2023Y
1parendpar: start, par: end13/12/2023Y
2parstartpar: start, par: end14/08/2023Y
2parendpar: start, par: end12/09/2023Y
3ParstartPar:start, Par:end12/01/2023Y
3ParendPar:start, Par:end04/05/2023Y
3Parstartpar: start10/08/2023N

 Thank you in advance

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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.

Ashish_Mathur_0-1712719665728.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

11 REPLIES 11
Ashish_Mathur
Super User
Super User

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.

Ashish_Mathur_0-1712719665728.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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?

P_P2595_0-1712730880404.png

 

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.

Ashish_Mathur_0-1712743898178.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

 

P_P2595_0-1712793192904.png

 

 

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😊

What should the answer be for MRN 28549?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

P_P2595_0-1712803638608.png

 

Someone else will help you with this.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

No problem, thank you so much for your time

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.