Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
Solved! Go to Solution.
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:
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.
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:
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!! 🙂
PQ gets only the values from Excel files. Whether formula is present or not, it is immaterial to it.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
14 | |
13 | |
13 | |
12 | |
12 |