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]

Re

En attendant de vous lire, voici mon premier essai de simplification
(qui n'en n'est pas vraiment une)

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]),
    Filtrer = Table.SelectRows(Table.SelectRows(Table.TransformColumnTypes(Source,{{"Modifié le", type datetime}}), each Date.IsInCurrentYear([Modifié le])), each Text.EndsWith([NOM], ".pdf")),
    Fractionner = Table.SplitColumn(Filtrer, "NOM", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"IDENTITE", "NUMERO", "LIBELLE", "Début", "Fin", "Montant"}),
    AjoutCol = Table.AddColumn(Table.TransformColumnTypes(Fractionner,{{"Début", type date}, {"Fin", type date}}), "NB JOURS", each (Duration.Days([Fin]-[Début])+1)),
    Final = Table.RemoveColumns(Table.TransformColumnTypes( Table.ReplaceValue(Table.ReplaceValue(AjoutCol,".pdf","",Replacer.ReplaceText,{"Montant"}),"E",",",Replacer.ReplaceText,{"Montant"}),{{"Montant", type number}}),{"Modifié le"})
in
   Final

Quand je m'arrête sur l'étape AjoutCol, PQ affiche une colonne vide

Je ne comprends pas pourquoi
Par contre quand je passe sur Final, là les durées apparaissent bien.

 

[merinos]

Je fais cela souvant ...

je synchronise les fichiers en local sur mon PC... puis je peux travailler comme d'hbitude .

La seule chose qui est spéciale c'est que le chemin d'accès au fichier évolue d'une personne a l'autre. donc je dois faire un systeme de parametres qui donne accès aux differents fichiers source.

je chercherai sur mon PC pro demain si je peux passer un exmple...

 

[Staple1600]

Bonsoir @merinos

Merci de t'être arrêté dans le fil

Je suis obligé de travailler avec Sharepoint.
Nous sommes obligés d'enregistrer nos documents sur Sharepoint
(chaque service à son arborescence propre et les droits d'accès qui vont avec)
On doit avoir accès aux documents quelque soit le PC sur lequel on ouvre sa session

Le document Excel sera utilisé par d'autres que moi.

En l'état le code M fonctionne.

Simplement comme je l'ai dit, j'aimerai voir quelle syntaxe M les membres du forum utiliseraient pour faire cette opération.

Lister les fichiers PDF d'un répertoire (ces fichiers PDF avant un schéma de nom particulier)

Au final, une fois le tableau structuré obtenu par PQ, chaque utilisateur sera a même de faire les TCD de son choix

PS: je n'ai Sharepoint qu'au boulot et avec Sharepoint, le tableau obtenu par le fichier *.iqy
est un peu différent (voir lien dans mon premier message)

 

[alexga78]

Bonjour le fil,

Pas vraiment plus court...

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]),
    Columns = {{"IDENTITE", "NUMERO", "LIBELLE", "Début", "Fin", "Montant", "NB JOURS"} , {type text, type text, type text, type date, type date, type number, type number}, Table.ColumnNames(Source)},
    Filter = Table.SelectRows(Source, each (Date.IsInCurrentYear(DateTime.From(Record.Field(_, Columns{2}{1}), "fr-FR")) and Text.EndsWith(Record.Field(_, Columns{2}{0}), ".pdf"))),
    Custom = List.Accumulate(Table.Column(Source, Columns{2}{0}), {}, (s,c)=> s & {let x = Text.Split(c,"_")
    in List.RemoveLastN(x,1) & {Text.Replace(Text.Replace(x{5},".pdf",""), "E","")} & {Duration.Days(Date.From(x{4}, "fr-FR") - Date.From(x{3}, "fr-FR"))+1}}),
    Result = Table.TransformColumnTypes(Table.Combine(List.Transform(Custom, each Table.FromRows({_}, Columns{0}))), List.Zip({Columns{0}, Columns{1}}), "fr_FR")
in
    Result

Bonne journée

 

[merinos]

J'ai une methode directe... mais je préfère travailler a partir d'une replique locale.


1: je veux avoir la liste des documents (TOUS LES DOCUMENTS ) accessibles en sharepoint.

https://xxxxx.sharepoint.com/sites/wpNOC133/Documents%20partages/General/Client%20prep%20Bernard/sites/Assemblage%20sites.xlsx?web=1

je garde

https://xxxxxx.sharepoint.com/sites/wpBNOC133

car on ne peut avoit de "%20" dans l'adressse (c'est un espace)

Puis je fais un query vers un directory sharepoint. j'obtient 4500 documents...je filtre sur le bon directory, les bons documents,...

2: J'applique une fonction sur les fichiers restants

C'est pas rapide car il trouve tous les fichiers aux quels j'ai accès ... mais cela fonctionne.

A+

Merinos

let
    Source = SharePoint.Files("https://XXXXXX.sharepoint.com/sites/wp-BNOC133/", [ApiVersion = 15]),
// 4.500 files

    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "Bernard")),
// my data

    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Extension] = ".csv"),
    #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each Text.Contains([Name], "SENT_BE_LB_TT_")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows2",{"Content"}),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Removed Other Columns", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}),
    #"Removed Errors1" = Table.RemoveRowsWithErrors(#"Removed Other Columns1", {"Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Errors1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Date/Time", type text}, {"Name_Path_Reference_Adjusted", type text}, {"Object_Value", Int64.Type}})
in
    #"Changed Type"

 

[merinos]

Salut @Staple1600 ,


J'ai trouvé une methode BEAUCOUP plus rapide...


voir cette vidéo.

Cela donne ceci:

let
//ligne a modifier
//Source = SharePoint.Files("https://sibelga.sharepoint.com/sites/wp-BNOC133/", [ApiVersion = 15])

//on change:  ".Files" en ".Contents"
    Source = SharePoint.Contents("https://XXXXX.sharepoint.com/sites/wp-BNOC133", [ApiVersion = 15]),

//On navigue parmis les directory
    #"Documents partages" = Source{[Name="Documents partages"]}[Content],
    General = #"Documents partages"{[Name="General"]}[Content],
    #"Client prep Bernard" = General{[Name="Client prep Bernard"]}[Content],
    sites = #"Client prep Bernard"{[Name="sites"]}[Content],
// et on n'a plus que le directory que l'on doit avoir


    #"Removed Other Columns" = Table.SelectColumns(sites,{"Content"}),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Removed Other Columns", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}),
    #"Removed Errors1" = Table.RemoveRowsWithErrors(#"Removed Other Columns1", {"Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Errors1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"PARENT", type text}, {"ENTITY_TYPE", type text}, {"SITE", type text}, {"Nom Site", type text}, {"ADRESSE", type text}, {"position Geo", type any}, {"USAGE", type text}, {"Site Type", type text}, {"GROUND_SURF", type any}, {"TOT_FLOORS_SURF", Int64.Type}, {"TOT_FLOORS_HEATED_SURF", Int64.Type}, {"PEB_SURF", type any}, {"SOL_PV", type logical}, {"SOLAR Therm", type logical}, {"COGEN ?", type logical}, {"REF_YEAR", type any}, {"lien Entité", type text}})
in
    #"Changed Type"

 

[Staple1600]

Bonjour le fil

