cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jferrando Frequent Visitor
Frequent Visitor

Extract part of a field with different content

Hi! I'm quite new in Power BI and yet discovering its amazing possibilities.

 

Right now I'm creating a monitoring dashboard for a backup service. I connected to an Exchange account and read it's backup confirmation emails. This emails are sent from different platforms and have different structures.

 

What I'm trying to do is to get the JOB_NAME from every email subject. The structures I have are the following:

 

JOB_NAME - Copia de seguridad issues

JOB_NAME: issues

Copia JOB_NAME - Issues

Copia JOB_NAME: issues

Informe de copia [issues] > client > JOB_NAME > date, issues

Copia programada, client > JOB_NAME > date, issues

Alerta de BE: Issues (Servidor: Server_name) (Tarea:JOB_NAME)

Job JOB_NAME "completed:" Issues

[Job_result] JOB_NAME (VM_count) Issues

 

Each line is one kind of subject. I have tried to extract text between delimiters, but this means I have to creats a column for each kind of subject line. 

 

Have you any idea of how can I do this? Can I have a column that reads the subject_field and gets JOB_NAME?

 

Thanks!

6 REPLIES 6
Community Support Team
Community Support Team

Re: Extract part of a field with different content

HI @jferrando,

 

It is hard to clarity your requirement without any sample data, can you please share more detailed informations?

 

For your requirement, you should ensure where the fields stored, then use records function to extract specific fields values.

Record functions

 

Regards,

xiaoxin Sheng

 

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



For learning resources/Release notes, please visit: | |
jferrando Frequent Visitor
Frequent Visitor

Re: Extract part of a field with different content

Hi! Thanks for your reply

 

What I try to do is to identify the Job_Name on every email sent by each platform. For instance, I receive email sent from Veeam Backup software with this structure on the subject:

 

[Failed] serverDC - serverMAIL (2 VMs) 2 failed

or

Job serverDC- serverMAIL completed: Failed

 

What I need to do is to get the Job_Name (server DC - serverMAIL) in a column wich is between [Failed] and (2VMs) in the first case, and between Job and completed: in the second case.

 

In this examples I need to isolate Job_Name with this structure, but I also use other backup platforms wich send email using another subject structure. Actually, if we find the way to isolate Job_Name in this examples, I guess we'll be able to do it with the rest of the subjects.

 

Thank you

Community Support Team
Community Support Team

Re: Extract part of a field with different content

HI @jferrando,

 

Can you please share some sample data and actual transfer result for analysis? It is hard to clarify your issue from your description.

Notice: please do mask sensitive data before sharing. 

 

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |
jferrando Frequent Visitor
Frequent Visitor

Re: Extract part of a field with different content

Hi, thanks for your reply and attention.  This is the kind of subjects i receive, and from all of them I need to get the text underlined:

 

  • [Failed] BackupUSB - Setmanal (3 VMs) 3 failed
  • [Warning] serverDC - serverMAIL (3 VMs) 1 warning
  • [Failed] BackupUSB - Setmanal (3 VMs) 3 failed
  • Job ServerSAP - serverSQL completed: Failed
  • Job serverDC - serverFS completed: Failed
  • VM backup job ServerSQL completed: Failed
  • VM backup job ServerFS completed: Success
  • Alerta de Backup Exec: Fallo de tarea (Servidor: "SRVOR") (Tarea: "Backup Company")
  • Alerta de Backup Exec: Fallo de tarea (Servidor: "SRVDC") (Tarea: "Copia de Seguretat")
  • Còpia programada, ABC Company> SAP - CLOUD > 2017-11-20-14-30-00, s'ha perdut
  • Còpia programada, ABC Company> PACO-PC > 2017-11-17-14-00-00, s'ha perdut
  • Copia NAS - ABC Company: Backup completado con exito
  • COPIA ABC Company: Backup completado con exito
  • Copia ServerDC (Dades) a NAS Synology - Copia de seguridad finalizada con errores
  • Datos - ABC Company - Copia Datos - Disco NAS - Copia de seguridad finalizada con errores
  • Copia de Seguretat - Copia de Segureta DIARIA - NAS i USB - Copia de seguridad completada correctamente

What I have identified is the structure of all kind of subjects, and pasted on the messages before. I've been testing with FIND() to find the beggining and ending of each Job_Name so I could try to find it, but I'm an amateur Power BI user and I'm a bit lost Smiley Happy

 

Thank you!

Super User
Super User

Re: Extract part of a field with different content

Forget PowerBI for a second, how you do will otherwise? I don't see any standard pattern and that will make it hand. Although there is find function for DAX which can help you.

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





jferrando Frequent Visitor
Frequent Visitor

Re: Extract part of a field with different content

Based on the example befor, I would ask Power BI something like:

 

For: [Failed] BackupUSB - Setmanal (3 VMs) 3 failed

I'd ask: Get all text between "] " and " ("

 

For: Job ServerSAP - serverSQL completed: Failed

I'd ask: Get all text between "Job " and "completed:"

 

For: VM backup job ServerSQL completed: Failed

I'd ask: Get text between "backup job" and "completed:"

 

For: Alerta de Backup Exec: Fallo de tarea (Servidor: "SRVOR") (Tarea: "Backup Company")

I'd ask: Get all text between "Tarea: "" and "")"

 

And so on. There is about 9 different subject structures

 

Thanks!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 386 members 3,347 guests
Please welcome our newest community members: