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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
tackytechtom
Super User
Super User

Hi @Anonymous ,

 

Here a possible solution:

tomfox_2-1654116863300.png

 

 

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/

 



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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

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

tackytechtom
Super User
Super User

Hi @Anonymous ,

 

Here a possible solution:

tomfox_2-1654116863300.png

 

 

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/

 



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Anonymous
Not applicable

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

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➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors