Merge queries returning blank value - intermittent after refresh

[abelanger]

Hello everyone,

I have an intermittent issue after a model refresh where table A is merged with table B using UserID, table A is showing Blank values under UserLogging while the data is there in Table B. After a refresh, the issue disappears and I can now see UserLogging. When the error shows up, I don't see any errors in PowerBI or during the model refresh. 

Note that table B contains a very large number of rows (approximately 1 million rows).

To summarise:

- I can user the right UserID's in table A and B

- Table B contains UserLogin

- The merge for Table A and B works most of the time; however, sometimes there are only blank values for UserLogging in table A

- I validated the data types for UserIDs

- There are no other errors during data model refresh.

Thank you for your help.

Alex

[Anonymous]

Hi @abelanger ,

I understand that you are experiencing intermittent blank values in the UserLogging column of Table A after merging it with Table B using UserID. I'll be happy to assist you with this. To troubleshoot and resolve this issue, please follow the steps below:

1. Check the data types: Ensure that the data types of the UserID column in both Table A and Table B are the same. If they are different, you may encounter issues during the merge. Make sure the data types are compatible.

2. Verify the merge conditions: Double-check the merge conditions to ensure that you are using the correct columns for the merge. Confirm that the UserID column is correctly mapped in both tables.

3. Check for null values: Verify if there are any null values in the UserID column of either Table A or Table B. If there are null values, they may cause the merge to return blank values. You can filter out null values before merging or handle them appropriately in your data model.

4. Optimize performance for large datasets: Since Table B contains a large number of rows, it's essential to optimize the performance of your data model. Consider the following suggestions: - Ensure that your data model is properly indexed for efficient merging. - Check if there are any calculated columns or measures that might be impacting the merge performance. Consider optimizing or removing them if necessary. - Evaluate the performance of your hardware and infrastructure to ensure it can handle the size of your dataset.

5. Monitor refresh logs: During the model refresh, monitor the logs for any warnings or errors that might provide insights into the intermittent issue. Check the Power BI logs, as well as any other relevant logs, for any potential errors or warnings related to the merge operation.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[abelanger]

@PijushRoy I'm a little bit concerned as Table A is used to apply RLS and may have been used in merging with other queries. Any ideas why a sheduled refresh would cause this when a manual refresh in the PBI service works fine?

[PijushRoy]

Hi @abelanger 
When you are marging, please check Datatype in both table for specific column in PQ
also check datatype in Power BI desktop for both column

If A is your dim table, Use RLS on table A which can filter your fact table data means Table B

[PijushRoy]

Hi @abelanger 
You can use relationship inplace of Join in Power Query when you have large dataset.

Thanks

[abelanger]

Somehow it seems to be related to scheduled refresh, everytime we manuall refresh the model, the tables are merged correctly.