Getting two attachments in same row for one ID

[Bharathi_99]

I have ID column, attachment column and URL Column

For one ID I will be have multiple attachments and there will be URL for that ID also.

I have two show all Attachments in single line for that ID with comma separated and that attachment should be clickabale URL. I have created one summary table to get columns of Attachment and URL with comma separated in same line as below from Existing Table. But when I give URL to text in Web URL in visual Format, It's not working

Attachments =

SUMMARIZE(

    'Responses',

    'Responses'[ID],

    "Attachment_Name", CONCATENATEX(

        'Responses',

        'Responses'[Attachment Name],

        ", "

    ),

    "Attachment_URLs", CONCATENATEX(

        'Responses',

        'Responses'[URL],

        ", "

    )

)

I want to get Attachment with comma separated for one ID in same line and when I hover on to particular Attachment text it should show respective URL and when I click on it it should navigate to that URL

Ex:   ID        Attachment
       101     July.png, July.docx

In above two attachments are coming in same line with comma separated , when i click on July.png it should go to png image link website and when i click on July. docx it should go to docx website page

Please help me 
Thanks in advance!

[v-nuoc-msft · Accepted solution]

Hi @Bharathi_99 

Thank you very much saud968  for your prompt reply.

For your question, here is the method I provided:

Here's some dummy data

"Responses"

Create a new table.

AttachmentsWithLinks = 
SUMMARIZE(
    'Responses',
    'Responses'[ID],
    "Attachment_Links", CONCATENATEX(
        'Responses',
        "<a href='" & 'Responses'[URL] & "' target='_blank'>" & 'Responses'[Attachment Name] & "</a>",
        ", "
    )
)

Go to the Visualizations pane and click "Get more visuals".

Search for and install Shielded HTML Viewer.

Adding an HTML Viewer to a Report.

This way, each attachment name is a clickable link that opens the corresponding URL in the browser.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[saud968]

To achieve this in Power BI, you can use DAX to create a calculated column that combines the attachment names and URLs into a single string with HTML anchor tags. This way, each attachment name will be clickable and will navigate to the respective URL. Here's how you can do it:

Create a new calculated column in your table with the following DAX formula:
Attachments_HTML =
VAR Attachments =
CONCATENATEX(
'Responses',
"<a href='" & 'Responses'[URL] & "' target='_blank'>" & 'Responses'[Attachment Name] & "</a>",
", "
)
RETURN Attachments
Use this new column in your visual. When you add this column to your table or matrix visual, make sure to set the data category of this column to "Web URL" in the modeling tab.

Enable HTML content in your visual. Go to the format pane of your table or matrix visual, and under the "Values" section, turn on the "URL icon" option. This will render the HTML content as clickable links.

This approach will concatenate all attachments for each ID into a single line with comma separation, and each attachment name will be a clickable link that navigates to the respective URL.

Here's an example of how the output will look:

ID Attachments
101 <a href='http://example.com/july.png' target='_blank'>July.png</a>, <a href='http://example.com/july.docx' target='_blank'>July.docx</a>

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[Bharathi_99]

It's not working. It's not coming as URL
My output should be have text which is like a clickable url  

[saud968]

Let's try a different approach to ensure the attachments are displayed as clickable URLs in Power BI. We'll use a combination of DAX and Power Query to achieve this.

Step 1: Create a Calculated Column for HTML Links
First, create a new calculated column in your table with the following DAX formula:

Attachments_HTML =
VAR Attachments =
CONCATENATEX(
'Responses',
"<a href='" & 'Responses'[URL] & "' target='_blank'>" & 'Responses'[Attachment Name] & "</a>",
", "
)
RETURN Attachments
Step 2: Enable HTML Content in Power Query
Go to Power Query Editor:

In Power BI Desktop, click on Transform Data to open the Power Query Editor.
Add a Custom Column:

In the Power Query Editor, select your table and click on Add Column > Custom Column.
Use the following formula to create a custom column that combines the attachment names and URLs into HTML links:
= Table.AddColumn(#"PreviousStepName", "Attachments_HTML", each Text.Combine(List.Transform([Attachments], each "<a href='" & [URL] & "' target='_blank'>" & [Attachment Name] & "</a>"), ", "))
Replace #"PreviousStepName" with the name of the previous step in your query.

Close and Apply:
Click on Close & Apply to save your changes and return to Power BI Desktop.
Step 3: Use the HTML Column in Your Visual
Add the HTML Column to Your Visual:

Add the Attachments_HTML column to your table or matrix visual.
Enable HTML Content:

Go to the format pane of your table or matrix visual.
Under the Values section, turn on the URL icon option.
This should render the HTML content as clickable links in your visual. Each attachment name will be displayed as a clickable URL that navigates to the respective link.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[Bharathi_99]

After adding Custom column , it came as table and I expanded Attachments_Html column, it given below error

Expression.Error: We cannot convert the value null to type List.
Details:
Value=
Type=[Type]

we have nulls also in Attachment Filename column

Could you please help...