Data Connecting Failed

[jjasso]

I'm currently using PowerBI's free trial.  I'm using the finance app and I am trying to connect the data from Business Central but I keep getting an error that says the refresh failed.

This is the email message I received after a few hours:

An error occurred while processing the data in the semantic model.

Refresh failed:

Finance app has failed to refresh.
Failure details: The last refresh attempt failed because of an internal service error. This is usually a transient issue. If you try again later and still see this message, contact support.

Column '<oii>G/L Account No.</oii>' in Table '<oii>G/L Account</oii>' contains a duplicate value '<ccon>1205</ccon>' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.

I don't have a duplicate G/L so that can't be the error.  I'm new to PowerBI so I have no idea how to fix this error.  Please help!

[v-pnaroju-msft]

Thankyou, @GilbertQ, @BhavinVyas3003 for your response.

Hi jjasso,

We sincerely appreciate your inquiry posted on the Microsoft Fabric Community Forum.

Based on my understanding, the error message, "Column 'G/L Account No.' contains a duplicate value '1205'..." arises due to the following reasons:
a. The G/L Account No. column is employed in a one-to-many relationship, where Power BI mandates unique values on the one-side of such relationships.
b. Although no visible duplicates may exist in Business Central, Power BI can identify "hidden duplicates" such as trailing spaces (for example, "1205" versus "1205 "), case mismatches, or invisible characters.

Kindly follow the steps outlined below, which may help to resolve the issue:

1. Open Power BI Desktop and navigate to Transform Data.
2. In the G/L Account table, select the G/L Account No. column and apply the following transformations: Transform > Format > Trim, Transform > Format > Clean, and optionally, Transform > Format > UPPERCASE.
3. Use the Group By function on the G/L Account No. column to check for duplicate values. Ensure that this column now contains only unique values.
4. Re-examine the model’s relationships and confirm that the G/L Account No. field is positioned on the one-side and is unique.
5. Apply the changes and refresh the report.

Furthermore, please refer to the following links for additional information:
Introduction to Business Central and Power BI - Business Central | Microsoft Learn
Building Reports in Power BI Desktop to Display Business Central Data - Business Central | Microsoft...
Enabling Power BI integration with Business Central - Business Central | Microsoft Learn

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Should you have any further questions, please feel free to contact the Microsoft Fabric community.

Thank you.

[BhavinVyas3003]

The error means the G/L Account No. column (e.g., value 1205) is not unique, which is required for a one-to-many relationship or primary key. Even if it looks unique in Business Central, hidden duplicates (like trailing spaces or case differences) can cause this.

Please check for:

• Duplicates in Power BI (Power Query: Group By G/L Account No. → Count > 1)
• Inconsistent formatting (spaces, case)
• Relationship issues in the model

Fix the duplicates or adjust the relationship, then republish and refresh.

[GilbertQ]

Hi @jjasso 

Based on your above, you have a GL account that has got a duplicate value. Meaning, if you had to go into your table in business central, you would see that the account 1205 is on more than one row. You would need to resolve this issue before it would successfully refresh.