Faire calcul conditionnel selon 2 tables

Ce sujet contient 10 réponses, 3 participants et a été mis à jour pour la dernière fois par keveen.vigie, le il y a 1 année et 7 mois.

11 sujets de 1 à 11 (sur un total de 11)

Auteur

Articles
16 septembre 2022 à 10 h 18 min #107196

keveen.vigie
Participant

Bonjour à tous,

Je suis débutant sur PBI et powerQuery et je suis confronté à un problème.

J’ai 2 tables:

* Invoiced Sales
qui contient les champs suivantes:
– date de commande
– montant commande (montant en devise locale)
– identifiant devise locale

* Rate Fx:
qui contient les champs suivants:
– l’année (2019, 2020…)
– l’identifiant de device
– le taux de conversion en USD

Je souhaiterai ajouter un champ dans ma table “Invoiced Sales” qui soit le résultat du calcul:
[montant commande en devise local] * [taux de conversion]
et ce, en fonction de la date de commande:
pour les commandes de 2021, appliquer le taux 2021 etc..

Je ne vois pas du tout comment faire.

Déjà est-ce faisable?

A l’avance merci

16 septembre 2022 à 16 h 33 min #107205

Daniel
Participant

Bonjour keeveen, Le Forum,

Plusieurs étapes seront nécessaires.
1 – Chargement de la 1ère table (Invoiced Sales)
2 – Chargement de la seconde table (Rate fx)
3 – Ajout d’une colonne personnalisée (Montant en USD), elle correspond à l’étape ‘AjoutColMontantUSD’ de la requête ci-dessous, pour chaque ligne de ta table Invoiced Sales, on va regarder quelles lignes de la table Rate fx correspondent à l’année et à la devise et on va récupérer le taux de change qui sera multiplié par le montant commande
Quelques explications :
Table.SelectRows(tabExchangeRate,
(varCalc) => varCalc[Devise code]=[Devise code]
and varCalc[Année]=Date.Year([Date de commande] )) permet de sélectionner les lignes de la table “Rate Fx” que j’ai appelé tabExchangeRate si on lui ajoute [Taux de change] on récupère une liste d’une ligne correspondant au taux de change de la devise et de l’année de la table “Invoiced Sales”, et en lui ajoutant {0} on récupère le taux de change, il suffit de multiplier le montant de commande par ce taux pour obtenir le montant en USD

Début de la requête
let
Source = Excel.CurrentWorkbook(){[Name=”Tableau1″]}[Content],
ModificationType = Table.TransformColumnTypes(Source,{{“Montant commande”, type number}, {“Date de commande”, type date}}),
AjoutColMontantUSD = Table.AddColumn(ModificationType, “Montant en USD”, each [Montant commande] * Table.SelectRows(tabExchangeRate,
(varCalc) => varCalc[Devise code]=[Devise code]
and varCalc[Année]=Date.Year([Date de commande] ))[Taux de change]{0}, type number)
in
AjoutColMontantUSD
Fin de la requête

En espérant avoir pu t’aider à résoudre ton problème
Cordialement
Daniel

16 septembre 2022 à 16 h 38 min #107206

Éric Ste-Croix
Participant

Bonjour

Il y a plusieurs facon d’y parvenir. En voici une.

Dans votre table Invoiced_Sales, creer une colonne calcule afin d’isoler l’annee de la commande.
Fonction YEAR en DAX

Dans votre modele de donnee, creer une relation entre vos deux tables sur la base du champ annee.

