// Invoices with gross amounts // Pack: European invoices (semicolon CSV) (Power Query Exercises, https://powerqueryexercises.com/downloads/european-invoices-semicolon) // // Source bindings // In the Power Query Exercises simulator each name below is bound directly to a pack file. In Excel or // Power BI Desktop the first steps of this query do that binding instead: // Invoices the pack file rechnungen.csv -> File.Contents(PackFolder & "rechnungen.csv") // Set PackFolder to the folder where you extracted the pack (keep the trailing backslash). // // Validated in the Power Query Exercises simulator (engine 0.1.0) against this pack's files; it returns // 14 rows with columns Rechnungsnr, Datum, Kunde, Netto, MwSt, VAT, Gross. // It has not been run in Excel or Power BI by us. This is query text, not a ready-to-refresh workbook. // Independent practice material; not affiliated with or endorsed by Microsoft. let PackFolder = "C:\PQE\european-invoices-semicolon\", Invoices = File.Contents(PackFolder & "rechnungen.csv"), Source = Csv.Document(Invoices, [Delimiter=";", Encoding=65001, QuoteStyle=QuoteStyle.Csv]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", {{"Datum", type date}, {"Netto", type number}, {"MwSt", Int64.Type}}, "de-DE"), #"VAT" = Table.AddColumn(#"Changed Type", "VAT", each Number.Round([Netto] * [MwSt] / 100, 2), type number), #"Gross" = Table.AddColumn(#"VAT", "Gross", each [Netto] + [VAT], type number) in #"Gross"