Faire calcul conditionnel selon 2 tables

Accueil – Le CFO masqué Forums Power Query 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 semaine et 6 jours.
11 sujets de 1 à 11 (sur un total de 11)
  • Auteur
    Articles
  • #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

    #107205
    Daniel Blanc
    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

    #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

    #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….

    #107280
    Daniel Blanc
    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.
    #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

    #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é.

    #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.

    #107629
    keveen.vigie
    Participant

    @Daniel

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

    merci beaucoup

    #107919
    Daniel Blanc
    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.
    #107929
    keveen.vigie
    Participant

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

11 sujets de 1 à 11 (sur un total de 11)
  • Vous devez être connecté pour répondre à ce sujet.