Excel Advanced - For Power Users & Sage 50 | Excel forum

Hi Daphne,

Thank you for the forum question.

I am sorry it is not a part of our service to have online meetings and share screens, but I hope that you can get what you want to achieve from my answer here.

I have two solutions. The first one you can use, if it is not possible to make a live connection to SAGE. The second solution is by using a live connection to SAGE.


Solution 1

Pulling Data from Sage to Excel Using Power Query
Export Data from Sage:

Generate the report in Sage and export it as a CSV or Excel file.
Import Data into Excel with Power Query:

Open Excel and go to the "Data" tab.
Click on "Get Data" and choose the appropriate source (e.g., "From File" > "From CSV" or "From Workbook").
Navigate to your exported file and select it.
Power Query Editor will open, allowing you to preview and transform the data.
Transform Data in Power Query:

Use the Power Query Editor to clean and format your data. You can:
Remove unnecessary columns.
Filter rows.
Change data types (e.g., text to date).
Split columns by delimiter if needed.
Once you're satisfied with the transformations, click "Close & Load" to load the data into Excel.
Setting a Standard Outlook for Your Summary
Create a Summary Template:

Design your summary table layout in Excel.
Include headers, footers, and any standard formatting you need.
Save as a Template:

Save the file as an Excel template (.xltx) for future use.
Using Power Query with Your Template:

Open your template file.
Use Power Query to import and transform new data as needed.
Load the transformed data into the predefined sections of your summary table.
Example Workflow
Open Excel and Your Template:

Open your summary template file.
Import Data with Power Query:

Go to the "Data" tab, click "Get Data," and select your data source.
Transform the data in Power Query Editor and load it into your summary table.
Update Summary:

The data will be loaded into your summary table, maintaining the standard layout and formatting.

Solution 2

Step-by-Step Guide to Connect Sage Live to Power Query
Set Up OData Feed in Sage:

Log in to your Sage account.
Navigate to the settings or administration section where you can manage integrations.
Look for an option to enable or configure OData feeds. You might need to create a new OData endpoint if one doesn't already exist.
Note down the OData URL and any necessary authentication details (username, password, etc.).
Connect to OData Feed in Power Query:

Open Excel and go to the "Data" tab.
Click on "Get Data" > "From Other Sources" > "From OData Feed".
Enter the OData URL you obtained from Sage.
If prompted, enter your Sage credentials to authenticate the connection.
Load and Transform Data:

Once connected, Power Query will display a list of available tables and data from Sage.
Select the tables or data you need and click "Load" to import them into Excel.
Use the Power Query Editor to transform and clean the data as needed. You can remove unnecessary columns, filter rows, change data types, etc.
When you're done, click "Close & Load" to load the transformed data into Excel.
Example Workflow
Open Excel:

Go to the "Data" tab and select "Get Data" > "From Other Sources" > "From OData Feed".
Enter OData URL:

Input the OData URL from Sage and authenticate with your credentials.
Select and Transform Data:

Choose the data you need and use the Power Query Editor to make any necessary transformations.
Load Data into Excel:

Load the cleaned data into your Excel workbook.
This setup allows you to pull live data from Sage into Excel using Power Query, making it easier to keep your reports up-to-date.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector