Fix duplication issues in Power BI

📚 Documentation Connection guides
,
1

Did you receive an error message from Power BI that your data source contains duplicates? This is how you fix it.

What causes this duplicate error?

The known data sources for this error are 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.

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”.

edit-query-menu-power-bi

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”.

powerbi-power-query-filter
powerbi-power-query-filter2264×952 291 KB

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.

    // 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]))

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