Did you receive an error message from Power BI that your data source contains duplicates? This is how you fix it.
How does the error message look like?
What causes this duplicate error?
The cause for this error is the unique IDs in Fortnox Projects* and Fortnox Articles.
But how can there be duplicates among unique IDs, you may ask, and rightfully so.
Answer: Fortnox IDs are case sensitive and Power BI is not. This mean that Fortnox doesn’t see “abc123” and “ABC123” as duplicates, but Power does.
* Our data models are automatically filtering out duplicate project by ignoring the oldest one. If you need to reach all projects, including the duplicate, please use the raw data in fortnox_api.
How can I fix it?
You need to either
- Remove the duplicated records in Fortnox or
- filter them out in Power BI.
Remove duplicates in Fortnox
Log in to Fortnox and find the records in Projects or Customers. Delete all expect one with the same non case sensitive name. From the example above, either “abc123” or “ABC123” needs to go.
You won’t be able to delete records if they have related records like invoices or vouchers. The you can use the next alternative.
Filter out duplicates in Power BI
Go to the “Power Query Editor” by clicking “Edit query” from the table menu. You are probable looking for table “dim_projects” or “dim_articles”.
There we have prepared a filter for you.
- Add all IDs in the list ValuesToExclude (marked green)
- Remove the comment signs
//(marked red) - Make sure you have a comma [,] after each statement.
- Replace the last parameter, e.g. “dim_projects_View” with “FilteredTable”.
Click “Done”, then “Close & Apply” and refresh the data.
If you can’t see the filter part in your query, you can copy it from below.
Projects
// Define the list of projectId to exclude
ValuesToExclude = {"Value1", "Value2", "Value3"},
// Filter the Project table to exclude rows with "projectId" in the list above
FilteredTable = Table.SelectRows(dim_projects_View, each not List.Contains(ValuesToExclude, [projectId]))
Articles
// Define the list of projectId to exclude
ValuesToExclude = {"Value1", "Value2", "Value3"},
// Filter the Project table to exclude rows with "projectId" in the list above
FilteredTable = Table.SelectRows(dim_articles_View, each not List.Contains(ValuesToExclude, [ArticleId]))
Can I get a list of all duplicates?
You sure can. Simple one of these queries in Google Bigquery. Need help, just add a reply below.
Ps. Remember to replace [workspaceId] with your unique Workspace ID.
Projects
SELECT
p.OrgId,
Name,
LOWER(ProjectNumber) ProjectId,
COUNT(*) count
FROM
`enhanza.fortnox_api_[workspaceId].projects` p
LEFT JOIN
`enhanza.fortnox_api_[workspaceId].company_settings` c
ON
p.OrgId = c.OrgId
GROUP BY
OrgId,
ProjectId,
Name
HAVING
COUNT(*) > 1
Articles
SELECT
a.OrgId,
Name,
LOWER(ArticleNumber) ArticleNumber,
COUNT(*) count
FROM
`enhanza.fortnox_api_[workspaceId].articles` a
LEFT JOIN
`enhanza.fortnox_api_[workspaceId].company_settings` c
ON
a.OrgId = c.OrgId
GROUP BY
OrgId,
ArticleNumber,
Name
HAVING
COUNT(*) > 1
