Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mandar5118
Frequent Visitor

Get data from excel

Hi,

 

I have a excel file from where I need to get data. The problem is the values in the excel file are formulas, and I dont want to do a paste special everytime while importing to truly make this automated. Is there a way around this?

 

Thanks

Mandar

1 ACCEPTED SOLUTION
Adamboer
Responsive Resident
Responsive Resident

Yes, there is a way to get the calculated values instead of the formulas while importing data from an Excel file in Power Query.

When importing data from Excel, Power Query has an option to load the data as a connection only, which means it will not load the data into the worksheet, but only create a connection to the data source.

To load the calculated values instead of the formulas, follow these steps:

  1. In the Power Query Editor, click on the "Data" tab.
  2. Click on the "From File" button and select "From Workbook" in the drop-down menu.
  3. Browse to the location of the Excel file and select it, then click "Import".
  4. In the "Navigator" window, select the worksheet with the data you want to import.
  5. Click on the "Transform Data" button.
  6. Select the columns with the formulas.
  7. Click on the "Replace Values" button under the "Transform" tab.
  8. In the "Replace Values" dialog box, enter "=" in the "Value To Find" field and leave the "Replace With" field blank.
  9. Click "OK" to replace the formulas with their calculated values.
  10. Click on the "Close & Load" button to load the data into your worksheet.

By using the "Replace Values" feature to replace "=" with blank, Power Query will treat the values in the selected columns as static, calculated values instead of formulas. This will allow you to import the data with the calculated values, without having to do a paste special every time.

View solution in original post

4 REPLIES 4
Adamboer
Responsive Resident
Responsive Resident

Yes, there is a way to get the calculated values instead of the formulas while importing data from an Excel file in Power Query.

When importing data from Excel, Power Query has an option to load the data as a connection only, which means it will not load the data into the worksheet, but only create a connection to the data source.

To load the calculated values instead of the formulas, follow these steps:

  1. In the Power Query Editor, click on the "Data" tab.
  2. Click on the "From File" button and select "From Workbook" in the drop-down menu.
  3. Browse to the location of the Excel file and select it, then click "Import".
  4. In the "Navigator" window, select the worksheet with the data you want to import.
  5. Click on the "Transform Data" button.
  6. Select the columns with the formulas.
  7. Click on the "Replace Values" button under the "Transform" tab.
  8. In the "Replace Values" dialog box, enter "=" in the "Value To Find" field and leave the "Replace With" field blank.
  9. Click "OK" to replace the formulas with their calculated values.
  10. Click on the "Close & Load" button to load the data into your worksheet.

By using the "Replace Values" feature to replace "=" with blank, Power Query will treat the values in the selected columns as static, calculated values instead of formulas. This will allow you to import the data with the calculated values, without having to do a paste special every time.

Awesome! thanks so much!! 🙂

Vijay_A_Verma
Super User
Super User

PQ gets only the values from Excel files. Whether formula is present or not, it is immaterial to it. 

serpiva64
Super User
Super User

Hi, if you want to get the result of your formula you don't need to do paste special because during get data you loose the formula and achieve the result.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors