Formula not evaluated with import Data from Web Office 2019 and newer

[Christophe79]

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

[ronrsnfld]

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:

Data import and analysis options 

[Christophe79]

Hi,

Thanks for answering, but i want to use Table option, with old wizard, it create a range not a table.

So having a table give me access to all options a table have (look, autosize, etc ...)

Regards

[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.

[Christophe79]

Unfortunatly, making some formula in PowerQuery goes against my goal;

Delivering a web table with everythings included (even formulas) allow me to update workbooks contents without even updating the file itself. 

I really don't get why Excel does not try to evaluate the cells like legacy one do.

I feel that a column type "Excel Formula" is missing int the columns types list into Power Query.

[ronrsnfld]

Then use my second option

[Christophe79]

Hi

Actually, i firstly missread you message, i'll try this thing to re write formulas using table references and see.

I had this idea, but was too lazy t try and eventually forgot it by getting so much annoyed 😛

Thx

Regards

[KNP]

I can imagine this is very frustrating.

Unfortunately I don't have a good solution for you if you don't want to convert this to a Power Query only solution.

You could see if the answers in this post are of any use to you...

https://stackoverflow.com/questions/31293828/excel-doesnt-update-value-unless-i-hit-enter/31294048

[Christophe79]

Hi,

Well there are a lot of hints in this link that i wan try... i'll try all of them and see what happen.

I'll keep ou posted.

Regards

[v-eqin-msft]

Hi @Christophe79 ,

It's not possible for Power BI to calculate the formula directly when importing data. I'd suggest you do the calculations firstly in Excel, and then in Power BI Desktop to Get data from Excel.

Best Regards,
Eyelyn Qin

[Christophe79]

Hi,

I believe the formula is not evaluated in Power Query but in Excel. 

I feel it is just a matter of telling Excel to process this column as Formula and not as Text.

I don't want to believe this isn't possible. 😞

regards