Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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:
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
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.
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.
Then use my second option
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
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
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
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
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
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