Forum Discussion
Formula not evaluated with import Data from Web Office 2019 and newer
Hello Everyone,
I'm encountering an issue using Import data from web since Office >= 2019
I have a web server that deliver some data as an HTML table as is:
| ID_OF | QTE | STATUT_PH | PHASE | ID_ARTI | REF_ARTI | LIB_ARTI | CDC_LIBELLE | FAMILLE | PROCEDURE_PREM_A | DATE_DEBUT_td | DUREE_RESTANTE | MARGE | JOURNEE_PASSAGE | PRIORITE | DATE_FIN_OF | CYNAPS_TEXTE_TACHE | COMMENTAIRES | AGE_AU_POSTE | MATIERES |
| 614414 | 1 | EC | 20.00 | 58119 | D539 25408 000 01 | Voile Equipé | =SIERREUR(RECHERCHEV("RIVETAGE SA";GROUPES;2;FAUX);"RIVETAGE SA") | EDSSA | 0 | 2022/02/24 | 0 | 21 | J | 99 | 2022/03/04 | 01/02/22 08:1317/01/22 08:55 MANQUE 1 SUPPORT. VU AVEC JOSH. | 56,87 | ||
| 601314 | 3 | D | 40.00 | 83565 | U311A1101104 | FRAME | =SIERREUR(RECHERCHEV("RETOUCHE PROTECTION SA";GROUPES;2;FAUX);"RETOUCHE PROTECTION SA") | SMASA | 0 | 2021/10/04 | ,56 | -122 | J | 99 | 2021/10/06 | 25/01/22 09:48 sido partiel non dispo au poste | 20,96 | ||
| 610335 | 50 | EC | 20.00 | 85051 | FILM VENTOUSE PLATE ASSY GENERIQUE | FILM VENTOUSE PLATE ASSY | =SIERREUR(RECHERCHEV("Ligne support batterie SA";GROUPES;2;FAUX);"Ligne support batterie SA") | FDNSA | 0 | 2021/09/08 | ,17 | -148 | J | 99 | 2021/09/14 | Voir avec Joshua. 24/01/22 | 13,73 | 4744 - FILM DE PROTECTION - - Plat - Largeur : 1270 (MM) Epaisseur : 0.12 (MM) - FILM PIEUVRE ULTRA TRANSPARENT 1,27MX30M - APPRO PROPRE | |
| 610457 | 80 | D | 20.00 | 69530 | V251.11676.000.00 | BOLT ASSY | =SIERREUR(RECHERCHEV("COLLAGE STRUCTURAL SA";GROUPES;2;FAUX);"COLLAGE STRUCTURAL SA") | EDSSA | 0 | 2022/02/28 | 2,2 | 25 | S+4 | 50 | 2022/03/04 | 22,89 | |||
| 609281 | 1 | D | 20.00 | 24178 | S570.12154.000.00 | SUPPORT CARENAGE | =SIERREUR(RECHERCHEV("Ligne Potences SA";GROUPES;2;FAUX);"Ligne Potences SA") | SMASA | 0 | 2022/02/28 | ,13 | 25 | S+4 | 50 | 2022/03/08 | 29,89 | |||
| 604693 | 9 | D | 20.00 | 73527 | FJSA1-1C1100-01 | ENS CHARIOT MOBILE | =SIERREUR(RECHERCHEV("Aménagement Cabine";GROUPES;2;FAUX);"Aménagement Cabine") | JGDSA | 0 | 2022/03/03 | 3,8 | 28 | S+4 | 50 | 2022/03/24 | ,91 |
With an Office 2016 or older:
When i use menu : Data >Get External Data > From web, there is a windows where you can enter an URL and it previsualize the data.
At the end the data are imported into Excel into a range. The columns that contains formulas are being evaluated and display the result of the formula without anymore actions. (Same when updating the source)
With an Office 2019 or newer :
When i use menu : Data > From Web , there is process where you enter the URL and you end up into Power Query.
At the end a new Sheet is created with a Table (and all the tools associated with a table, what is the purpose of using this method) and this table has an updatable datasource.
The issue is the columns containing an Excel Formula are displayed as fulltext un Excel, they are not being evaluated.
If i select a cell and press F2 and Enter, then the formula is being evaluated.
So how do i make Office 2019 and newer, to evaluate this cells when importing data from Refreshing the source.
Best regards
Christophe
10 Replies
ronrsnfldSuper User
From your description, I am guessing you were using the original import wizard in the older versions of Excel.
You can enable it in current versions under File/Options/Data. For additional information, see:
- ronrsnfld
I haven't played with the legacy wizard to advise you with regard to that. But you can always add the formula columns as custom columns within Power Query. Or you could re-write your formulas to use table references to the table that PQ generates. I believe the first option will be more stable.