informer

Il n'est pas rare de trouver dans des sources de données au format Excel et autres, des champs avec des listes de valeurs. L'objectif est alors de créer une ligne supplémentaire par valeur de la liste. Un exemple vaut mieux qu'une longue explication alors illustration

La source de travail



Et l'objectif est celui-ci




Le langage M possède des fonctions extrêmement puissant pour réaliser cet opération. Il faut faire les opérations suivantes :

1. Transformer les champs multivaluées en objet liste
2. Développer les champs avec des objets listes

Voici le code dans son entier

Code c :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
let 
let 
Source = Excel.Workbook(File.Contents("C:\Data\Fichier.xlsx"), null, true),
DataTable = Source{[Item="Data",Kind="Table"]}[Data],
Typage = Table.TransformColumnTypes(DataTable,{{"Cost Item", type text}, {"Value", Int64.Type}, {"Location", type text}, {"Business", type text}, {"Department", type text}}),
ListInCol = 
  let 
    tblWithList = Table.TransformColumns(Typage,{{"Location", each Text.Split(_,",")},{"Business", each Text.Split(_,",")},{"Department", each Text.Split(_,",")} } ), anyOut = tblWithList
  in anyOut,
  ExpandLocation  = Table.ExpandListColumn(ListInCol, "Location"),
  ExpandBusiness = Table.ExpandListColumn(ExpandLocation, "Business"),
  ExpandDpt = Table.ExpandListColumn(ExpandDpt, "Department")
  in ExpandBusiness

Les 3 premières lignes sont pour le chargement et le formatage du fichier

Code c :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Source =  Excel.Workbook(File.Contents("C:\Data\Fichier.xlsx"),  null, true),
DataTable = Source{[Item="Data",Kind="Table"]}[Data],
Typage  = Table.TransformColumnTypes(DataTable,{{"Cost Item", type text},  {"Value", Int64.Type}, {"Location", type text}, {"Business", type text},  {"Department", type text}}),

il faut ensuite transformer les colonnes de la liste en objet Liste

La fonction qui permet de modifier les colonnes est Table.TransformColumns dont la définition est :

Code c :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
<a href="https://powerquery.how/table-transformcolumns/" target="_blank">Table.TransformColumns</a>(  table as table, // Source de données
   transformOperations as list, // Méthode (fonction) appliquée aux colonnes  {{NommDeColonne1, méthode1},...,{NommDeColonneN, méthodeN}}
   optional defaultTransformation as nullable function,// Voir <a href="https://powerquery.how/table-transformcolumns/" target="_blank">ici</a>
   optional missingField as nullable number // Voir <a href="https://powerquery.how/table-transformcolumns/" target="_blank">ici </a>) as table

Le code pour transformer chaque colonne en objet liste

Code c :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
ListInCol = 
  let 
     tblWithList = Table.TransformColumns(Typage,{{"Location", each Text.Split(_,",")},{"Business", each Text.Split(_,",")},{"Department", each Text.Split(_,",")} } ), anyOut = tblWithList
  in anyOut,

Le code pour développer chaque colonne objet liste en ligne

Code c :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
ExpandLocation  = Table.ExpandListColumn(ListInCol, "Location"),
ExpandBusiness = Table.ExpandListColumn(ExpandLocation, "Business"),
ExpandDpt = Table.ExpandListColumn(ExpandDpt, "Department")
in ExpandBusiness

Code générique pour ne pas écrire les noms de colonnes en dur

Code c :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
ListInCol = 
  let 
    lstColName = Table.ColumnNames(Typage)
    ,lstFieldForExpand = List.Skip (lstColName, 2) 
    ,tblWithList = Table.TransformColumns( Typage,List.Transform(lstFieldForExpand, each {_, each Text.Split(_,",")})), anyOut = tblWithList
  in anyOut,
ColumnsExpanded = 
  let 
    lstColName = Table.ColumnNames(Typage) 
    ,lstFieldForExpand = List.Skip (lstColName, 2)
    ,anyOut = List.Accumulate (lstFieldForExpand , ListInCol, (anyFirstOut, anyCurr)=> Table.ExpandListColumn(anyFirstOut, anyCurr) )
  in anyOut 
in ColumnsExpanded

C'est maintenant Votre Minute M !