Dans votre table Invoiced_Sales, creer une colonne calcule afin d’effectuer le calcul desire, voici le code en DAX :
MontantConverti =
IF(Invoiced_Sales[Identifiant_Devise_locale] = “USD”,
Invoiced_Sales[Montant commande] * RELATED(‘Rate FX'[Taux_de_conversion]),
Invoiced_Sales[Montant commande] )

La fonction LOOKUPVALUE peut egalement etre utilise lorsqu’il n’y a pas de relation entre les 2 tables.

En esperant que cela pourra vous aider

Cordialement

Eric Ste-Croix

19 septembre 2022 à 3 h 49 min #107233

keveen.vigie
Participant

Hello

Merci de vous pencher sur mon soucis.

@Eric: ta solution ne peut pas convenir car elle ne prend pas en compte l’année (taux variable selon l’année)

@Daniel:

J’ai essayé d’adapter ton exemple à mon modèle mais j’ai une erreur:

let
Source = Excel.Workbook(Web.Contents(“https://XXXXXX.sharepoint.com/XXXXX/Petra%20report.xlsx”), null, true),
#”Invoiced Sales_Sheet” = Source{[Item=”Invoiced Sales”,Kind=”Sheet”]}[Data],
#”Changed Type” = Table.TransformColumnTypes(#”Invoiced Sales_Sheet”,{{“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}, {“Column17”, type text}, {“Column18”, type text}, {“Column19”, type any}, {“Column20”, type any}, {“Column21”, type any}, {“Column22″, type text}}),
#”Removed Top Rows” = Table.Skip(#”Changed Type”,3),
#”Promoted Headers” = Table.PromoteHeaders(#”Removed Top Rows”, [PromoteAllScalars=true]),
#”Dernières lignes supprimées” = Table.RemoveLastN(#”Promoted Headers”,2),
#”Changed Type1″ = Table.TransformColumnTypes(#”Dernières lignes supprimées”,{{“Sales Organization”, type text}, {“Distribution Channel”, type text}, {“Product Hierarchy Top Level”, type text}, {“Plant”, type text}, {“Invoice Number”, Int64.Type}, {“Billing Type”, type text}, {“Release to Accounts Status”, type text}, {“Posting Date”, type date}, {“Sold To”, Int64.Type}, {“Sold To Name”, type text}, {“Sold To Country”, type text}, {“Ship To”, Int64.Type}, {“Ship-To Name”, type text}, {“Ship To Country”, type text}, {“Material Code”, type text}, {“Material Description”, type text}, {“Batch”, type text}, {“Profit Centre”, Int64.Type}, {“Quantity in Base UOM”, Int64.Type}, {“Net Sales”, type number}, {“Net Sales in doc currency”, type number}, {“Doc Currency”, type text}}),
#”Colonne fusionnée insérée” = Table.AddColumn(#”Changed Type1″, “Customer”, each Text.Combine({Text.From([Sold To], “fr-FR”), [Sold To Name]}, ” – “), type text),
#”Type modifié” = Table.TransformColumnTypes(#”Colonne fusionnée insérée”,{{“Sold To”, type text}}),
#”AjoutColMontantUSD” = Table.AddColumn(#”Type modifié”, “Net Sales USD”, each [Net Sales] * Table.SelectRows(#”Official Rate Fx”,
(varCalc) => varCalc[Currency]=[Doc Currency]
and varCalc[Année]=Date.Year([Posting Date] ))[Rate]{0}, type number),
#”Montant en USD” = AjoutColMontantUSD{0}[Net Sales USD]
in
#”Montant en USD”

Il n’y a pas assez d’élément dans l’énumération pour terminer l’opération….

19 septembre 2022 à 12 h 11 min #107280

Daniel
Participant

Bonjour keveen, Le Forum,

Peux-tu me dire à quoi sert la dernière étape #”Montant en USD” = AjoutColMontantUSD{0}[Net Sales USD], en faisant le test avant de poster ma 1ère réponse cela n’en faisait pas partie et j’obtenais le montant converti dans la devise USD ?
S’il t’était possible de fournir un exemple de données anonymisées de tes deux tables, je pourrais regarder, car comme cela je ne vois pas d’où cela vient.
En pièce jointe, mon essai par rapport à ta description initiale.

Attachments:
You must be logged in to view attached files.

19 septembre 2022 à 17 h 42 min #107308

Éric Ste-Croix
Participant

Bonjour Keveen,

La solution que je propose gere le dynamisme sur l’annee a partir :

1 – De la relation entre les 2 tables dans votre modele de donnee sur la base du champ Annee.
2 – La fonction DAX “RELATED” qui permet de recuperer le taux de conversion selon l’annee de la transaction.

N.B. : Cette solution doit etre developpe en DAX (Champs calcules) et non dans Powerquery.

Cordialement

Eric Ste-Croix

20 septembre 2022 à 3 h 35 min #107315

keveen.vigie
Participant

Bonjour vous deux

@Daniel Blanc:
#”Montant en USD” = AjoutColMontantUSD{0}[Net Sales USD] doit être une erreur de ma part… J’avais fait des essais et c’est surement un reste.
Mais même sans j’ai une erreur…
Le truc c’est que je ne comprends pas la ligne suivante:

Table.AddColumn(#”Type modifié”, “Net Sales USD”, each [Net Sales] * Table.SelectRows(#”Official Rate Fx”,
(varCalc) => varCalc[Currency]=[Doc Currency]
and varCalc[Year]=Date.Year([Posting Date] ))[Rate]{0}, type number)

Donc compliquer de trouver mon erreur.
Pour info:
[Net Sales], [Posting Date] et [Doc Currency] sont des champs de ma table “Invoiced Sales”
[Year], [Currency], [Rate] sont des champs de ma table “Official Rate Fx”

Par contre à partir de la ligne (varCalc)…. je ne pige rien à la formule.

@Eric St-Croix:
Je vais faire des essais supplémentaires du coup et vous tiens informé.

20 septembre 2022 à 6 h 14 min #107606

keveen.vigie
Participant

@Eric:

Par contre ta formule ne recupère le taux que pour les lignes USD, du coup je dois embriquer les IF pour faire tous les cas de figure?

A moins qu’il existe un équivalent de select Case en VB.

20 septembre 2022 à 8 h 12 min #107629

keveen.vigie
Participant

@Daniel

Cela fonctionne nickel… sans faute de frappe de ma part 😉

merci beaucoup

20 septembre 2022 à 12 h 05 min #107919

Daniel
Participant

Bonjour keveen, Eric, Le Forum,

Content de voir que cela fonctionne, merci pour le retour.

Afin de te permettre de comprendre la formule utilisée, j’ai joint quelques explications dans le document ci-joint
Cordialement
Daniel

Attachments:
You must be logged in to view attached files.

21 septembre 2022 à 7 h 08 min #107929

keveen.vigie
Participant

Super, un grand merci car la formule restait obscure pour moi 😉
Auteur

Articles