Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Mederic
Helper V
Helper V

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 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

1 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

dufoq3_0-1711823958319.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

18 REPLIES 18
Mederic
Helper V
Helper V

A little help please for my last point ?

Thank you in advance

Best regards

Hi, maybe tomorrow.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Thanks @dufoq3 ,

No problem

Best regards

Done.

 

dufoq3_0-1712303650426.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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

Thank you for everything.

Best regardd

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


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

@dufoq3 

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

Mederic
Helper V
Helper V

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
Helper V
Helper V

@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

Enjoy 😉


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Mederic
Helper V
Helper V

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

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


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Mederic
Helper V
Helper V

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

  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

dufoq3_0-1711816591697.png

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

dufoq3_1-1711816620678.png

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

@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

 

 

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

dufoq3_0-1711823958319.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

dufoq3
Super User
Super User

Hi @Mederic

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors