topic Re: Data refresh error. October 2020 last version in Report Server

Data refresh error. October 2020 last version

declanR — Tue, 01 Dec 2020 10:34:50 GMT

Hi!

Com error: mscorlib, An item withe the same key has already been added. Power BI Desktop and PowerBi report server last version.

Source - sql server request. Merging with a table based on a query gives this error on PowerBI Report Server

Re: Data refresh error. October 2020 last version

declanR — Tue, 01 Dec 2020 10:43:40 GMT

Re: Data refresh error. October 2020 last version

declanR — Tue, 01 Dec 2020 10:48:16 GMT

Re: Data refresh error. October 2020 last version

declanR — Tue, 01 Dec 2020 10:51:19 GMT

table 3 - sql request:

SELECT
ul.user_id,
ul.timestamp AS timestamp_st,
ul.value_new
FROM user_log_data ul
LEFT JOIN users u
ON ul.user_id = u.id
WHERE ul.field = 'level'
AND u.regdate >= '2014-01-01'
AND ul.timestamp >= '2020-07-27 14:30:30.0000000'
AND u.is_active = 1

Re: Data refresh error. October 2020 last version

declanR — Tue, 01 Dec 2020 10:51:38 GMT

table 6 - sql request:

SELECT
*
FROM (SELECT
ub1.user_id,
MAX(ub1.timestamp) AS max_timestamp_b,
ub1.week
FROM (SELECT
ub.user_id,
ub.timestamp,
DATEPART(WEEK, ub.timestamp) AS week
FROM user_barcode ub
WHERE ub.timestamp >= '2020-07-27') AS ub1
LEFT JOIN users u
ON user_id = u.id
WHERE u.regdate >= '2014-01-01'
AND u.is_active = 1
GROUP BY ub1.user_id,
ub1.week) AS ub2

Re: Data refresh error. October 2020 last version

Anonymous — Tue, 01 Dec 2020 11:51:45 GMT

I'm also encountering this same error in one of my report.

However in my research I've seen that it could potentially be caused by having 2 columns with the same name, which would be the case in your "table 6" (both user_id and week). You could try renaming both of them in the query and see if it fixes the issue.

If it ends up being the solution it's a real problem that Desktop and Report Server do not have the same behavior.

Re: Data refresh error. October 2020 last version

declanR — Tue, 01 Dec 2020 12:47:01 GMT

Unfortunely, the solution doesn't work. Replase query "table 6" with simple query:

SELECT user_id as user1 FROM user_barcode. The error is the same.

Re: Data refresh error. October 2020 last version

Anonymous — Wed, 02 Dec 2020 13:58:14 GMT

Hello DeclanR,

I managed to find a solution for my issue, I'm not sure if it will work for you but it's worth a try.

In short, our datasources were created with different methods (1 line vs 2 lines) even thought they connected to the same DB and when we merged them we would get this error. See this thread for a more detailed explanation: https://community.powerbi.com/t5/Report-Server/COM-error-mscorlib-An-item-with-the-same-key-has-already-been/m-p/1524902#M16968

Re: Data refresh error. October 2020 last version

declanR — Fri, 04 Dec 2020 06:03:44 GMT

Hi Jean. This is the first thing I tried. Unfortunately, not working for me.

Re: Data refresh error. October 2020 last version

Icey — Tue, 12 Jan 2021 06:33:43 GMT

Hi declanR ,

The latest version has been released. Version: 1.9.7675.15620 (Build 15.0.1104.300), Released: January 8, 2021*

It fixed some bugs:

- Fixed issue with refresh of reports with two or more data sources that differ only by the casing of the letters.
- Fixed issue with refresh of reports certain combinations of nested joins.

Please update and check if it solves the issue you are experiencing.😊

Reference: Power BI Report Server October 2020 update 2 - Microsoft Power BI Community

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

Re: Data refresh error. October 2020 last version

declanR — Tue, 12 Jan 2021 06:39:29 GMT

Thanks, MS. My big pain is resolved (maybe). Testing now

Power BI Report Server

Version: 1.9.7675.15620 (Build 15.0.1104.300), Released: January 8, 2021
- Bug fixes
  - Fixed issue with refresh of reports with two or more datasources that differ only by the casing of the letters.
  - Fixed issue with refresh of reports certain combinations of nested joins.

Re: Data refresh error. October 2020 last version

Icey — Thu, 14 Jan 2021 06:44:21 GMT

Hi declanR ,

Does the latest version solve the issue you encountered?

Best Regards,

Icey