Create two additional column using Power Query

[Anonymous]

Hi Expert,

I have a requirement to create two additional Columns Capital and Interest from below two conditions  

There are two types of Transactions

1. Payment (43.82 : Credit Column)= Capital Credit Memo (34.5)+ Interest Credit (9.32)
2. Return (43.82 : DebitColumn) = Capital Debit Memo (34.5)+Interest Debit Memo (9.32)

Payment transactions are posted in the Credit column  and the breakdown of this payment  is split into Capital Credit Memo & Interest Credit Memo 

Similarly, Return  transactions are posted in the Debit column  and the breakdown of this payment is split into Capital Debit  Memo & Interest Credit  Memo

Note the Posting date for Payment = Capital Credit Memo + Interest Credit same and also it will same for Return

For Example : 

Id	Description	PostingDate	ValueDate	Debit	Credit	Expected Column (Capital)	Expected Column (Interest)
6552	Payment	01/03/2022	26/02/2022	NULL	43.82	34.5	9.32
6552	Interest Credit Memo	01/03/2022	26/02/2022	0	9.32
6552	Capital Credit Memo	01/03/2022	26/02/2022	0	34.5
6552	Return	01/03/2022	26/02/2022	43.82	NULL	34.5	9.32
6552	Interest Debit Memo	01/03/2022	26/02/2022	9.32	0
6552	Capital Debit Memo	01/03/2022	26/02/2022	34.5	0

Please could you advise how I can achieve this in the Power query? I tried the Conditional column unfortunately it didn't work.

Many Thanks

Dan

[tackytechtom · Accepted solution]

Hi @Anonymous ,

Here a possible solution:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjM1NVLSUQpIrMxNzSsBsgwM9Q2M9Y0MjEDCRmb6BkYwjl+ojw+QMjHWszBSitWB6/XMK0ktSi0uUXAuSk3JLFHwTc3Nx2uQARBb6hmjGOKcWJBZkphDkhnGJnqmyGYEpZaUFuXh1QZxPNQv2PzgkppEjPVg54NcgcULRBoBdj3YiFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Description = _t, PostingDate = _t, ValueDate = _t, Debit = _t, Credit = _t]),
    #"Filtered Rows Interest Credit Memo" = Table.SelectRows(Source, each ([Description] = "Interest Credit Memo")),
    #"Filtered Rows Capital Credit Memo" = Table.SelectRows(Source, each ([Description] = "Capital Credit Memo")),
    #"Filtered Rows Interest Debit Memo" = Table.SelectRows(Source, each ([Description] = "Interest Debit Memo")),
    #"Filtered Rows Capital Debit Memo" = Table.SelectRows(Source, each ([Description] = "Capital Debit Memo")),
    #"Merged Table with Interest Credit Memo" = Table.NestedJoin(Source, {"Id"}, #"Filtered Rows Interest Credit Memo", {"Id"}, "Filtered Rows Interest Credit Memo", JoinKind.LeftOuter),
    #"Merged Table with Capital Credit Memo" = Table.NestedJoin(#"Merged Table with Interest Credit Memo", {"Id"}, #"Filtered Rows Capital Credit Memo", {"Id"}, "Filtered Rows Capital Credit Memo", JoinKind.LeftOuter),
    #"Merged Table with Interest Debit Memo" = Table.NestedJoin(#"Merged Table with Capital Credit Memo", {"Id"}, #"Filtered Rows Interest Debit Memo", {"Id"}, "Filtered Rows Interest Debit Memo", JoinKind.LeftOuter),
    #"Merged Table with Capital Debit Memo" = Table.NestedJoin(#"Merged Table with Interest Debit Memo", {"Id"}, #"Filtered Rows Capital Debit Memo", {"Id"}, "Filtered Rows Capital Debit Memo", JoinKind.LeftOuter),
    #"Expanded Filtered Rows Interest Credit Memo" = Table.ExpandTableColumn(#"Merged Table with Capital Debit Memo", "Filtered Rows Interest Credit Memo", {"Credit"}, {"Filtered Rows Interest Credit Memo.Credit"}),
    #"Expanded Filtered Rows Capital Credit Memo" = Table.ExpandTableColumn(#"Expanded Filtered Rows Interest Credit Memo", "Filtered Rows Capital Credit Memo", {"Credit"}, {"Filtered Rows Capital Credit Memo.Credit"}),
    #"Expanded Filtered Rows Interest Debit Memo" = Table.ExpandTableColumn(#"Expanded Filtered Rows Capital Credit Memo", "Filtered Rows Interest Debit Memo", {"Debit"}, {"Filtered Rows Interest Debit Memo.Debit"}),
    #"Expanded Filtered Rows Capital Debit Memo" = Table.ExpandTableColumn(#"Expanded Filtered Rows Interest Debit Memo", "Filtered Rows Capital Debit Memo", {"Debit"}, {"Filtered Rows Capital Debit Memo.Debit"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Filtered Rows Capital Debit Memo",{{"Id", Int64.Type}, {"Description", type text}, {"PostingDate", type date}, {"ValueDate", type date}, {"Debit", type text}, {"Credit", type text}, {"Filtered Rows Interest Credit Memo.Credit", type number}, {"Filtered Rows Capital Credit Memo.Credit", type number}, {"Filtered Rows Interest Debit Memo.Debit", type number}, {"Filtered Rows Capital Debit Memo.Debit", type number}}),
    #"Added Capital Column" = Table.AddColumn(#"Changed Type", "Capital", each if [Description] = "Payment" then [Filtered Rows Capital Credit Memo.Credit] else if [Description] = "Return" then [Filtered Rows Capital Debit Memo.Debit] else null),
    #"Added Interest Column" = Table.AddColumn(#"Added Capital Column", "Interest", each if [Description] = "Payment" then [Filtered Rows Interest Credit Memo.Credit] else if [Description] = "Return" then [Filtered Rows Interest Debit Memo.Debit] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Interest Column",{"Filtered Rows Interest Credit Memo.Credit", "Filtered Rows Capital Credit Memo.Credit", "Filtered Rows Interest Debit Memo.Debit", "Filtered Rows Capital Debit Memo.Debit"})
