Table to Accounting entries

[Mederic]

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 example file with the data

-Sample PowerBI ;

https://we.tl/t-hgnrP6clG7 

-Explanations in pdf

https://we.tl/t-RtseJQllU3 

I am very grateful for your answers in advance

Thank you

Best regards

[Mederic]

A little help please for my last point ?

Thank you in advance

Best regards

[dufoq3]

Hi, maybe tomorrow.

[Mederic]

Thanks @dufoq3 ,

No problem

Best regards

[dufoq3]

Done.

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)),
    MergedQueriesVAT = Table.NestedJoin(FilteredRows, {"Supplier no."}, VAT, {"Supplier no."}, "VAT", JoinKind.LeftOuter),
    ExpandedVATCode = Table.ExpandTableColumn(MergedQueriesVAT, "VAT", {"VAT Code"}, {"VAT Code"}),
    Ad_AmountHelper = Table.AddColumn(ExpandedVATCode, "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.", "VAT Code"}, {{"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({[VAT Code]}, 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

[Mederic]

Thank you very much @dufoq3, 
I'm testing this tonight, but given your level it can only work.

Thank you for everything.

Best regardd

[dufoq3]

You're welcome Mederic. Let me know if it works as you wish or not.

[Mederic]

@dufoq3 

It's perfect, thank you for this solution in style 😊,
Best regards

[Mederic]

Hello everyone,

Hello @dufoq3 ,

The code works perfectly but there are 2 VAT codes depending on the supplier
How can I complete the M code to add this condition ?

Do you also know of any training courses/tutorials, paid or free, to progress in Power Query, preferably at an advanced level ?

Power BI File :

https://we.tl/t-OK2CyWHYvo

Pdf file for explanations :

https://we.tl/t-07a6ns7v3r

Thank in advance

Best regards

[Mederic]

@dufoq3 ,

It's impressive, it works perfectly in just a few steps,
The code is very well written and structured

Many thanks and have a great weekend

Best regards

[dufoq3]

Enjoy 😉

[Mederic]

Thank you very much @dufoq3 ,

I'm very grateful,

I'll take a look and get back to you tomorrow at the latest,
Good evening
Best regards

[dufoq3]

You're welcome @Mederic, let me know if it works as you wish. 😉 Have a nice day.

[Mederic]

Hello,

Here are some other explanations that I hope will be clearer :

Power BI file :

https://we.tl/t-gLD8Pm2meU 

Explanations .pdf

https://we.tl/t-uYRjZ0GMt4

Thanks in advance

Best regards

[dufoq3]

1. What about rows without any invoice number. Shall we ignore them?
2. could you double check supplier 4010100? In your output you have this

but in Data there are 2 different invoices, you've considered only last row (second invoice). Why?

[Mederic]

@dufoq3 ,

1. That's right, Lines without an invoice number should be ignored until the invoice has been received.
When the invoice arrives, I should add it to Data then automatically to the Output table

2. Sorry, I made a mistake, the value in "Output" should be 4010100 and not 4010070 on the line at -330.72

---

but in Data there are 2 different invoices, you've considered only last row (second invoice). Why?

---

This error is also related to the one above

Thanks

Best regards

[dufoq3 · Accepted solution]

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

[dufoq3]

Hi @Mederic, 

• data does not make sense
• description is too poor
• you are missing columns in source data like, Item Text, Cost Centre etc.

[Mederic]

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