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