Forum Discussion
Mederic
Post Patron
2 years agoTable 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
dufoq3
Community Champion
2 years agoHi Mederic,
- data does not make sense
- description is too poor
- you are missing columns in source data like, Item Text, Cost Centre etc.
- Mederic2 years ago
Post Patron
Hello dufoq3
Thank you for your reply and sorry for the explanations. I had put some explanations in my Excel workbook but didn't think to put them in Power BI.
I will try to post a new Power BI file with all the explanations.
About the cost centre, it's the same and it will be opposite the positive amounts. I should have created this Text parameter in Power BI
For the texts, it's the same text according to the positive or negative amounts and adding the invoice number, PO number and the corresponding GL code.
Thanks in advance
Best regards