importing data from powerbi to PowerApp

[Anonymous]

I am creating a powerapp to be used on a powerBI page (via powerapps visual) to pull data from the dashboard.  The aim of the app is to send a scorecard to each team member depending on various data available in the dashboard.

I have been able to pull data from columns to the app that have been used in the visual on the dashboard.  When we are selecting a team member on the matrix, the related filtered data is being pulled to the app screen.

In the app, I have used few labels in the gallery that are displaying the selected data on the dashboard (ThisItem.SharedColumnData).  I again pulled this data (shared data from power BI dashboard) to a text input box (Gallery1.Selected.SharedColumnData) that is being used as an email body.  Here, I am sending mail through a button using - Office365Outlook.SendEmail(EmailAddressTextInputBox.Text, SubjectTextInputBox.Text, EmailBodyForMember.Text). 

Q1- I have been so far able to pull single entity data to the app like total score, average score, etc, but can we pull the course name table data to the app?

Q2- Can we create a table in the email body text and can we import data to this table through any connection?

[v-siky-msft]

Hi @Anonymous ,

What is the relationship between course, team member and score tables?

If there are related, you can use LookUp function to retrieve the Course Name based on the common field between these tables.

If you want to create a table in the email body, please refer to this blog to learn how to create a tabular data by HTML.

Hope this helps.

Sik

[Anonymous]

@v-siky-msft 

TABLE - 1

ID    NAME

1         X

2         Y

3         Z

TABLE - 2

ID         COURSE_ID

1            M                   

2            N                   

3            O                   

1            N                   

2            M                 

TABLE - 3

COURSE_ID     COURSE NAME

M                       EFGH

N                        JKLM

0                        OPQR

The relationships are as follows:

TABLE 2 -> many to one -> TABLE 1

TABLE 2 -> many to one -> TABLE 3

[v-siky-msft]

Hi @Anonymous ,

Please elaborate more about your requirement.

Where do you want to show the course name?

Which table do these scores column belong to?

You can use the following syntax to reference the related value, Ex: First(Table2).'COURSE_ID'.'COURSE NAME'

Sik

[Anonymous]

@v-siky-msft 

Scores column is in the table 2.

As mentioned earlier, I want to pull this data to the app so that this can be send as a table to the members .  I want it as a table form in the email as follows.

Dear member,

                 You have completed the following courses this far.

MNOP

QRST

WXYZ

but the issue here is we are so far able to pull only single entity data like, total score, total hours, etc.  So, I was wondering if a custom measure can give us a list like [MNOP, QRST, WXYZ] that would be great.  At least, we can send the mail stating as follows: 

Dear member,

                 Here is the list of courses you have completed so far : [MNOP, QRST, WXYZ]

                 This is your total score:  XXXXX.

                 Total no of hours you have spent on these courses:  XXXXX.

On the other hand, if we can bring a matrix directly into the mail, that would be great.

NOTE:  I am currently exploring the html option you shared and yet to find a solution.

[v-siky-msft]

Hi @Anonymous ,

Please share the code about how do you define the body text. I need to provide a solution according to your formula usage.

In general, you can  try this syntax to reference the course name.

Ex: First(Table2).'Course_ID'.'Course Name' or ThisItem.'Course_ID'.'Course Name'

Sik

[Anonymous]

@v-siky-msft 

First, I used the text input box to be included with the office365connection with the following code.

"Hi " & Gallery1.Selected.Full_Name & "," & Char(13) & Char(13) & "Below is the summary of your professional course analysis." & Char(13) & Char(13)& "Overall summary:" & Char(13)& "Total no of courses you have completed:  " & Gallery1.Selected.'Count Unique Courses' & "." & Char(13) & "Your total score:  " & Gallery1.Selected.TotalScore & "." & Char(13) & "Your average score:  " & Gallery1.Selected.'Average Score' & "." & Char(13) &

Char(13) & "Last quarter summary:" & Char(13) &

Char(13) & Char(13) & "Thanks and Regards" & Char(13) & "Professional Training Department" & Char(13) & Char(13) & "This is a test mail for an ongoing app to check whether users are able to receive mail through this app.  If your are receiving this mail, please acknowledge the receipt of the mail to the sender and disregard the mail."

