Power Query Simplification code M (issu de l'assistant PQ) ou autre syntaxe pour ce résultat

[Staple1600]

Bonjour le fil

Situation de départ
Il y a N fichiers PDF(*) dans un dossier SharePoint
Depuis Sharepoint, j'ai fait Exporter vers Excel et j'obtiens un fichier owssvr.iqy
(voir explications ici sur ce qu'est ce fichier)
j'ouvre alors ce fichier *.iqy dans Excel et j'obtiens le contenu du dossier Sharepoint dans un tableau structuré

(*) le nom des fichiers PDF est normalisé: ils sont tous nommés sur le schéma suivant
NOM PRENOM_1234_Libellé_JJ.MM.AA_JJ.MM.AA_500E25.pdf

Spoiler: cliquez voir pour le code M

Ci-dessous code M actuel généré par l'assistant de PQ

let
    Source =Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdJNa8QgEAbgv1Jy3oT5cNTx7q3dlmJP20Uo3UJhD730/1cXJXUTkOQ9PbwzejpNx+enh5fXWH6Yj2/l8/j9cble338BLooZ/FIOmYy2B/HRyfLz+TUdJgIyM9BMklCCkQAwnQ//VaoqDSpl0IYxtkAk8d60CVxg3ppcTR5MzggLuFtB1wIpbUyXwAfc6WmqaQazUL0eml5YdGP6BBrIbk2ppgymZKQ+cl+CIRvJ3auakALuTG+ragfVZuTb0JzJtGCUI/Oq8gzlYL2nvfldVd2gunVs6rcvFqLA0LXWrereBnxV/aD68oJaRdQWLLl435QT+l1Tq6mDqevzJG7BqoneDE2LalJZadvq+Q8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NOM = _t, Modifié le = _t]),
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Modifié le", type datetime}}),
    #"Lignes filtrées" = Table.SelectRows(#"Type modifié", each Date.IsInCurrentYear([Modifié le])),
    #"Lignes filtrées1" = Table.SelectRows(#"Lignes filtrées", each Text.EndsWith([NOM], ".pdf")),
    #"Fractionner la colonne par délimiteur" = Table.SplitColumn(#"Lignes filtrées1", "NOM", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"NOM.1", "NOM.2", "NOM.3", "NOM.4", "NOM.5", "NOM.6"}),
    #"Colonnes renommées" = Table.RenameColumns(#"Fractionner la colonne par délimiteur",{{"NOM.1", "IDENTITE"}, {"NOM.2", "NUMERO"}, {"NOM.3", "LIBELLE"}, {"NOM.4", "Début"}, {"NOM.5", "Fin"}, {"NOM.6", "Montant"}}),
    #"Type modifié1" = Table.TransformColumnTypes(#"Colonnes renommées",{{"Début", type date}, {"Fin", type date}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié1", "NB JOURS", each (Duration.Days([Fin]-[Début])+1)),
    #"Valeur remplacée" = Table.ReplaceValue(#"Personnalisée ajoutée",".pdf","",Replacer.ReplaceText,{"Montant"}),
    #"Valeur remplacée1" = Table.ReplaceValue(#"Valeur remplacée","E",",",Replacer.ReplaceText,{"Montant"}),
    #"Type modifié2" = Table.TransformColumnTypes(#"Valeur remplacée1",{{"Montant", type number}}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Type modifié2",{"Modifié le"})
in
    #"Colonnes supprimées"

J'ai inclu un petit exemple avec la fonction EncodeSourceTable de @mromain

Pour ma gouverne et par curiosité, j'aimerai étudier vos propositions de code M pour arriver au même résultat (avec moins d'étapes ou avec un code M épuré)

Merci d'avance aux PQristes qui passeront dans ce fil.

 

[Staple1600]

Bonjour le fil

@alexga78
Ma crainte était fondée
Le test que je voulais faire dans le message#14 a échoué

Je cherche toujours une solution pour cette limitation des 100ières lignes
De ce que j'ai lu sur le web c'est un réglage par défaut de Sharepoint)
Donc avec la syntaxe Source = Web.Page(Web.Contents("URL_dans_Navigateur"))
(voir message#9)

@merinos
Si tu emploies le code M du message#9, tu n'as que 100 lignes ou plus de 100 lignes ?

Si quelqu'un a une idée pour contourner la chose.

PS: C'est simplement par curiosité.

 

[mromain]

Bonjour à tous,

@Staple1600

Si quelqu'un a une idée pour contourner la chose

Je vais parler à l’aveugle n’ayant pas de Sharepoint pour tester…
Est-ce que dans l’interface Web de Sharepoint l’affichage des éléments se fait sur plusieurs pages avec 100 éléments par pages ?
Si c’est bien le cas, peut-être que ça marche comme sur certains sites : chaque page a sa propre URL - avec la notion de page qui se retrouve en paramètre de l’URL (du style ?page=2).

Si c’est toujours le cas, il y a peut-être moyen de regarder de ce côté : faire une boucle qui dit "d’accumuler les données tant que la page suivante existe".

Je note également que c'est simplement par curiosité. Sinon, je préconiserais d’utiliser le connecteur dédié dans la mesure du possible.

A+

 

[Amilo]

Bonjour à tous,
Bonjour @Staple1600,
J'utilise au quotidien SharePoint au boulot.
Je n'ai jamais rencontré cette limitation de 100 lignes dans Power Query avec aucun des connecteurs : Web, dossier ou autres.
Je me connecte parfois aussi via l'explorateur Windows qu'on avait crée à l'utisation de Sharepoint.
Je ne suis pas informaticien mais j'appellerais cela, un clone de SharePoint en mode Explorateur.
Mais pas certain que cela soit la meilleure solution !!!
Sinon, à tout hasard, n'y aurait-il pas une limitation dans les options de Sharepoint ?
Cordialement

 

[Staple1600]

Bonsoir @mromain

Je ne dispose que d'Office 2019 au boulot.
Et j'ai donc pas le connecteur que j'ai mis en copie d'écran (message#8)

Ce que je ne comprends pas , c'est que la syntaxe
Source = Xml.Tables(Web.Contents("URL_dans_fichier_osswr.iqy"))
Fonctionne sans demande d'identification
alors que
Source = SharePoint.Contents me demande de m'identifier

Je ne peux pas changer le nombre de fichiers par page dans l'affichage web
(par défaut d'après ce que je lis , c'est 100 items par page)
C'est effectivement le cas à mon boulot.

Sur mon PC perso, j'ai Office 365 et je n'ai pas non plus accès à ceci

mais à ceci

 

[Staple1600]

Re

Bonsoir @Amilo
On doit avoir la même version de Sharepoint
Au boulot, ils utilisent Sharepoint2013
(en tout cas, là ou sont stockés les PDF que je dois lister dans Excel)
L'url n'est pas en https mais en http
Je ne sais pas si cela joue

Demain, je posterai des copies d'écran de l'interface "Sharepoint" que j'utilise.

 

[Cousinhub]

Bonsoir @mromain

Je ne dispose que d'Office 2019 au boulot.
Et j'ai donc pas le connecteur que j'ai mis en copie d'écran (message#8)

Ce que je ne comprends pas , c'est que la syntaxe
Source = Xml.Tables(Web.Contents("URL_dans_fichier_osswr.iqy"))
Fonctionne sans demande d'identification
alors que
Source = SharePoint.Contents me demande de m'identifier

Je ne peux pas changer le nombre de fichiers par page dans l'affichage web
(par défaut d'après ce que je lis , c'est 100 items par page)
C'est effectivement le cas à mon boulot.

Sur mon PC perso, j'ai Office 365 et je n'ai pas non plus accès à ceci
Regarde la pièce jointe 1195895
mais à ceci
Regarde la pièce jointe 1195896

Hello,
Bizarre, sous 2021, on a ceci :

Non testé, n'ayant point de SharePoint...

 

[Staple1600]

Bonsoir @Cousinhub

Tu as 2021 Professionnel ?

Moi, j'ai Office 365 par abonnement.
Voilà ce que j'ai dans A partir d'un fichier

 

[Cousinhub]

Re-,
Oui, 2021 Pro
Et pour "d'autres sources" :

 

[Staple1600]

Re

@Amilo
Concernant la limitation à 100 lignes, voila ce que j'ai trouvé qui expliquerait le pourquoi
Malheureusement, je n'ai pas la main pour les manips décrites dans la vidéo ci-dessous
https://www.youtube.com/watch?v=KLosq-mccoI

Ci-dessous la méthode expliquée par Microsoft pour obtenir le fichier osswr.iqy

Exporter vers Excel à partir de SharePoint ou de listes - Support Microsoft

Découvrez comment utiliser la commande Exporter vers Excel dans SharePoint

support.microsoft.com

Ce fameux fichier qui lui s'affranchit de la limite des 100 lignes dans l'import PQ.

Je suppose que la clé du mystère est dans chaine de caractère
http://sharepointsite/_vti_bin/owssvr.dll?XMLDATA=1&List={XXX-XXXX-XXXX-XXX}&View={XXX-XXXX-XXXX-XXX}&RowLimit=0&RootFolder=%2fLists%2fSharepoint%20Site

Mais en passant par l'URL classique (celle affichée dans le navigateur), quand j'affiche le dossier Sharepoint contenant les PDFS, et que j'utilise cette URL dans PQ, là je n'obtiens comme évoqué précédemment que les 100ières lignes

 

[Amilo]

@Staple1600 ,
En fouillant un peu sur le Web, je suis tombé sur ce fil :
- qui fait référence à cette vidéo
- et dans la partie inférieure de la vidéo se trouve un autre lien avec 2 codes ici

Par contre, l'URL dans ce fil est en "https:" et l'application utilisée est Power BI au lieu de Excel

Cordialement

 

[Staple1600]

Re

@Amilo
Lors de mes recherches, j'étais tombé sur cette video

Mais comme je le disais précédemment, la méthode 2 ( le biais donc) fonctionne bien
(l'url dans mon *.iqy est en http et cela ne pose pas de problème)
Il me ramène tout le contenu du dossier SharePoint

Par contre, l'adresse dans le navigateur (elle aussi en http) n'est pas du tout la même que celle présente dans le fichier *.iqy
Et quand je fais depuis PQ -> Données/A partir du web avec l'URL de la barre d'adresse de Edge
C'est là que je n'obtiens que 100 lignes

Ce que je ne comprends pas c'est pourquoi dans un cas , il n'y pas de demande d'authentification et dans l'autre si.

Je testerai demain au boulot depuis PowerBI Desktop

Mais le but c'est de le faire depuis Excel et en utilisant une des trois syntaxes ci-dessous
(avec Excel 2019)
Normalement la connexion Anonyme devrait fonctionner, non ?

source de l'image

 

[Amilo]

Re,
J'essayerai de connaitre un peu mieux l'environnement dans lequel nous sommes dès que je trouve un peu de temps : version de SharePoint notamment.
Dans un des fichiers d'un collègue dans lequel j'avais crée une connexion vers un simple fichier Excel, il est nécessaire de s'authentifier au moins la 1ère fois avec l'adresse mail et MDP Windows depuis PQ.
Parfois, sans connaître la raison, la connexion PQ s'interrompt et demande de s'authentifier à nouveau via le compte Pro.

Je regarderai demain si le fichier se trouve dans un emplacement SharePoint et quel est le début de l'URL !

Cordialement

 

[Staple1600]

Re

@Amilo
Tu peux aussi regarder, stp, si dans ta version, tu as ce bouton Exporter vers Excel

Donc quand j'ai cette affichage dans mon navigateur, je vois le contenu par page de 100 éléments
Là, on voit Page Active, moi, j'ai <1-100>

De plus, sur mon Sharepoint, seuls certains sont actifs, donc je ne peux utiliser Sharepoint à 100%
(alors que sur d'autres copies d'écran, tous les boutons sont actifs)

 

[merinos]

Bonjour @Staple1600 , bonjour a tous,

SharePoint étant stocké parfois/souvant(?) hors de l'entreprise , il est normal de devoir s'identifier.
C'est effectivement un probleme... J'ai souvant le probleme sur l'ordi de l'un ou de l'autre...
Je n'ai pas trouvé de solution.


Source = SharePoint.Files : j'emploie regulierement... mais je me retrouve avec 4 à 5.000 fichiers.... c'est LENT .



Source = SharePoint.Contents : je viens de découvrir , encore un peu tot pour en parler. Semble vraiment plus rapide. J'attends d'avoir a développer un nouveau rapport.
(ce n'est plus mon boulot , je suis gestionaire d'une application de gestion de l'energie... )

 

[mromain]

Bonjour à tous

@Staple1600

Ce que je ne comprends pas , c'est que la syntaxe
Source = Xml.Tables(Web.Contents("URL_dans_fichier_osswr.iqy"))
Fonctionne sans demande d'identification
alors que
Source = SharePoint.Contents me demande de m'identifier

C'est bizare...
Chez moi, chaque fois que j'utilise Web.Contents sur un nouveau site, PowerQuery me demande une "autorisation".
La liste des sites "autorisés" est visible dans les Options et paramètre > Paramètres de la source de données > Autorisations globales.

Je sais que "le moter Web de PowerQuery" est considéré comme Internet Explorer 11 tournant sur Windows 10 (alors que je suis sur Windows 11). On peut facilement faire le test en interrogeant WhatIsMyBrowser.com depuis PowerQuery :

Peut-être que tes identifiants sharepoint sont déjà stockés dans Internet Explorer 11 sur ton poste d'où le fait que PowerQuery ne te les redemande pas.
Peut-être.


Pour en revenir à ce que je te disais au post #17 sur la limite de 100 items avec la méthode Web.Contents, je ne peux pas tester sur un SharePoint, mais je vais essayer de faire le parallèle avec XLD :

• quand on va sur le forum Excel de XLD, on est sur la première page et l'URL est la suivante :
  
  https://excel-downloads.com/forums/forum-excel.7/​
  Cette page contient 100 fils (plus les deux épinglés au début).
• quand on va sur la deuxième page, l'URL est la suivante :
  
  https://excel-downloads.com/forums/forum-excel.7/page-2​
  la page contient 100 fils.
• quand on va sur la troisième page, l'URL est la suivante :
  
  https://excel-downloads.com/forums/forum-excel.7/page-3​
  la page contient 100 fils.
• ...

Chaque page a sa propre URL, et il est normal que Web.Contents ne voit que le contenu de l'URL demandée.

Ayant compris ce mécanisme de page, on peut néanmoins avec PowerQuery boucler sur plusieurs pages pour agréger les résultats.
Tu trouveras un exemple ci-dessous qui fait ça sur les 5 premières pages du forum XLD avec la fonction List.Generate.

let
    // fonction permettant d'extraire les fils d'une page du forum XLD
    fnExtractXldPage = (numPage as number) as table =>
        let
            urlXldBase = "https://excel-downloads.com/forums/forum-excel.7/page-",
            ListeThreads = Table.FromList(List.Select(List.Transform(Lines.FromBinary(Web.Contents(urlXldBase & Text.From(numPage))), Text.Trim), each Text.StartsWith(_, "<a href=""/threads/") and not Text.Contains(_, "/latest""") and Text.Contains(_, "class="""" data-tp-primary=""on""")), Splitter.SplitByNothing(), {"CodeSourceLien"}, null, ExtraValues.Error),
            AjoutColLienThread = Table.AddColumn(ListeThreads, "LienThread", each "https://excel-downloads.com" & Text.BetweenDelimiters([CodeSourceLien], """", """"), type text),
            AjoutColTitreThread = Table.AddColumn(AjoutColLienThread, "TitreThread", each Text.BetweenDelimiters([CodeSourceLien], ">", "<"), type text),
            AjoutColNumPage = Table.AddColumn(AjoutColTitreThread, "NumPage", each numPage, Int64.Type),
            SélectionColonnes = Table.SelectColumns(AjoutColNumPage,{"NumPage", "TitreThread", "LienThread"})
        in
            SélectionColonnes,
    testFnExtractXldPage = fnExtractXldPage(1),

    // extraction des pages du forum (limité à 5 pages (pour le bien du site))
    initialPage = 1,
    nbPageMax = 5,
    ExtractData = List.Generate(
        () => [NumPage = initialPage, DataPage = fnExtractXldPage(initialPage)],
        each [NumPage] <= nbPageMax + initialPage - 1 and [DataPage] <> null,
        each [NumPage = [NumPage]+1, DataPage = let data = fnExtractXldPage([NumPage]+1) in if data{0}[LienThread] = [DataPage]{0}[LienThread] then null else data],
        each [DataPage]
    ),
    
    // conversion en table
    ToTable = Table.Combine(ExtractData)
in
    ToTable

A+