Answered

Flow, need to change headers in HTML table (data provided from Powerbi Query)

(0) Share

Report

Posted on by ChrisWF

Currently I have a flow, that querys against a dataset in powerbi and changes it to an HTML table and attaches it to an email and sends it out. this is all working. I recently was asked to format it in table, this also works. my current problem however is its using the headers from what is generated in powerbi and I can't seem to nail down changing these headers in HTML.

My flow

Everything is working up until changing the names of the headers.

here is an example of the output.

Currently I have a flow, that query's against a dataset in powerbi and changes it to an HTML table and attaches it to an email and sends it out. this is all working. I recently was asked to format it in table, this also works. my current problem however is its using the headers from what is generated in powerbi and I can't seem to nail down changing these headers in HTML.

All responses (3)

Answers (1)

ChrisWF 11 on at

Like (0)

Report

Re: Flow, need to change headers in HTML table (data provided from Powerbi Query)

Sure thing, my situation required me to build a table out of Dax first summarizing two different tables together.

Which looks like this
FILTER(
ADDCOLUMNS(
SUMMARIZE(
tbl_WorkPackDrawings,
'tbl_WorkPackDrawings'[Work Package],
'tbl_WorkPackDrawings'[Drawing],
'tbl_WorkPackDrawings'[Revision],
"MaxRev", MAX('tbl_DrawingsACC'[Rev])
),
"Dif", IF([Revision] <> [MaxRev], TRUE(), FALSE()),
"Superintendent",
CALCULATE(
VALUES('tbl_workpackages'[Superintendent]),
USERELATIONSHIP('tbl_WorkPackDrawings'[Work Package], 'tbl_WorkPackages'[WP Number])
),
"Foreman",
CALCULATE(
VALUES('tbl_workpackages'[Foreman]),
USERELATIONSHIP('tbl_WorkPackDrawings'[Work Package], 'tbl_WorkPackages'[WP Number])
)
),
[Dif] = TRUE()
)

After that in order to get the Dynamic content to work in the flow. I built a visual for that table.
and used the record function under Optimize>Performance Analyzer. and the dax code for said visual came out as such.

// DAX Query
DEFINE
VAR __DS0Core =
SUMMARIZE(
'tbl_WorkPackDrawingRevisions',
'tbl_WorkPackDrawingRevisions'[Work Package],
'tbl_WorkPackDrawingRevisions'[Drawing],
'tbl_WorkPackDrawingRevisions'[Revision],
'tbl_WorkPackDrawingRevisions'[MaxRev],
'tbl_WorkPackDrawingRevisions'[Dif],
'tbl_WorkPackDrawingRevisions'[Superintendent],
'tbl_WorkPackDrawingRevisions'[Foreman]
)

VAR __DS0PrimaryWindowed =
TOPN(
501,
__DS0Core,
'tbl_WorkPackDrawingRevisions'[Work Package],
1,
'tbl_WorkPackDrawingRevisions'[Drawing],
1,
'tbl_WorkPackDrawingRevisions'[Revision],
1,
'tbl_WorkPackDrawingRevisions'[MaxRev],
1,
'tbl_WorkPackDrawingRevisions'[Dif],
1,
'tbl_WorkPackDrawingRevisions'[Superintendent],
1,
'tbl_WorkPackDrawingRevisions'[Foreman],
1
)

EVALUATE
__DS0PrimaryWindowed

ORDER BY
'tbl_WorkPackDrawingRevisions'[Work Package],
'tbl_WorkPackDrawingRevisions'[Drawing],
'tbl_WorkPackDrawingRevisions'[Revision],
'tbl_WorkPackDrawingRevisions'[MaxRev],
'tbl_WorkPackDrawingRevisions'[Dif],
'tbl_WorkPackDrawingRevisions'[Superintendent],
'tbl_WorkPackDrawingRevisions'[Foreman]

You can easily get the code for your specific visual using the Record function, steps listed in my resolution post.
Jaylongp5 3 on at

Like (0)

Report

Re: Flow, need to change headers in HTML table (data provided from Powerbi Query)

Could you share your Dax code? I hope I can use it I have a flow similar to yours.
Verified answer

ChrisWF 11 on at

Like (0)

Report
Re: Flow, need to change headers in HTML table (data provided from Powerbi Query)

Solved the problem.

I needed to use a different Dax code through Power Bi to get the dynamic content when creating the HTML table

the steps to get this DAX content are as follows:
Build Visual.
Navigate to Optimize.
Select Performance Analyzer.
Start Recording on Performance Analyzer.
Refresh Visuals
Expand desired table
Copy query.

This newly copied DAX code took the spot of the DAX code I was using in my Power Bi Query previously.

the new Flow looks like this:
Using the copied Dax code from the previous steps allow me to Parse this as a JSON, while the prior code would not.
I had to Utilize "Create CSV table" here but it is only relevant later.
This next step now had the Dynamic content from the JSON that the "First Table Rows" from Power Bi didn't give me access too.
I added a compose section just to format the HTML table into something more present on the eyes for the recipients of the email.

This is also where the CSV tables comes into play. I was unable to get my condition to work using other dynamic content, but I knew from the previous example using the CSV table would trigger a True on my conditional to send the email.

this is the result,

I will also leave the Code I used to format the table here:

<style>
table {
font-family: Arial, Helvetica, sans-serif;
background-color: #EEEEEE;
border-collapse: collapse;
width: 50%;
}
table td, table th {
border: 1px solid #ddd;
padding: 3px 8px; /* Adjust padding as needed */
white-space: nowrap; /* Prevent content from wrapping */
}
table th {
font-size: 15px;
font-weight: bold;
padding-top: 12px;
padding-bottom: 12px;
text-align: left;
background-color: #1C6EA4;
color: white;
}
</style>
"Dynamic content from the body of the HTML table here"