in
    #"Removed Columns"

Let me know if this helps or if you have a question 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[tackytechtom · Accepted solution]

Hi @Anonymous ,

The idea was to filter the table for the specific rows and then join these rows back into the table. With that you can outsource the value of the rows per group as columns. 

I think the best way to understand the code is to create a new blank query and paste the code into there. From there I am sure you can figure out what I did and thereby apply the code onto your use case. Admittedly, it is sometimes a bit tricky to apply the M code straight to your model, / example. 

Add a blank query (under the ribbon home) and copy and paste the whole M code from above.

Let me know if I can help you more 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Anonymous]

@tackytechtom  It worked when I pasted in Blank Query, I managed the get the output using your logic. Thanks a Lot appreciate your solutions.

[AlexisOlson · Accepted solution]

Here's an alternative that only requires one merge:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjM1NVLSUQpIrMxNzSsBsgwM9Q2M9Y0MjEDCRmb6BkYwDhCZGOtZGCnF6sD1eeaVpBalFpcoOBelpmSWKPim5ubjNcQAiC31jFEMcU4syCxJzCHJDGMTPVNkM4JSS0qL8vBqgzge6A9s7ndJTSLGarDTQS7A4nwijQC7HGxELAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Description = _t, PostingDate = _t, ValueDate = _t, Debit = _t, Credit = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Description", type text}, {"PostingDate", type date}, {"ValueDate", type date}, {"Debit", type number}, {"Credit", type number}}, "en-IN"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Id", "Description", "PostingDate", "ValueDate"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> 0) and (Text.EndsWith([Description], "Memo"))),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Filtered Rows", {{"Description", each Text.BeforeDelimiter(_, " "), type text}}),
    #"Added Custom" = Table.AddColumn(#"Extracted Text Before Delimiter", "Desc", each if [Attribute] = "Credit" then "Payment" else if [Attribute] = "Debit" then "Return" else "", type text),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Description]), "Description", "Value"),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Id", "Description"}, #"Pivoted Column", {"Id", "Desc"}, "Pivoted Column", JoinKind.LeftOuter),
    #"Expanded Pivoted Column" = Table.ExpandTableColumn(#"Merged Queries", "Pivoted Column", {"Capital", "Interest"}, {"Capital", "Interest"})
