Forum Discussion
Mederic
2 years agoPost Patron
Table to Accounting entries
Hello everyone, I've got a big project involving major transformations from a "Data" table to a new "Output" table. I've prepared a pdf file with all the explanations I've also put together an e...
- 2 years ago
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)
- You can reorder columns, but it is not necessary.
- 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