Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi Expert,
I have a requirement to create two additional Columns Capital and Interest from below two conditions
There are two types of Transactions
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
Solved! Go to 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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
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.
@tackytechtom It worked when I pasted in Blank Query, I managed the get the output using your logic. Thanks a Lot appreciate your solutions.
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.
@AlexisOlson It worked, I used the Merged option instead of calculated columns I have big transaction data. Thanks a Lot appreciate your solutions.
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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
@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
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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
12 | |
11 | |
11 | |
8 |
User | Count |
---|---|
43 | |
25 | |
15 | |
14 | |
12 |