in
    #"Expanded Pivoted Column"

Or you could write some fancy calculated columns and do no merges.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjM1NVLSUQpIrMxNzSsBsgwM9Q2M9Y0MjEDCRmb6BkYwDhCZGOtZGCnF6sD1eeaVpBalFpcoOBelpmSWKPim5ubjNcQAiC31jFEMcU4syCxJzCHJDGMTPVNkM4JSS0qL8vBqgzge6A9s7ndJTSLGarDTQS7A4nwijQC7HGxELAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Description = _t, PostingDate = _t, ValueDate = _t, Debit = _t, Credit = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Description", type text}, {"PostingDate", type date}, {"ValueDate", type date}, {"Debit", type number}, {"Credit", type number}}, "en-IN"),
    #"Added Capital" =
    Table.AddColumn(
        #"Changed Type", "Capital", each
            let
                T = Table.SelectRows(#"Changed Type", (r) => r[Id] = [Id]),
                Return = 
                    if [Description] = "Payment"
                        then T{[Description = "Capital Credit Memo"]}[Credit]
                    else if [Description] = "Return"
                        then T{[Description = "Capital Debit Memo"]}[Debit]
                    else null
            in 
                Return
        , type number
    ),
    #"Added Interest" =
    Table.AddColumn(
        #"Added Capital", "Interest", each
            let
                T = Table.SelectRows(#"Added Capital", (r) => r[Id] = [Id]),
                Return = 
                    if [Description] = "Payment"
                        then T{[Description = "Interest Credit Memo"]}[Credit]
                    else if [Description] = "Return"
                        then T{[Description = "Interest Debit Memo"]}[Debit]
                    else null
            in 
                Return
        , type number
    )
in
    #"Added Interest"

This method probably isn't very efficient with large data.

[Anonymous]

@AlexisOlson It worked,  I used the Merged option instead of calculated columns I have big transaction data. Thanks a Lot appreciate your solutions.

[tackytechtom · Accepted solution]

Hi @Anonymous ,

Here a possible solution:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjM1NVLSUQpIrMxNzSsBsgwM9Q2M9Y0MjEDCRmb6BkYwjl+ojw+QMjHWszBSitWB6/XMK0ktSi0uUXAuSk3JLFHwTc3Nx2uQARBb6hmjGOKcWJBZkphDkhnGJnqmyGYEpZaUFuXh1QZxPNQv2PzgkppEjPVg54NcgcULRBoBdj3YiFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Description = _t, PostingDate = _t, ValueDate = _t, Debit = _t, Credit = _t]),
    #"Filtered Rows Interest Credit Memo" = Table.SelectRows(Source, each ([Description] = "Interest Credit Memo")),
    #"Filtered Rows Capital Credit Memo" = Table.SelectRows(Source, each ([Description] = "Capital Credit Memo")),
    #"Filtered Rows Interest Debit Memo" = Table.SelectRows(Source, each ([Description] = "Interest Debit Memo")),
    #"Filtered Rows Capital Debit Memo" = Table.SelectRows(Source, each ([Description] = "Capital Debit Memo")),
    #"Merged Table with Interest Credit Memo" = Table.NestedJoin(Source, {"Id"}, #"Filtered Rows Interest Credit Memo", {"Id"}, "Filtered Rows Interest Credit Memo", JoinKind.LeftOuter),
    #"Merged Table with Capital Credit Memo" = Table.NestedJoin(#"Merged Table with Interest Credit Memo", {"Id"}, #"Filtered Rows Capital Credit Memo", {"Id"}, "Filtered Rows Capital Credit Memo", JoinKind.LeftOuter),
    #"Merged Table with Interest Debit Memo" = Table.NestedJoin(#"Merged Table with Capital Credit Memo", {"Id"}, #"Filtered Rows Interest Debit Memo", {"Id"}, "Filtered Rows Interest Debit Memo", JoinKind.LeftOuter),
    #"Merged Table with Capital Debit Memo" = Table.NestedJoin(#"Merged Table with Interest Debit Memo", {"Id"}, #"Filtered Rows Capital Debit Memo", {"Id"}, "Filtered Rows Capital Debit Memo", JoinKind.LeftOuter),
    #"Expanded Filtered Rows Interest Credit Memo" = Table.ExpandTableColumn(#"Merged Table with Capital Debit Memo", "Filtered Rows Interest Credit Memo", {"Credit"}, {"Filtered Rows Interest Credit Memo.Credit"}),
    #"Expanded Filtered Rows Capital Credit Memo" = Table.ExpandTableColumn(#"Expanded Filtered Rows Interest Credit Memo", "Filtered Rows Capital Credit Memo", {"Credit"}, {"Filtered Rows Capital Credit Memo.Credit"}),
    #"Expanded Filtered Rows Interest Debit Memo" = Table.ExpandTableColumn(#"Expanded Filtered Rows Capital Credit Memo", "Filtered Rows Interest Debit Memo", {"Debit"}, {"Filtered Rows Interest Debit Memo.Debit"}),
    #"Expanded Filtered Rows Capital Debit Memo" = Table.ExpandTableColumn(#"Expanded Filtered Rows Interest Debit Memo", "Filtered Rows Capital Debit Memo", {"Debit"}, {"Filtered Rows Capital Debit Memo.Debit"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Filtered Rows Capital Debit Memo",{{"Id", Int64.Type}, {"Description", type text}, {"PostingDate", type date}, {"ValueDate", type date}, {"Debit", type text}, {"Credit", type text}, {"Filtered Rows Interest Credit Memo.Credit", type number}, {"Filtered Rows Capital Credit Memo.Credit", type number}, {"Filtered Rows Interest Debit Memo.Debit", type number}, {"Filtered Rows Capital Debit Memo.Debit", type number}}),
    #"Added Capital Column" = Table.AddColumn(#"Changed Type", "Capital", each if [Description] = "Payment" then [Filtered Rows Capital Credit Memo.Credit] else if [Description] = "Return" then [Filtered Rows Capital Debit Memo.Debit] else null),
    #"Added Interest Column" = Table.AddColumn(#"Added Capital Column", "Interest", each if [Description] = "Payment" then [Filtered Rows Interest Credit Memo.Credit] else if [Description] = "Return" then [Filtered Rows Interest Debit Memo.Debit] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Interest Column",{"Filtered Rows Interest Credit Memo.Credit", "Filtered Rows Capital Credit Memo.Credit", "Filtered Rows Interest Debit Memo.Debit", "Filtered Rows Capital Debit Memo.Debit"})
in
    #"Removed Columns"

Let me know if this helps or if you have a question 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Anonymous]

@tackytechtom Thanks Tom, appreciate your response I pasted the give M-Code but it gave me the below error message. Please could you advise what steps you have taken in order to achieve that two-column? Did you create additional table to merge the table.

Many Thanks

Dhan

[tackytechtom · Accepted solution]

Hi @Anonymous ,

The idea was to filter the table for the specific rows and then join these rows back into the table. With that you can outsource the value of the rows per group as columns. 

I think the best way to understand the code is to create a new blank query and paste the code into there. From there I am sure you can figure out what I did and thereby apply the code onto your use case. Admittedly, it is sometimes a bit tricky to apply the M code straight to your model, / example. 

Add a blank query (under the ribbon home) and copy and paste the whole M code from above.

Let me know if I can help you more 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/