Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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 .
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.
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.
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
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
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 .
Thanks @v-diye-msft and @Ani1991 for your suggestions and pointers
I will do some research and try to progress
Regards
Fred
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |