Power Query & SharePoint Library

PowerBI

 

Contexte :

Un client a sur son Office 365 plusieurs librairies SharePoint pour les différentes filiales et la direction internationale du groupe. Chacune des filiales ne peut accéder qu’à leurs bibliothèques mais la direction internationale à tous les droits sur toutes les librairies, des Workflows SharePoint de validation seront par la suite implémentés (ouch ! je vais devoir m’y mettre).

Un cube tabulaire Power Pivot doit être implémenté permettant la consolidation des données provenant des différentes filiales ainsi que de la direction.

Pour simplifier l’alimentation un Workflow, suite à une validation regroupera les documents d’alimentation dans une seule librairie qui sera cachée aux utilisateurs et deviendra ma seule source d’alimentation.

Maintenant que toutes mes données à alimenter sont regroupées dans une même librairie, comment les merger, les rapatrier dans mon modèle, les nettoyer, les corréler, … via une requête M (langage de programmation pour Power Query) ?

 

Power Query

Power Query n’est plus à présenter, c’est l’ETL pour les utilisateurs finaux de la stack Self-Service BI de Microsoft (Power BI).

Le langage pour effectuer des requêtes dans Power Query est le M mais aucune connaissance en informatique n’est nécessaire pour effectuer la plupart des transformations de base :

PowerQuery

 

Liste des documents d’une librairie SharePoint

PowerQueryAdvanced

Voici la requête pour afficher la liste des documents présents dans la librairie « INPUT » du site SharePoint Pulsweb/Test (à copier dans l’éditeur de requête avancé) :

let 
    Source = SharePoint.Contents("https://pulsweb.sharepoint.com/sites/Test/"),
    Q1 = Source{[Name="INPUT"]}[Content]
in
    Q1

Résultat :
PowerQueryResult

 

Données d’un fichier Excel d’une librairie SharePoint

Voici la requête permettant d’afficher les données de l’onglet « IN » du fichier Excel « MyExcelFile.xlsx » situé dans la librairie « INPUT » du site SharePoint Pulsweb/Test :

let Source = SharePoint.Contents("https://pulsweb.sharepoint.com/sites/Test/"),
    Q1 = Source{[Name="INPUT"]}[Content],
    Q2 = Q1{[Name="MyExcelFile.xlsx"]}[Content],
    Q3 = Excel.Workbook(Q2),
    Q4 = Q3{[Item="IN",Kind="Sheet"]}[Data],
    Q5 = Table.TransformColumnTypes(Q4,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}}),
    Q6 = Table.Skip(Q5,1),
    Q7 = Table.PromoteHeaders(Q6),
    Q8 = Table.RemoveColumns(Q7,{"Indicator_Name", "Indicator_Unit"}),
    Q9 = Table.UnpivotOtherColumns(Q8,{"Indicator_Code", "Target_Type"},"Attribute","Value"),
    Q10 = Table.TransformColumnTypes(Q9,{{"Value", type number}, {"Attribute", type date}})
in Q10

Document initial :
PowerQueryInputFile

Résultat :
PowerQuery_Result

 

Création de fonctions

Il est possible de créer des fonctions en M permettant d’enchainer un certain nombre de tâches et de leur passer des paramètres. Ma fonction GetSharepointFile prend en entrée l’adresse du site SharePoint et le nom du fichier à mettre au bon format :

let Source = (SharepointSite,FileName) =>
let Source = SharePoint.Contents(SharepointSite),
    Q1 = Source{[Name="INPUT"]}[Content],
    Q2 = Q1{[Name=FileName]}[Content],
    Q3 = Excel.Workbook(Q2),
    Q4 = Q3{[Item="IN",Kind="Sheet"]}[Data],
    Q5 = Table.TransformColumnTypes(Q4,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}}),
    Q6 = Table.Skip(Q5,1),
    Q7 = Table.PromoteHeaders(Q6),
    Q8 = Table.RemoveColumns(Q7,{"Indicator_Name", "Indicator_Unit"}),
    Q9 = Table.UnpivotOtherColumns(Q8,{"Indicator_Code", "Target_Type"},"Attribute","Value"),
    Q10 = Table.TransformColumnTypes(Q9,{{"Value", type number}, {"Attribute", type date}})
in Q10
in Source

La fonction GetFileData prend en entrée l’adresse d’un site SharePoint, liste ses différents documents et exécute la fonction GetSharepointFile pour chacun d’entre eux :

let Source = (SharepointSite) =>
let Source = SharePoint.Contents(SharepointSite),
    Q1 = Source{[Name="INPUT"]}[Content],
    Q2 = Table.SelectColumns(Q1,{"Name"}),
    Q3 = Table.AddColumn(Q2, "Custom", each GetSharepointFile(SharepointSite,[Name])),
    Q4 = Table.ExpandTableColumn(Q3, "Custom", {"Indicator_Code", "Target_Type", "Attribute", "Value"}, {"Custom.Indicator_Code", "Custom.Target_Type", "Custom.Attribute", "Custom.Value"})
in Q4
in Source

Tous mes documents Excel de la librairie SharePoint sont maintenant mergés, nettoyés et aux formats attendus !

 

Conclusion

Le langage M n’est pas compliqué à appréhender, il suffit de découper les différentes étapes du flux au même titre que dans un ETL 😉

Petit point négatif, l’environnement de développement avec l’éditeur avancé n’est pas très pratique et la description des erreurs est limitée… mais le produit reste jeune et évolue assez vite…

 

1 Comment

  • Chris Says

    Bonjour,

    une Query qui récupère et consolide les données de plusieurs fichiers d’un dossier Sharepoint devrait demander les identifiants pour ce connecter au site Sharepoint pour chaque fichier.

    Est il possible qu’elle ne demande à valider ses identifiants qu’une seule fois, soit demander les accès uniquement pour le dossier contenant les fichiers ?

Comments are closed.