@alexga78
Juste un petit souci
La dernière partie du nom des fichiers PDF, correspond à un montant
NOM PRENOM_1234_Libellé_JJ.MM.AA_JJ.MM.AA_500E25.pdf
D'où mon étape pour remplacer le E par une virgule puis de typer la colonne en Nombre décimal
Merci pour ta syntaxe M (qu'il va falloir que je creuse)

@merinos
Version utilisée au boulot : Sharepoint 2013 et Office 2019
J'ai essayé ton dernier code en adaptant l'URL.
j'ai un problème d'authentification
(que je choisisse Anonyme, Windows ou Compte professionnel), c'est pareil
Sur ma version d'Office, je n'ai pas de choix concernant Sharepoint comme ici

Soit l'URL que j'ai saisi n'est pas bonne.
Soit à mon boulot, il y a des restrictions appliquées pour les utilisateurs lambda de Sharepoint

 

[Staple1600]

Re

J'ai aussi voulu tester en passant par Source = Web.Page
(voir méthode 1)
Le problème, c'est que cela ne renvoie que les 100 premières lignes
(ce qui semble lié au fait que sur la page web Sharepoint, l'affichage se fait de 100 en 100 éléments)

Spoiler: méthode 1

URL_dans_Navigateur= l'adresse qui s'affiche dans la barre d'adresse quand je suis le dossier Sharepoint contenant les PDF

let
    Source = Web.Page(Web.Contents("URL_dans_Navigateur")),
    Data0 = Source{0}[Data],
    #"Type modifié" = Table.TransformColumnTypes(Data0,{{"Type", type text}, {"Nom", type text}, {"Modifié", type datetime}, {"Modifié par", type text}, {"Typologie", type text}, {"Mot-clé", type text}, {"Commentaire d’archivage", type text}, {"Taille du fichier", type text}}),
    #"Lignes filtrées" = Table.SelectRows(#"Type modifié", each Date.IsInCurrentYear([Modifié])),
    #"Autres colonnes supprimées" = Table.SelectColumns(#"Lignes filtrées",{"Nom"})
in
    #"Autres colonnes supprimées"

En poursuivant mes tests, j'ai trouvé un biais à ce problème de limite des 100 lignes grâce au fichier owssvr.iqy
(voir méthode 2)

Spoiler: méthode 2

URL_dans_fichier_osswr.iqy= URL qui se trouve dans ce fichier
Voir exemple ci-dessous

J'ai donc copié l'URL complète dans PQ
et là c'est OK, je n'ai plus la limite des 100 lignes

let
    Source = Xml.Tables(Web.Contents("URL_dans_fichier_osswr.iqy")),
    Table1 = Source{1}[Table],
    Table0 = Table1{0}[Table],
    Table2 = Table0{0}[Table],
    Table3 = Table2{0}[Table],
    #"Type modifié" = Table.TransformColumnTypes(Table3,{{"Attribute:ows_LinkFilename", type text}, {"Attribute:ows_Modified", type datetime}, {"Attribute:ows_Editor", type text}, {"Attribute:ows__CheckinComment", type text}, {"Attribute:ows_DocIcon", type text}, {"Attribute:ows_Typologie", type text}, {"Attribute:ows_FileSizeDisplay", Int64.Type}}),
    #"Autres colonnes supprimées" = Table.SelectColumns(#"Type modifié",{"Attribute:ows_LinkFilename", "Attribute:ows_Modified"})
in
    #"Autres colonnes supprimées"

version allégée

let
Source = Xml.Tables(Web.Contents("URL_dans_fichier_osswr.iqy")),
Debut = Table.ExpandTableColumn(Table.ExpandTableColumn(Source{1}[Table][Table]{0}, "Table", {"Table"}, {"Table.Table"}), "Table.Table", {"Attribute:ows_LinkFilename", "Attribute:ows_Modified"}, {"Attribute:ows_LinkFilename", "Attribute:ows_Modified"}),
Datas = Table.RemoveColumns(Table.SelectRows(Table.TransformColumnTypes(Debut,{{"Attribute:ows_Modified", type datetime}}), each Date.IsInCurrentYear([#"Attribute:ows_Modified"])),{"Name"})
in
    Datas

 

[Staple1600]

Re

@merinos (et tous ceux qui passeront ici, amateur de PQ et utilisateur de Sharepoint)
J'ai continué à chercher des infos sur PQ et Sharepoint
J'ai trouvé cette syntaxe M

let
    Toys = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15]){[Title="Toys"]}[Items],
    ToysData = Table.SelectColumns(Toys,{"Id", "Title", "Price", "Modified"})
 in
    ToysData

source du code M

Est-ce qu'ici Toys correspond à un dossier Sharepoint ?
Et dans ce cas, il suffirait d'indiquer le nom des colonnes désirées ?

 

[Staple1600]

Bonjour le fil

En attendant de comprendre pourquoi la syntaxe M avec SharePoint me demande de m'identifier ou de me connecter alors que les autres méthodes fonctionnent sans cette demande d'authentification, j'ai étudier le code M proposé par @alexga78 ligne à ligne et j'ai ajouté commentaires et question sur celui-ci

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]),
    // Columns = Array contenant 3 "sous-array" ? qui renvoie Liste dans PQ
    Columns = {{"IDENTITE", "NUMERO", "LIBELLE", "Début", "Fin", "Montant", "NB JOURS"} , {type text, type text, type text, type date, type date, type number, type number}, Table.ColumnNames(Source)},
    // Filtre sur date modification dans l'annéee en cours et uniquement fichier PDF
    Filter = Table.SelectRows(Source, each (Date.IsInCurrentYear(DateTime.From(Record.Field(_, Columns{2}{1}), "fr-FR")) and Text.EndsWith(Record.Field(_, Columns{2}{0}), ".pdf"))),
    // Séparer le nom des PDF avec le séparateur _
    Custom = List.Accumulate(Table.Column(Source, Columns{2}{0}), {}, (s,c)=> s & {let x = Text.Split(c,"_")
    // Remplacer .pdf par rien et E par une virgule
// Calculer le nombre de jours entre Début et Fin
    in List.RemoveLastN(x,1) & {Text.Replace(Text.Replace(x{5},".pdf",""), "E",",")} & {Duration.Days(Date.From(x{4}, "fr-FR") - Date.From(x{3}, "fr-FR"))+1}}),
    // Là, faut que je creuse ce que fait List.Zip, car retourne une liste de listes en combinant des élements à la même position dans plusieurs listes
    // ca me parle pas vraiment un samedi au saut du lit ;-)
    Result = Table.TransformColumnTypes(Table.Combine(List.Transform(Custom, each Table.FromRows({_}, Columns{0}))), List.Zip({Columns{0}, Columns{1}}), "fr_FR")
in
    Result

J'ai corrigé (çà c'était facile), pour avoir un nombre décimal.

Je continue de chercher comment contourner cette limitation de 100 lignes quand on passe par la syntaxe
Source = Web.Page(Web.Contents("URL_dans_Navigateur")),

 

[alexga78]

Bonjour JM, le fil,

L'étape "Columns" permet de créer une liste de 3 listes (Nom des colonnes Finales, type des colonnes finales, nom des colonnes de la source) par simplicité et pour une utilisation future dans le code.

La fonction Table.TransformColumnTypes requière une synthaxe {{"Nom Colonne", type}} - liste de liste pour chaque colonne, la fonction List.Zip permet de faire ceci.

Cordialament,

 

[Staple1600]

Bonjour @alexga78

Merci pour les explications

Est-ce que tu utilises et/ou connais Sharepoint ?

 

[Staple1600]

Re

@alexga78
En regardant ton code M dans ta réponse dans le fil de cathodique, j'ai repris de bout de code
Comme ce qui m'intéresse c'est de lister les PDF dans un dossier donné de Sharepoint
Je vais tester ton code lundi

let
    Source = Folder.Files(Record.ToList(Excel.CurrentWorkbook(){[Name="Folder_Path"]}[Content]{0}){0}),
    #"Autres colonnes supprimées" = Table.SelectColumns(Source,{"Name"})
in
#"Autres colonnes supprimées"

ou dans Folder_Path j'aurais copié URL_dans_Navigateur

Normalement puisque cela fonctionne avec Web.Page(Web.Contents, cela devrait aussi être peut-être le cas avec Folder.Files,non ?
(Mais je crains que non)

 

[alexga78]

Re,
désolé je n'ai pas d'accès à un sharepoint, donc c'est difficile de t'aider ou de tester...