cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

importing data from powerbi to PowerApp

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.

Screenshot 2020-06-13 at 10.08.07 AM.png

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: importing data from powerbi to PowerApp

Hi @ogh_sabyasachi ,

 

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

View solution in original post

11 REPLIES 11
Highlighted
Community Support
Community Support

Re: importing data from powerbi to PowerApp

Hi @ogh_sabyasachi ,

 

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

 

Highlighted
Helper II
Helper II

Re: importing data from powerbi to PowerApp

@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

 

Highlighted
Community Support
Community Support

Re: importing data from powerbi to PowerApp

Hi @ogh_sabyasachi ,

 

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

 

Highlighted
Helper II
Helper II

Re: importing data from powerbi to PowerApp

@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.

Highlighted
Community Support
Community Support

Re: importing data from powerbi to PowerApp

Hi @ogh_sabyasachi ,

 

 

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

 

Highlighted
Helper II
Helper II

Re: importing data from powerbi to PowerApp

@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!!

Highlighted
Community Support
Community Support

Re: importing data from powerbi to PowerApp

Hi @ogh_sabyasachi ,

 

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

Highlighted
Helper II
Helper II

Re: importing data from powerbi to PowerApp

@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?

 

 

Highlighted
Community Support
Community Support

Re: importing data from powerbi to PowerApp

Hi @ogh_sabyasachi ,

 

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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,674)