Table to Accounting entries

Done.

 

I've not included columns Document Text (this is the same as Invoice no.) and Currency (I don't know the currency from the source data)

 

1. You can reorder columns, but it is not necessary.
2. Add this step to Criteria table please

 

= Table.Buffer(#"Type modifié")

 

 

Result

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZVLbsIwEIavgrIOYmb8zLLi2ZZueKwQC0qzYAMoQO/Uc/RitQ1JSSiOwKhSggOK832M/3hms4gJwCiOOCCABnM1MOfbhIYD9jpEmoIAQeYnEC1gLQLi9kvlRMZjacbudGQ+250uKDP2NodsvdofstTecj6/x9xgjsmm8Z42ttlmme526Uc0j50SBSsxgpKQ1Rt9f20X2WK/2qxvNkpyI26f2zkZ9UfJ+GgkqfrICyMdK1FyYmYcp9nnamkrRHA2WSAjIVvulqtSCLlUYq+eCyl0Uoq0qpPSKlYep9/jRMRgIt7Eowfwysm8DMIFlAWvNemkBnrHavOTFrpajAstOmpJV1mUj62FeAD0lgWX/8xTwTzObuHpPFvKzu0XPHkMtEi0+wsenhSxdxspbWtP7aYm5nKsIAa4nq4kWIzpcuXr00V5NehoVoFyoe0NxDxQxSNv36kiRb5nokO+VJFI9uUm7wLoSrMrLwDDs7l2aAJrKknoe7kFBmuZ/qLv1OLeaAgZ7IayLhr3VS0JNlM6rkkQJX+auZ3xqpksGqW0lF7VjISdTtrXQ3hdD6lmWxbd0qW8W0CVgxJpVgdF6dtbLoB5pwyov/Sm9ow4/wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PO = _t, #"Supplier no." = _t, #"Supplier Name" = _t, #"ID Equipement" = _t, #"Repair Date" = _t, ColF = _t, #"Col G" = _t, #"Col H" = _t, #"Col I" = _t, #"Amount excl." = _t, Currency = _t, #"GL Code" = _t, Description = _t, #"Invoice Date" = _t, #"Invoice no." = _t, #"Corrected amount excl." = _t, Statut = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"PO", Int64.Type}, {"Supplier no.", Int64.Type}, {"Supplier Name", type text}, {"ID Equipement", type text}, {"Repair Date", type date}, {"ColF", Int64.Type}, {"Col G", Int64.Type}, {"Col H", Int64.Type}, {"Col I", Int64.Type}, {"Amount excl.", type number}, {"Currency", type text}, {"GL Code", type text}, {"Description", type text}, {"Invoice Date", type date}, {"Invoice no.", type text}, {"Corrected amount excl.", Int64.Type}, {"Statut", type text}}),
    FilteredRows = Table.SelectRows(ChangedType, each ([Invoice Date] <> null)),
    Ad_AmountHelper = Table.AddColumn(FilteredRows, "AmountHelper", each if [#"Corrected amount excl."] <> null then [#"Corrected amount excl."] else [#"Amount excl."], Currency.Type),
    GroupedRows = Table.Group(Ad_AmountHelper, {"Supplier no.", "Invoice no."}, {{"All", each Table.AddColumn(_, "GL Account", (x)=> Criteria{[GL Code = x[GL Code]]}[GL Account]) , type table}, {"Amount incl. VAT", each -List.Sum([AmountHelper])*1.2, Currency.Type}}),
    Ad_Transformed = Table.AddColumn(GroupedRows, "Transformed", each 
        [ a = Table.RowCount([All]),
          b = Table.AddIndexColumn(Table.FromColumns({
                    List.Repeat({[#"Invoice no."]}, a+1), //Invoice no.
                    {[Amount incl. VAT]} & List.Transform([All][AmountHelper], (x)=> x * 1.2), //Amount incl. VAT
                    {null} & [All][GL Account], //GL Account
                    {[#"Supplier no."]}, //Supplier no.
                    {""} & List.Repeat({"DC"}, a), //Code VAT
                    {"X"} & List.Repeat({""}, a), //Calulate tax on brut
                    {"Repair acc. to Invoice " & [#"Invoice no."]} & List.Transform(List.Zip({ List.Transform([All][PO], (x)=> "Repair " & Text.From(x)), [All][GL Code] }), (y)=> Text.Combine(y, " - ")), //Item Text
                    {""} & List.Repeat({"200T25G743"}, a), //Cost Center
                    [ x = {Date.ToText([All][Invoice Date]{0}?, "ddmmyyyy")}, y = List.Repeat(x, a+1) ][y] //Document Date
                    }, type table[#"Invoice no."=text, Amount incl. VAT=Currency.Type, GL Account=Int64.Type, #"Supplier no."=Int64.Type, Code VAT=text, Calculate tax on brut=text, Item Text=text, Cost Center=text, Document Date=text]), "Line no.", 1, 1, Int64.Type)
        ][b], type table),
    CombinedTransformed = Table.Combine(Ad_Transformed[Transformed]),
    Ad_CompanyNo = Table.AddColumn(CombinedTransformed, "Company no.", each "FR340", type text),
    Ad_DocumentType = Table.AddColumn(Ad_CompanyNo, "Document Type", each "TS", type text),
    Ad_PostingDate = Table.AddColumn(Ad_DocumentType, "Posting Date", each Date.From(DateTime.FixedLocalNow()), type date)
in
    Ad_PostingDate