cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Create two additional column using Power Query

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 : 

IdDescriptionPostingDateValueDateDebitCreditExpected Column (Capital)Expected Column (Interest)
6552Payment01/03/202226/02/2022NULL43.8234.59.32
6552Interest Credit Memo01/03/202226/02/202209.32  
6552Capital Credit Memo01/03/202226/02/2022034.5  
6552Return01/03/202226/02/202243.82NULL34.59.32
6552Interest Debit Memo01/03/202226/02/20229.320  
6552Capital Debit Memo01/03/202226/02/202234.50  

 

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

3 ACCEPTED SOLUTIONS
tomfox
Super User
Super User

Hi @Anonymous ,

 

Here a possible solution:

tomfox_2-1654116863300.png

 

 

Here the M code that you can paste into advanced editor:

tomfox_1-1654116500431.png

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/

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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.

 

tomfox_0-1654199402500.png

 


Let me know if I can help you more 🙂

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

AlexisOlson
Super User
Super User

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.

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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

AlexisOlson
Super User
Super User

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

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

tomfox
Super User
Super User

Hi @Anonymous ,

 

Here a possible solution:

tomfox_2-1654116863300.png

 

 

Here the M code that you can paste into advanced editor:

tomfox_1-1654116500431.png

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/

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@tomfox 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.

dhan2609_0-1654125835068.png

 


Many Thanks

Dhan

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.

 

tomfox_0-1654199402500.png

 


Let me know if I can help you more 🙂

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Kudoed Authors