Pulling Dynamics 365 "File" type fields into Power BI

[jonclay]

Hi everyone,

I have a couple of fields in my Dynamics 365 CRM that are of the "File" Type. This allows attachments to be directly uploaded to them.

However, when I open the relevent Entity/Table in Power BI using the SQL Server Connection, these two fields do not appear in the list of fields, I assume because of their Type. I don't want to bring the actual attachments in - I just want to put a Yes or No in Power BI if an attachment exists against the field or not in Dynamics 365.

Does anyone have any expeirence / knowledge on how I can achieve getting these fields into Power BI?

Many thanks
Jon

[jonclay]

Hi @123abc 

Fantastic, thank you.

I haven't tried this yet, but the procedure sounds as though it'll work, so I've Accepted it as the Solution.

Best wishes
Jon

[123abc]

Dynamics 365 "File" type fields are used to store attachments or documents related to records in your CRM. If you want to indicate whether an attachment exists for a particular record in Power BI without actually pulling in the attachments, you can follow these general steps:

1. Create a Custom Column in Power Query: Power Query is a data transformation tool within Power BI that allows you to manipulate your data before it's loaded into the data model. You can create a custom column in Power Query that checks whether there's an attachment for each record in your CRM data.

   Here's an example of how you might create a custom column using Power Query:

   • Open Power BI and go to the Power Query Editor (Edit Queries).
   • Locate your Dynamics 365 data source.
   • Add a custom column with a formula that checks if the "File" type fields are empty or not for each record.

   The formula might look something like this:

M Language:

if [AttachmentField1] = null and [AttachmentField2] = null then "No" else "Yes"

1. Replace [AttachmentField1] and [AttachmentField2] with the actual names of your "File" type fields.

2. Load the Data into Power BI: After creating the custom column in Power Query, load the data into Power BI. This custom column will contain "Yes" if there are attachments and "No" if there are none for each record.

3. Use the Custom Column in Your Visualizations: Now, you can use the custom column you created in your Power BI visualizations. You can use it as a filter, in tables, or any other way you want to display the information.

By following these steps, you can indicate whether attachments exist for records in Power BI without actually importing the attachments themselves. Instead, you'll have a "Yes" or "No" indicator based on the presence of attachments in your Dynamics 365 data.

[jonclay]

Hi @123abc 

Many thanks for your quick reply.

This doesn't seem to work for me as I'm unable to see the actual fields in my field list within Power BI, so I can't reference them. 

What I'm doing is:
- going to Transform data
- clicking the Add Column tab

- clicking Custom Column
- creating the code as shown below:

The column is created by the results show Error:

This happens even if I put the entity/table name before the fieldname in the custom field e.g. contact.new_penportraitupload

Am I doing something wrong?

Many thanks
Jon

[123abc · Accepted solution]

I apologize for the confusion. It seems that Power BI's direct query to Dynamics 365 CRM might not support "File" type fields, which could be causing the issue you're encountering. To work around this limitation, you can use Power Automate (formerly known as Microsoft Flow) to periodically extract data from Dynamics 365 CRM and store it in a different data source like SharePoint or a database where the "File" type fields can be processed more easily in Power BI.

Here's a high-level overview of the steps:

1. **Set up a Flow in Power Automate**:
- Create a new flow that triggers on a schedule or when data in your Dynamics 365 CRM entity is updated.

2. **Retrieve Data from Dynamics 365 CRM**:
- Use the "List records" or "Get records" action in Power Automate to retrieve the data you need from your CRM, including the "File" type fields.

3. **Extract Attachments and Store Them**:
- For each record in your Dynamics 365 CRM data, extract any attachments from the "File" type fields. You can save these attachments to a location such as SharePoint or a database table.

4. **Create Calculated Columns in the External Data Source**:
- In the external data source (SharePoint, database, etc.) where you store the extracted data and attachments, create calculated columns to determine if attachments exist and assign "Yes" or "No" values accordingly.

5. **Connect Power BI to the External Data Source**:
- In Power BI, connect to the external data source where you have stored the processed data with the calculated columns.

6. **Use Calculated Columns in Power BI Reports**:
- Now, you can use the calculated columns in Power BI to display whether attachments exist for each record.

This approach allows you to work with the "File" type fields more effectively and achieve your desired outcome in Power BI. Additionally, it provides the flexibility to schedule data updates and automate the extraction of attachments from Dynamics 365 CRM.