Create two additional column using Power Query

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/

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/

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.