but here I am unable to input a table that has to be send to the members

Then, I used the html input box with the following code from this page.  I am new to HTML, so first I was trying to create a table.  The first row is able to be created with the following codes.

"<strong> Items: </strong>" &

"<table width='100%' border='1' cellpadding='5' style='border:1px solid black; border-collapse:collapse'>" &

     "<tr style='background-color:#efefef'>

         <th> AverageScore </th> <th> Hours </th> <th> TotalScore </th>

     </tr>

     <tr>" &

"</Table>"

but when I am using PowerBIIntegration data in the following part of that html code:

"<strong> Items: </strong>" &

"<table width='100%' border='1' cellpadding='5' style='border:1px solid black; border-collapse:collapse'>" &

     "<tr style='background-color:#efefef'>

         <th> AverageScore </th> <th> Hours </th> <th> TotalScore </th>

     </tr>

     <tr>" &

              "<td>" & First([@PowerBIIntegration].Data).'Average Score' & " </td>

              <td>" & First([@PowerBIIntegration].Data).TotalHoursOfTraining & " </td>

              <td>" & First([@PowerBIIntegration].Data).TotalScore & " </td>", "</tr><tr>") &

"</Table>"

it is showing me error at the comma ( " </td>", "</tr><tr>") as (Unexpected characters.  Characters used in this formula in an unexpected way).  it is also not taking Gallery1_1.Selected.TotalScore in place of First([@PowerBIIntegration].Data).TotalScore.  I am not sure if there is anything wrong with the html code!!

[v-siky-msft]

Hi @Anonymous ,

Modify the HTML Table code as follows:

"<strong> Items: </strong>" &
"<table width='100%' border='1' cellpadding='5' style='border:1px solid black; border-collapse:collapse'>" &
     "<tr style='background-color:#efefef'>
         <th> AverageScore </th> <th> Hours </th> <th> TotalScore </th>
     </tr>
     <tr>" &
              "<td>" &  First([@PowerBIIntegration].Data).'Average Score'  & " </td>
              <td>" & First([@PowerBIIntegration].Data).TotalHoursOfTraining & " </td>
              <td>" & First([@PowerBIIntegration].Data).TotalScore &  "</td> 
    </tr>" &
"</Table>"

if you want to show the course name, please try this: 

First([@PowerBIIntegration].Data).'Course_ID'.'Course Name' 

 OR

LookUp('Table2', 'Average Score'=First([@PowerBIIntegration].Data).'Average Score','Course_ID'.'Course Name' )

Hope this helps.

Sik

[Anonymous]

@v-siky-msft .  Thanks in advance for your cooperation so far.

The HTML code is not working for me.  I am able to get the table headings with this.

"<h3> Hi </h3>" &

"<strong> Scorecard: </strong>" &
"<table width='100%' border='1' cellpadding='5' style='border:1px solid black; border-collapse:collapse'>" &
"<tr style='background-color:#efefef'>
<th>Average Score</th> <th> Total Score </th> <th> No of Courses </th>
</tr>
<tr>" &

"</table>"

but I want to add the below data to the table.

First([@PowerBIIntegration].Data).Average Score

First([@PowerBIIntegration].Data).TotalScore

First([@PowerBIIntegration].Data).Count Unique Courses

Can you please suggest the required change in code?

[v-siky-msft · Accepted solution]

Hi @Anonymous ,

Just add the value between <td> </tr> inside the <tr> </tr> tags.

"<h3> Hi </h3>" &

"<strong> Scorecard: </strong>" &
"<table width='100%' border='1' cellpadding='5' style='border:1px solid black; border-collapse:collapse'>" &
"<tr style='background-color:#efefef'>
<th>Average Score</th> <th> Total Score </th> <th> No of Courses </th>
</tr>
<tr><td> " &
First([@PowerBIIntegration].Data).Average Score&"</td> <td> "&First([@PowerBIIntegration].Data).TotalScore&"</td> <td> "& First([@PowerBIIntegration].Data).Count Unique Courses &"</td></tr> "&
"</table>"

Hope this helps.
Sik