Extract part of a field with different content

[jferrando]

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!

[v-shex-msft]

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

[jferrando]

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

[v-shex-msft]

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

[jferrando]

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 🙂

Thank you!

[parry2k]

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.

[jferrando]

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!