mromain
XLDnaute Barbatruc
Bonjour,
Ce post sert à présenter la fonction PowerQuery
Elle prend en entrée :
Elle retourne :
Le fichier ci-joint contient un exemple d'utilisation de cette fonction.
Code de la fonction
A+
Ce post sert à présenter la fonction PowerQuery
fnCompareTables qui permet de comparer deux tables de données.Elle prend en entrée :
- les deux tables à comparer ;
- la liste des colonnes composant "la clef des données" ;
- optionnellement, le nom des sources.
Elle retourne :
- les colonnes composant "la clef des données" ;
- une colonne StatutComparaison qui peut prendre quatre valeurs :
- Existe dans Source1 mais pas dans Source2 ;
- Existe dans Source2 mais pas dans Source1 ;
- Écarts, si la donnée existe dans les deux sources, mais ne contient pas les mêmes informations ;
- OK, si la donnée existe dans les deux sources et est identique.
- une colonne contenant la donnée de la Source1 ;
- une colonne contenant la donnée de la Source2 ;
- une colonne contenant la liste des écarts.
Le fichier ci-joint contient un exemple d'utilisation de cette fonction.
Code de la fonction
fnCompareTables :
PowerQuery:
let
// fonction permettant de faire la comparaison entre deux tables
fnCompareTables = (source1 as table, source2 as table, keyColumns as list, optional sourceName1 as nullable text, optional sourceName2 as nullable text) as table =>
let
ErrorMsg = [
Title = "Erreur dans les données sources à comparer",
KeyColumnNotDefined = "la/les colonne(s) permettant de définir la ""clé"" des données à comparer n'est/ne sont pas définie(s).",
ErrorDataSourcesStructure = "les sources de données ne comportent pas les mêmes colonnes — colonne(s) concernée(s) : '#{0}'.",
KeyColumnNotFound = "la/les colonne(s) suivante(s) '#{0}' est/sont absente(s) des sources de données.",
EmptyDataSource = "la source '#{0}' ne contient aucune donnée.",
DuplicatesInSource = "la/les colonne(s) '#{0}' ne permet(tent) pas d'identifier la clé des données dans la source '#{1}'."
],
ResultColumns = [
ComparisonStatus = "StatutComparaison",
DataPrefix = "Data - ",
Differences = "Écarts"
],
ComparisonStatus = [
ExistsInOnlyFirstSource = "Existe dans '#{0}' mais pas dans '#{1}'",
ExistsInBothButDifferences = "Écarts",
ExistsInBothAndOK = "OK"
],
sName1 = if sourceName1 = null then "Source 1" else sourceName1,
sName2 = if sourceName2 = null then "Source 2" else sourceName2,
keyCols = List.Transform(keyColumns, Text.From),
listColumnsNames = Table.ColumnNames(source1),
listColumnsInErrors = let listCols2 = Table.ColumnNames(source2) in List.RemoveItems(listColumnsNames, listCols2) & List.RemoveItems(listCols2, listColumnsNames),
missingKeyColumns = List.RemoveItems(keyCols, listColumnsNames),
TableKeys =
let
fnExtractKeysTable = (dataTable as table, sourceName as text) as table =>
let
colCountName = Text.Combine(keyCols) & "_",
GroupByKeysAndCount = Table.Group(dataTable, keyCols, {{colCountName, each Table.RowCount(_), Int64.Type}})
in
if Table.RowCount(dataTable) = 0 then
error [Reason = ErrorMsg[Title], Message = Text.Format(ErrorMsg[EmptyDataSource], {sourceName})]
else if List.Max(Table.Column(GroupByKeysAndCount, colCountName)) > 1 then
error [Reason = ErrorMsg[Title], Message = Text.Format(ErrorMsg[DuplicatesInSource], {Text.Combine(List.Transform(keyCols, each "[" & _ & "]"), " / "), sourceName})]
else
Table.SelectColumns(GroupByKeysAndCount, keyCols)
in
Table.Distinct(fnExtractKeysTable(source1, sName1)&fnExtractKeysTable(source2, sName2)),
MergeSource1 = Table.NestedJoin(TableKeys, keyCols, source1, keyCols, sName1, JoinKind.LeftOuter),
MergeSource2 = Table.NestedJoin(MergeSource1, keyCols, source2, keyCols, sName2, JoinKind.LeftOuter),
ExtractSourceRecords =
let
fnTransform=(tableSrc as table) as nullable record => if Table.RowCount(tableSrc)=0 then null else tableSrc{0}
in
Table.TransformColumns(MergeSource2, List.Transform({sName1, sName2}, each {_, fnTransform, type nullable record})),
resultRecordFields = {ResultColumns[ComparisonStatus], ResultColumns[DataPrefix] & sName1, ResultColumns[DataPrefix] & sName2, ResultColumns[Differences]},
tmpColComparison = "<" & Text.Combine(keyCols, "_") & ">",
AddColumnResultComparaison =
let
fnCompareSourceRecords = (recordSource1 as nullable record, recordSource2 as nullable record) as record =>
let
status =
if recordSource1 = null then
Text.Format(ComparisonStatus[ExistsInOnlyFirstSource], {sName2, sName1})
else if recordSource2 = null then
Text.Format(ComparisonStatus[ExistsInOnlyFirstSource], {sName1, sName2})
else if recordSource1 = recordSource2 then
ComparisonStatus[ExistsInBothAndOK]
else
ComparisonStatus[ExistsInBothButDifferences],
dataRecord1 = if recordSource1 <> null then Record.RemoveFields(recordSource1, keyCols) else null,
dataRecord2 = if recordSource2 <> null then Record.RemoveFields(recordSource2, keyCols) else null,
differences =
if status = ComparisonStatus[ExistsInBothButDifferences] then
Table.RenameColumns(
Table.FromRecords(
List.RemoveNulls(
List.Transform(Record.FieldNames(dataRecord1), each let valData1=Record.Field(dataRecord1, _), valData2=Record.Field(dataRecord2, _) in if valData1=valData2 then null else [Champ=_, valData1=valData1, valData2=valData2])
)
),
{{"valData1", sName1}, {"valData2", sName2}}
)
else
null
in
Record.FromList({status, dataRecord1, dataRecord2, differences}, resultRecordFields)
in
Table.AddColumn(ExtractSourceRecords, tmpColComparison, each fnCompareSourceRecords(Record.Field(_, sName1), Record.Field(_, sName2)), type record),
ExtractResultComparaison =
let
listTransformations = List.Zip({resultRecordFields, {type text, type nullable record, type nullable record, type nullable table}})
in
List.Accumulate(listTransformations, AddColumnResultComparaison, (s, c)=> Table.AddColumn(s, c{0}, each let compRecord = Record.Field(_, tmpColComparison) in Record.Field(compRecord, c{0}), c{1})),
SelectColumns = Table.SelectColumns(ExtractResultComparaison,keyCols & resultRecordFields),
ResultFunction =
if List.Count(keyCols) = 0 then
error [Reason = ErrorMsg[Title], Message = ErrorMsg[KeyColumnNotDefined]]
else if List.Count(listColumnsInErrors) > 0 then
error [Reason = ErrorMsg[Title], Message = Text.Format(ErrorMsg[ErrorDataSourcesStructure], {Text.Combine(List.Transform(listColumnsInErrors, each "[" & _ & "]"), " / ")})]
else if List.Count(missingKeyColumns) > 0 then
error [Reason = ErrorMsg[Title], Message = Text.Format(ErrorMsg[KeyColumnNotFound], {Text.Combine(List.Transform(missingKeyColumns, each "[" & _ & "]"), " / ")})]
else
SelectColumns
in
ResultFunction
in
fnCompareTablesA+
juste un ptit truc 
