cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
daniels13ca Frequent Visitor
Frequent Visitor

change datasoruce

Hello, I have an origin in Access but the data size is very big now, i'm trying to change de datasource to SQL Server and don´t find this option in Power BI Desktop. Is it possible do the migration without remake the dashboard?

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
v-jiascu-msft Super Contributor
Super Contributor

Re: change datasoruce

Hi @daniels13ca,

 

Seems no direct way available. But we can change the Power Query (M) code directly. One precondition is that the structure should be the same. If they have the same data type, that would be great. 

1. Usually, the first two rows codes are the data source.

2. If you have many queries, you have change them one by one. 

3. Please pay attention to the two parts tagged by blue 1 and 2.

4. Backup the code first, if you do anything wrong, you can paste the code back and do it again.

 

If you have any problem doing it, please post the code here. Please mask the private parts first.

change_datasoruce

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 REPLIES 8
Mathew101 Frequent Visitor
Frequent Visitor

Re: change datasoruce

You would need to work on the Power Query section to ensure that the end tables match what you've had before.  Then in thorey it should work.

 

I have heard a tail that sometimes even when everthing is 100% the same the visiuals need to be rebuilt, I take this with a pinch of salt however.

 

in sure that you have the data tables correct with the correct data types and you should be good to go. 

 

Regards,

Mathew

rocky09 Senior Member
Senior Member

Re: change datasoruce

the most challenging part is keeping the same Data format while migration.

1. I would take screenshots of all the data format structures of all tables including the relationships.

2. I will copy the pbix file then start testing it.

3. I will also make sure that all the column names in pbix are noted in some notepad/word.

 

 

daniels13ca Frequent Visitor
Frequent Visitor

Re: change datasoruce

My question is that in the options to change de datasource SQL Server is not availableCapture.jpg

 

 

 

rocky09 Senior Member
Senior Member

Re: change datasoruce

@daniels13ca

have you tried to edit through advanced editor in query editor window?

 

found this one

daniels13ca Frequent Visitor
Frequent Visitor

Re: change datasoruce

Yes, and the options are the same Man Sad

v-jiascu-msft Super Contributor
Super Contributor

Re: change datasoruce

Hi @daniels13ca,

 

Seems no direct way available. But we can change the Power Query (M) code directly. One precondition is that the structure should be the same. If they have the same data type, that would be great. 

1. Usually, the first two rows codes are the data source.

2. If you have many queries, you have change them one by one. 

3. Please pay attention to the two parts tagged by blue 1 and 2.

4. Backup the code first, if you do anything wrong, you can paste the code back and do it again.

 

If you have any problem doing it, please post the code here. Please mask the private parts first.

change_datasoruce

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Mathew101 Frequent Visitor
Frequent Visitor

Re: change datasoruce

It is highly unlikely that the data in SQL Database will be in the exact same format as it was when you where pulling data from the Access Database.  As a result there is a very high likelyhood of changes disrupting the report.

 

I woudl strongly suggest you go throught the query editor with a fine tooth comb to ensure, it matches 100%

 

Regards,

Mathew

daniels13ca Frequent Visitor
Frequent Visitor

Re: change datasoruce

This is the new code:

 

Captura.JPG

ThanksSmiley Very Happy

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 98 members 1,397 guests
Please welcome our newest community members: