Daily email text as input to BI report

[MitieFred]

In Outlook, on a daily basis, I receive a license report,  which I have been asked to create a graph from, showing the fluctuations in license requirements.  Please can someone advise me how I can take an email (saved as CSV  or not ?) and convert what appears to be row input into columns, propagating the date and picking out the values for the different "LICENSETYPE" entries ? 

From: Licensing Service Account
Sent: 22 August 2019 05:01
To: Recipients
Subject: Software Licence Report

Poduct Licence Report
See below for current Product licence position
Licence: LICENSETYPE1
Total Licences: 246
Current Users: 191
Licence: LICENSETYPE2
Total Licences: 6
Current Users: 5
Licence: LICENSETYPE3
Total Licences: 164
Current Users: 151

ending up with something like the following, manually created, data

Thanks in advance for any suggestions/pointers

[Ani1991 · Accepted solution]

Hi @MitieFred,

I tried to reproduce the scenario wherein I sent an email to myself with the email text as the one provided by you and saved that email as a text file 

Then in Power BI import this text file via get data (select the Delimiter as Colon : ) and click on Transform data. Select the necessary rows and click on Transpose under the Transform tab. Remove the unnecessary columns and click on Use First Row as Headers next to the Transpose option.

You will get the data as below,

Hope this helps.

Regards,

Ani

[v-diye-msft · Accepted solution]

Hi @MitieFred ,

It seems you'd like to get the result dynamically rather than manual input it each time.

You can use the solution provided by @Ani1991 , but make some modification in the M code about source place. 

For example: the folder contains multiple text which are created automatically. And you used Folder data source to connect to this folder and append those files.

 You can create a query connect to the folder, then open Advanced Editor, define a function in M query, then pass dynamic file path and file name within that folder. For more information, please refer to this article: Using Power Query to Combine Data from Multiple Excel Files into One Table .

[tsibilski · Accepted solution]

Save your messages in a specific folder.

Change one of them from msg to .csv. 

Create a query like it was fo CSV/TXT files filtering only the CSV files.

Once it works, change the filter from CSV to MSG and the extension of the message back to .msg.

You'll be able to get full text of the email. 

[Ani1991 · Accepted solution]

Hi @MitieFred,

I tried to reproduce the scenario wherein I sent an email to myself with the email text as the one provided by you and saved that email as a text file 

Then in Power BI import this text file via get data (select the Delimiter as Colon : ) and click on Transform data. Select the necessary rows and click on Transpose under the Transform tab. Remove the unnecessary columns and click on Use First Row as Headers next to the Transpose option.

You will get the data as below,

Hope this helps.

Regards,

Ani

[MitieFred]

Thanks @Ani1991 that added the data from the email and I've got the format/columns all set. 

How do I go about adding subsequent days data to that, as I want to create a history, rather than overwrite the record each day ?

Thanks again

Fred

[v-diye-msft · Accepted solution]

Hi @MitieFred ,

It seems you'd like to get the result dynamically rather than manual input it each time.

You can use the solution provided by @Ani1991 , but make some modification in the M code about source place. 

For example: the folder contains multiple text which are created automatically. And you used Folder data source to connect to this folder and append those files.

 You can create a query connect to the folder, then open Advanced Editor, define a function in M query, then pass dynamic file path and file name within that folder. For more information, please refer to this article: Using Power Query to Combine Data from Multiple Excel Files into One Table .

[MitieFred]

Thanks @v-diye-msft  and @Ani1991  for your suggestions and pointers

I will do some research and try to progress

Regards

Fred

[Ani1991]

Hi @MitieFred,
You would have to go in the same way as suggested by @v-diye-msft. 
Kindly let me know in case you have any further concerns and issues and would be happy to help.

Thanks,

Ani