oguruma
XLDnaute Impliqué
Bonjour,
ici dans ce poste deux fonctions qui permettent d'attribuer un type text, number... etc. à toutes les colonnes d'une table ou à une partie soit par exemple une partie du début, une partie de la fin....
fnColumnsToSameTypeV1
Par défaut début et fin sont à zéro, c'est-à-dire que toutes les colonnes seront transformées
= fnColumnsToSameTypeV1(Source,"type text")
le type text est attribué à toutes les colonnes
fnColumnsToSameTypeV2
Par défaut R : pour Range
F : Les n premières
L : les n dernières
N : N° de colonne spécifique
Exemples d'utilisation
= fnColumnsToSameTypeV1(Source,"type text",0,0)
= fnColumnsToSameTypeV2(Source,"type text",null,null)
= fnColumnsToSameTypeV2(Source,"type text",2,null,"F")
= fnColumnsToSameTypeV2(Source,"type number",1,null,"L")
= fnColumnsToSameTypeV2(Source,"type text",3,null,"N")
= fnColumnsToSameTypeV2(Source,"Int64.Type",1,null,"L") ==> nombre entier
= fnColumnsToSameTypeV2(Source,"Currency.Type",1,null,"L") ==> format monétaire
= fnColumnsToSameTypeV2(Source,"type date",1,null,"F")
voir la documentation Pwq sur les formats acceptés
Avec quelques fonctions de rappels :
ici dans ce poste deux fonctions qui permettent d'attribuer un type text, number... etc. à toutes les colonnes d'une table ou à une partie soit par exemple une partie du début, une partie de la fin....
fnColumnsToSameTypeV1
PowerQuery:
let fnColumnsToSameTypeV1 = (
pTable as any,
optional pColumnType as text,
optional pStartPosition as number,
optional pEndPosition as number,
optional pFunction as text) =>
let
Source = if pTable is text
then Excel.CurrentWorkbook(){[Name=pTable]}[Content]
else pTable,
StartPosition=if pStartPosition is null then 0 else pStartPosition,
EndPosition=if pEndPosition is null then 0 else pEndPosition,
ColumnType=if pColumnType is null then "text" else pColumnType,
Function=if pFunction is null then "R" else Text.Upper(pFunction), // "F:L:R:N"
StrType=ColumnType,
EvalColumnType=Expression.Evaluate(StrType,[Currency.Type=Currency.Type, Int64.Type=Int64.Type, Percentage.Type=Percentage.Type]),
TypeModified = Table.TransformColumnTypes(
Source,
List.Transform(
List.RemoveLastN(List.RemoveFirstN(Table.ColumnNames(Source),StartPosition),EndPosition),
each {_, EvalColumnType}
)
)
in
TypeModified
in
fnColumnsToSameTypeV1Par défaut début et fin sont à zéro, c'est-à-dire que toutes les colonnes seront transformées
= fnColumnsToSameTypeV1(Source,"type text")
le type text est attribué à toutes les colonnes
fnColumnsToSameTypeV2
PowerQuery:
let fnColumnsToSameTypeV2 = (
pTable as any,
optional pColumnType as text,
optional pStartPosition as number,
optional pEndPosition as number,
optional pFunction as text) =>
let
Source = if pTable is text
then Excel.CurrentWorkbook(){[Name=pTable]}[Content]
else pTable,
StartPosition=if pStartPosition is null then 0 else pStartPosition,
EndPosition=if pEndPosition is null then 0 else pEndPosition,
ColumnType=if pColumnType is null then "text" else pColumnType,
Function=if pFunction is null then "R" else Text.Upper(pFunction), // "F:L:R:N"
StrType= ColumnType,
EvalColumnType=Expression.Evaluate(StrType,[Currency.Type=Currency.Type, Int64.Type=Int64.Type, Percentage.Type=Percentage.Type]),
TypeModified = if Function = "R" // Colonnes entre deux positions
then
Table.TransformColumnTypes(
Source,
List.Transform(
List.RemoveLastN(List.RemoveFirstN(Table.ColumnNames(Source),StartPosition),EndPosition),
each {_, EvalColumnType}
)
)
else if Function = "F" // 1ère colonnes
then
Table.TransformColumnTypes(
Source,
List.Transform(
List.FirstN(Table.ColumnNames(Source),StartPosition),
each {_, EvalColumnType}
)
)
else if Function = "L" // Dernières colonnes
then
Table.TransformColumnTypes(
Source,
List.Transform(
List.LastN(Table.ColumnNames(Source),StartPosition),
each {_, EvalColumnType}
)
)
else if Function = "N" // Une colonne spécifique par sa position dans la table
then
Table.TransformColumnTypes(
Source,
List.Transform(
{Table.ColumnNames(Source){StartPosition - 1}},
each {_, EvalColumnType}
)
)
else null
in
TypeModified
in
fnColumnsToSameTypeV2Par défaut R : pour Range
F : Les n premières
L : les n dernières
N : N° de colonne spécifique
Exemples d'utilisation
= fnColumnsToSameTypeV1(Source,"type text",0,0)
= fnColumnsToSameTypeV2(Source,"type text",null,null)
= fnColumnsToSameTypeV2(Source,"type text",2,null,"F")
= fnColumnsToSameTypeV2(Source,"type number",1,null,"L")
= fnColumnsToSameTypeV2(Source,"type text",3,null,"N")
= fnColumnsToSameTypeV2(Source,"Int64.Type",1,null,"L") ==> nombre entier
= fnColumnsToSameTypeV2(Source,"Currency.Type",1,null,"L") ==> format monétaire
= fnColumnsToSameTypeV2(Source,"type date",1,null,"F")
voir la documentation Pwq sur les formats acceptés
Avec quelques fonctions de rappels :
PowerQuery:
let
//----------------------------------------------------------------------------------------------
// La fonction attend la table des types pour chaque colonne
// La table passée en paramètre peut être soit de type text soit de type table
//----------------------------------------------------------------------------------------------
fnTransformTypes=(pTable as any) as list =>
let
//------------------------------------------------------------------------------------------
// On évalue le type de paramètre table
//------------------------------------------------------------------------------------------
Source= if pTable is text
then Excel.CurrentWorkbook(){[Name=pTable]}[Content]
else if pTable is table then pTable else null,
//-----------------------------------------------------------------------------------------------
// La table des types doit comporter deux colonnes
// colonne 1 : nom de la colonne à transformer
// colonne 2 : type de la colonne
// les noms des colonnes ne sont pas imposés puisque la fonction les détecte à la ligne suivante
//------------------------------------------------------------------------------------------------
LstCol=Table.ColumnNames(Source),
//------------------------------------------------------------------------------------------
// Les noms des colonnes sont récupérés dans la variable de liste LstCol
// LstCol{0} = colonne 1 - nom des colonnes
// LstCol{0} = colonne 2 - types de colonnes
//------------------------------------------------------------------------------------------
ChangeColTypes = Table.TransformColumnTypes(Source,{{LstCol{0}, type text}, {LstCol{1}, type text}}),
//------------------------------------------------------------------------------------------
// On prend aussi en compte les types non primitifs
// [Currency.Type=Currency.Type, Int64.Type=Int64.Type, Percentage.Type=Percentage.Type]
// Astuce importante :
// Il est nécessaire de les déclarer afin qu'ils soient reconnus dans le contexte PQ
//------------------------------------------------------------------------------------------
ToType = Table.TransformColumns(ChangeColTypes,{{LstCol{1}, each Expression.Evaluate(_, [Currency.Type=Currency.Type, Int64.Type=Int64.Type, Percentage.Type=Percentage.Type])}}),
ToField = Table.AddColumn(ToType, "CustomTypes", each Record.FieldValues(_)),
//------------------------------------------------------------------------------------------
// Création de la liste des types à appliquer à chaque colonne
//------------------------------------------------------------------------------------------
RemoveOtherCols = Table.SelectColumns(ToField,{"CustomTypes"}),
ToList = RemoveOtherCols[CustomTypes]
in
try ToList otherwise null
in
try fnTransformTypes otherwise null
PowerQuery:
let
// LstType
Source= Excel.CurrentWorkbook(){[Name="TblType"]}[Content],
LstCol=Table.ColumnNames(Source),
ChangeColTypes = Table.TransformColumnTypes(Source,{{LstCol{0}, type text}, {LstCol{1}, type text}}),
ToType = Table.TransformColumns(ChangeColTypes,{{LstCol{1}, each Expression.Evaluate(_, [Currency.Type=Currency.Type, Int64.Type=Int64.Type, Percentage.Type=Percentage.Type])}}),
ToField = Table.AddColumn(ToType, "Custom", each Record.FieldValues(_)),
RemoveOtherCols = Table.SelectColumns(ToField,{"Custom"}),
ToList = RemoveOtherCols[Custom]
in
ToList