cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MitieFred Member
Member

Daily email text as input to BI report

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

Capture.PNG

2 ACCEPTED SOLUTIONS

Accepted Solutions
Ani1991 Member
Member

Re: Daily email text as input to BI report

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 
image.png

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.

image.png

You will get the data as below,

image.png

image.png

 

Hope this helps.

 

Regards,

Ani

View solution in original post

Community Support Team
Community Support Team

Re: Daily email text as input to BI report

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 .

 

 

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

View solution in original post

5 REPLIES 5
Ani1991 Member
Member

Re: Daily email text as input to BI report

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 
image.png

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.

image.png

You will get the data as below,

image.png

image.png

 

Hope this helps.

 

Regards,

Ani

View solution in original post

MitieFred Member
Member

Re: Daily email text as input to BI report

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

Community Support Team
Community Support Team

Re: Daily email text as input to BI report

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 .

 

 

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

View solution in original post

Ani1991 Member
Member

Re: Daily email text as input to BI report

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 

MitieFred Member
Member

Re: Daily email text as input to BI report

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

 

I will do some research and try to progress

 

Regards

Fred

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 410 members 3,854 guests
Please welcome our newest community members: