Direct Query Data Transformation

[Joanna219]

Hi,
I am trying to create dashboards based on my Access Database after the transformation in Power Query. Currently, I am able to directly load my Access Database to Power BI. However, after I made the transformation to remove the duplicate rows in Power Query Editor, I was not able to "Apply" the changes to the Power BI, and the following error message popped out:
"We couldn't connect to your DirectQuery data source or internal model. Double-check that your server and database names are correct, and that you have permission to access them.
No OLE DB Error Information found."

I would really appreciate for any suggestions or comments.

Thanks.

[dax]

Hi @Joanna219 ,

According to your descripion, it seems to be connection problem, I tgink you need to check the datasource setting to see whether your credential  and connection string are correct. And you also need to check whether you have permission  to access the database.

Best Regards,
Zoe Zhi

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

[Joanna219]

Hi @dax 

Thank you so much for your response. I do have the permission to the Access Database because I am able to load my database to Power BI. However, if I transform my data instead of directly load my data, even though I could see the transformed data in the Power Query Editor, I could not load the transformed data into dashboards, the Power BI will freeze and pop out the error message that I illustrated in my previous discussion.

Do you know any other solutions which could remove the duplicates in Power BI instead of the Power Query Editor?

Thank you so much for your help!

Sincerely,

Na

[dax]

Hi @Joanna219 ,

You could create a calculated column, and use rankx function to sort column, then you could try to add this calculated column into filter and set filter to find value which is 1, then you will onlt see the distinct result.

Best Regards,
Zoe Zhi

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