Discussion :

[Pierre Dumas]

Bonjour

Je cherche à faire l'équivalent d'un RECHERCHEX avec le paramètre -1 pour le mode de correspondance.

Dans mon exemple, pour un hôtel, j'ai des chambres et des prix qui changent suivant la saison.

J'ai donc une table des saisons (basse, moyenne, haute) avec les noms de fonction des dates (du 1er janvier au 4 avril c'est la basse saison, du 5 avril au 30 juin la moyenne, ...).
J'ai une table avec les chambres et leur réservation de date, et j'aimerai, bien sûr, savoir à quelle saison correspond quelle date pour avoir appliquer le tarif.

J'ai appliqué la solution présentée sur le site du CFO masqué, mais elle me parait un peu lourde.

Est-ce que quelqu'un connait une autre solution plus légère ?

Bonne journée

Pierre Dumas

[mromain]

Bonjour Pierre, le forum,

Ci-dessous une solution possible, si j’ai bien compris.
Après, ça dépend de comment sont tes données d’entrée...

Code :

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

1
2
3
4
5
6
7
let
    TableDefSeasons = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckosLk5V0lEyMIzRNzAEMUyADBOlWJ1oJd/8ytS8PLCsKVhQR8nYAMgwA8t6JJaWwHWag+SADEMjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Saison = _t, DébutPériode = _t, FinPériode = _t]), {{"Saison", Text.Type}, {"DébutPériode", Text.Type}, {"FinPériode", Text.Type}}),
    Source = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtY1MAQipVgdMNdU18BI18gUxjXSNTDVNYbLmugaGuoamSnFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]), {{"Date", Date.Type}}),
    fnYearSeasons = (year) => let fnYearDate = (t) => Date.FromText(t & "/" & Text.From(year), "fr-FR") in Table.TransformColumns(TableDefSeasons, List.Transform({"DébutPériode", "FinPériode"}, each {_, fnYearDate, type date})),
    AddColumnSeason = Table.AddColumn(Source, "Saison", each let d = [Date] in Table.First(Table.SelectRows(fnYearSeasons(Date.Year(d)), each ([DébutPériode] <= d) and [FinPériode] >= d))[Saison], type text)
in
    AddColumnSeason

A+

[Raccourcix]

Bonjour

une autre approche avec les mêmes données que mromain
je crée une table des dates "seuil" pour toutes les années présentes dans les dates puis j'insère les données à la suite, un tri et un "fill down" pour mettre la saison associée à chaque date

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
let
    TableDefSeasons = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckosLk5V0lEyMIzRNzAEMUyADBOlWJ1oJd/8ytS8PLCsKVhQR8nYAMgwA8t6JJaWwHWag+SADEMjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Saison = _t, DébutPériode = _t, FinPériode = _t]), {{"Saison", Text.Type}, {"DébutPériode", Text.Type}, {"FinPériode", Text.Type}}),
    Source = Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtY1MAQipVgdMNdU18BI18gUxjXSNTDVNYbLmugaGuoamSnFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]), {{"Date", Date.Type}}),
    Année = {Date.Year(List.Min(Source[Date]))..Date.Year(List.Max(Source[Date]))},
    AjoutAnnéeTable = Table.AddColumn(TableDefSeasons, "Année", each Année),
    #"Année développé" = Table.ExpandListColumn(AjoutAnnéeTable, "Année"),
    Seuil = Table.AddColumn(#"Année développé", "Date", each Date.From([DébutPériode]&"/"&Text.From([Année])), type date),
    #"Requête ajoutée" = Table.Combine({Seuil, Source}),
    #"Lignes triées" = Table.Sort(#"Requête ajoutée",{{"Date", Order.Ascending}}),
    #"Rempli vers le bas" = Table.FillDown(#"Lignes triées",{"Saison"}),
    #"Lignes filtrées" = Table.SelectRows(#"Rempli vers le bas", each [Année] = null),
    #"Choix Colonnes" = Table.SelectColumns(#"Lignes filtrées",{"Date", "Saison"})
in
    #"Choix Colonnes"

Une autre solution bien plus simple, très efficace, mais bien moins dynamique est de paramétrer en dur les règles. Si elles ne changent jamais, c'est bien plus rapide.

par exemple, on ajoute une colonne pour mettre le date au format MMJJ en numérique Date.Month([Date])*100+Date.Day([Date]).
puis une colonne conditionnelle if [MMJJ] <= 404 then "Basse" else if [MMJJ] <= 630 then "Moyenne" else ...
Stéphane

[Pierre Dumas]

Merci pour les solutions mromain et Raccourcix

Et d'une manière générale, est-ce ainsi que vous procédez quand vous êtes face à ce besoin ?

Ici, j'ai donné l'exemple d'un hôtel avec les chambres et les prix en fonction des saisons. Mais cela pourrait être des prix d'expédition de colis en fonction de leur poids, cela pourrait être une prime en fonction d'un taux de satisfaction ou d'utilisation, cela pourrait être un taux de commission en fonction du montant d'une marge, etc.

Est-ce que l'on pourrait avoir une solution "standard" avec une table de données et une table de correspondance ?

Et merci encore pour votre proposition

Pierre Dumas

[Philippe Tulliez]

Bonjour Pierre,
Je n'ia pas voulu répondre car il me semblait que l'exigence était d'avoir une solution avec Power Query mais j'utilise justement un cas similaire de location de chambre pour illustrer les fonctions de recherche.
J'utilise deux tableaux, l'un avec les dates et le type de tarification et l'autre contenant les prix. Voir illustration.

Illustration d'un cas simple donc sans une location qui englobe deux saisons une basse et une moyenne par exemple

[Raccourcix]

Il y a quelques années j'avais réalisé des tests pour simuler la fonction EQUIV(;;1)
une table de 1000 valeurs aléatoires entre 0,00 et 100,00 + une table de 6 seuils (0, 8, 12, 30, 50, 80) avec chacune une catégorie de A à F.
l'objectif étant de mettre la lettre correspondant aux 1000 valeurs.

Au final, toutes les solutions étaient plus lentes de 10 à 40 % que les if imbriqués (qui ne sont pas dynamiques) - et ce malgré l'utilisation du buffer pour mettre en mémoire la liste des seuils et celles des catégories
- table.combine + tri + fill down que j'ai utilisé dans mon exemple précédent.
- développement de tous les seuils sur toutes les lignes, filtre pour supprimer les seuil supérieurs à la valeur, regroupement pour récupérer le max des seuils restant
- ListPositionOf
- ListMax + ListSelect
- fonction pour filtrer la table des seuils et prendre la dernière ligne (similaire à la solution de mromain)

il faudrait que je reprenne ces tests avec mes connaissances actuelles pour voir si je trouve pas des optimisations.

Avec des dates on pourrait aussi générer une liste de toutes les catégories jour par jour et faire une fusion "inner" (ce qu'on ne peut pas faire avec des valeurs aléatoires qui sont trop nombreuses)

Stéphane

[Pierre Dumas]

Bonsoir

Merci @Philippe Tulliez, mais effectivement, ce qui m'intéressait, c'était bien de passer par Power Query.

Merci @Raccourcix pour ces détails et précisions.

En tout cas, il ne semble pas qu'il y ait, en ce moment, une solution simple, fiable et rapide pour répondre à ce genre de question.

Merci pour les éclairages et bonne continuation

Pierre Dumas

[Raccourcix]

Bonjour Pierre

je reviens sur nos échanges il y a quelques jours avec une réflexion à vous soumettre

l'inconvénient de la solution de mromain ou d'autres solutions du même type est que l'on applique ligne à ligne une fonction. ce qui est très lourd s'il y a de nombreuses données dans la table (plusieurs milliers par exemple)
l'inconvénient de la solution que j'ai proposée avec l'ajout des 2 tables, un tri et un fill down est le tri qui peut également être lourd suivant les données.

une autre approche serait de partir de la table avec les plages de dates, et de filtrer les données avec une fonction suivant les date de début et de fin. il ne reste plus qu'à développer les colonnes.
avec cette méthode, on applique peu de fois la fonction de filtre sur une grande table (qu'on pourrait mettre en buffer au besoin)
l'inconvénient cette fois est qu'on perd l'ordre des données

j'ai repris mon jeu de données test avec 5000 valeurs entre 0,00 et 100,00 + les 6 tranches de valeurs 0-8, 8-12, 12-30, 30-50, 50-80 et 80-101 (seuils bas inclus dans le filtre et seuils haut exclus)
avec VBA j'actualise 10 ou 20 fois successivement chacune des requêtes et je calcule un temps moyen

avec 5000 valeurs, le if imbriqué est toujours le plus efficace 0,33 seconde (mais les seuils sont en dur), cette autre approche à 0,34 (mais on perd l'ordre initial) et ma solution initiale avec le tri est à 0.35 (moins rapide de peu, mais on conserve l'ordre des données)
les autres solutions sont plus lentes

Suivant la volumétrie des données, il faut tester différentes approches
Stéphane

[mromain]

Bonjour à tous,

Ci-dessous une autre proposition où on transforme le paramétrage des saisons : on passe d'une ligne par période à une ligne par jour avec une colonne code jour (le jour au format MMJJ).
Cela permet de faire une simple jointure sur la table source avec un niveau de performance équivalent au paramétrage des règles "en dur" comme proposé par Stéphane au post #3.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
let
    TableDefSeasons = Table.FromRecords({[Saison="Basse", DébutPériode="01/01", FinPériode="04/04"], [Saison="Moyenne", DébutPériode="05/04", FinPériode="30/06"], [Saison="Haute", DébutPériode="01/07", FinPériode="31/12"]}),
 
    TransformTableDefSeasons = 
        let
            fnDaysCodesMDDList = (startTxt, endTxt) => 
                let
                    fnSplit = each List.Transform(Text.Split(_, "/"), Number.From),
                    startList = fnSplit(startTxt),
                    endList = fnSplit(endTxt),
                    monthsList = {startList{1} .. endList{1}},
                    daysList = {{startList{0} .. 31}} & List.Transform(List.Skip(monthsList, 2), each {1 .. 31}) & {{1 .. endList{0}}}
                in
                    List.TransformMany(List.Zip({monthsList, daysList}), each _{1}, (x, y) => x{0} * 100 + y),
            AddColumnsDaysCodeList = Table.AddColumn(TableDefSeasons, "CodeJourMJJ", each fnDaysCodesMDDList([DébutPériode],[FinPériode]), type list)
        in
            Table.ExpandListColumn(AddColumnsDaysCodeList, "CodeJourMJJ"),
 
    Source = #table(type table [Date=date], List.Transform(List.Dates(#date(2000,1,1), 150000, #duration(1,0,0,0)), each {_})),
    AddColumnDaysCode = Table.AddColumn(Source, "CodeJourMJJ", each Date.Month([Date])*100 + Date.Day([Date])),
    MergeDefSeasons = Table.NestedJoin(AddColumnDaysCode, {"CodeJourMJJ"}, TransformTableDefSeasons, {"CodeJourMJJ"}, "DefSaison", JoinKind.LeftOuter),
    DeleteColumnDaysCode = Table.RemoveColumns(MergeDefSeasons,{"CodeJourMJJ"}),
    DevelopSeason = Table.ExpandTableColumn(DeleteColumnDaysCode, "DefSaison", {"Saison"}, {"Saison"})
in
    DevelopSeason

A+

[Raccourcix]

Bonjour
Cette autre approche est possible car les MMJJ forment une liste exhaustive de 12*31 valeurs : 101 à 131 ... 1201 à 1231
ce qui n'est pas le cas de tous les RECHERCHEX approximés par le bas.

un autre possibilité de la proposition de mromain avec un code plus simple pour l'étape TransformTableDefSeasons (et des Table.Join plutôt que des Table.NestedJoin)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
let
    TableDefSeasons = Table.FromRecords({[Saison="Basse", DébutPériode="01/01", FinPériode="04/04"], [Saison="Moyenne", DébutPériode="05/04", FinPériode="30/06"], [Saison="Haute", DébutPériode="01/07", FinPériode="31/12"]}),
    Ajout_MMJJ = Table.AddColumn(TableDefSeasons, "MMJJ_seuil", each Number.From(Text.End([DébutPériode],2))*100+Number.From(Text.Start([DébutPériode],2))),
 
    MMJJ_exhaustive1 = Table.Join(Table.FromColumns({List.TransformMany({1..12}, each {1..31}, (x,y) => x*100+y)}, {"CodeJourMJJ"}), {"CodeJourMJJ"}, Ajout_MMJJ, {"MMJJ_seuil"}, JoinKind.LeftOuter),
    MMJJ_exhaustive2 = Table.FillDown(MMJJ_exhaustive1,{"Saison", "DébutPériode", "FinPériode"}),
 
    Source = #table(type table [Date=date], List.Transform(List.Dates(#date(2000,1,1), 150000, #duration(1,0,0,0)), each {_})),
    AddColumnDaysCode = Table.AddColumn(Source, "CodeJourMJJ", each Date.Month([Date])*100 + Date.Day([Date])),
    MergeDefSeasons = Table.Join(AddColumnDaysCode, {"CodeJourMJJ"}, MMJJ_exhaustive2, {"CodeJourMJJ"})
in
    MergeDefSeasons

Stéphane