What is the purose for Switching Data Source?
Excel is a widely used tool for data management and analysis in many organizations due to its simplicity, accessibility, and minimal setup requirements. However, as data size and complexity increase, Excel may no longer be sufficient. Managing large datasets, ensuring data consistency, and maintaining real-time updates can become cumbersome and inefficient with Excel alone.
This is where a robust solution like SQL Server comes into play. SQL Server provides powerful capabilities to handle extensive datasets, execute advanced queries, and offer secure, centralized data access. For organizations aiming to scale and improve operational efficiency, moving from Excel to SQL Server can be a game-changer.
In this blog, we’ll guide you through the process of migrating your data source from Excel to SQL Server, detailing each step to ensure a seamless transition.
How to Change Data Source for an Existing Report?
With the help of some simple M language script, we will modify the data source. Because Power BI data integration source settings do not provide us the opportunity to alter the source directly, as seen below, we must do this using M language.
There is no direct option to switch to a SQL database in the Power BI data source settings.
We shall now do the following actions to modify the through M script.
1. Select “Transform Data” from the Power BI ribbon.
Query editing in Power BI
2. Click on New Source and choose SQL Server to get the M script for the SQL Server database.
Selecting a new source from the SQL server
3. State the database server’s name. The database name may be left out.
Providing the database’s server name
4. Supply the login information you use to access the database.
Accessing the database
5. You will see a box suggesting all the databases and tables that are included in the database after successfully login in. We will choose a table from the database and click OK as shown below.
Choosing a table from the SQL database
6. The chosen table will now be imported, therefore the next step is to go to the Advanced Editor on the Query Editor’s ribbon, making sure to pick the table we just imported from the database as seen below.
Power Query Editor’s Advanced Editor
7. Copy the M script of the chosen area as it includes the data source in M and we will see the M script for the relevant table.
Copy the M code Copy the M code
8. Go to the excel source table that is shown under Queries after clicking Done.
Choosing Excel Table
9. Go to that Excel table’s Advanced Editor; you’ll see something similar there.
The data source script in M for Excel Table is included in the highlighted code.
10. As seen below, swap out the highlighted code with the copied code that ends with a comma.
The update that is performed replaces the code that is italicized. Select “Done”
Below is the result of this.
Now that we have closed and applied, we can inspect the data source.
Now, as seen below, you will find that our data source has been changed from Excel to SQL if you look at the data source settings.
As you can see, business intelligence has a lot to offer businesses of all sizes across all industries. If you’re not already using BI in your decision-making process, now is the time to start!
For more customizable options, feel free to reach out to our Power BI Consulting Firm and explore tailored solutions to meet your specific needs.
Check out other helpful Power BI Blogs
- What are the different types of Data Analysis?
- Choosing the Right BI tool – Follow these major guidelines
- Power BI Advantages over Tableau
- How to Format Power BI Visualization
- Job Roles and Responsibilities in Power BI
Follow us on Twitter, Facebook, Linkedin to stay updated with our latest blog and what’s new in Power BI.
If you are looking forward to getting your data pipeline built and setting up the dashboard for business intelligence, book a call now from here.
#analytics #data #business #artificialintelligence #machinelearning #startup #deeplearning #deeplearning #datascience #ai #growth #dataanalytics #india #datascientist #powerbi #dataanalysis #businessanalytics #businessanalyst #businessandmanagement #dataanalyst #businessanalysis #analyst #analysis #powerbideveloper #powerbidesktop #letsviz
