Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello guys,
How can I convert the rows of a particular transaction to specific columns in Power Query?
See below:
My data
Transaction Date | Transaction details | Credit | Debit |
02/10/2019 | 1 | 157983.79 | |
2 | |||
3 | |||
08/10/2019 | 4 | 226529.07 | |
5 | |||
6 | |||
23/10/2019 | 16 | 2306805.07 | |
17 | |||
18 | |||
24/10/2019 | 19 | 2000000 | |
20 | |||
21 | |||
24/10/2019 | 22 | 580 | |
23 | |||
29/10/2019 | 25 | 155361.21 | |
26 | |||
27 | |||
31/10/2019 | 28 | 35 | |
29 | |||
01/11/2019 | 30 | 2317497.1 | |
31 | |||
32 |
The expected outcome:
Transaction Date | Transaction details 1 | Transaction details 2 | Transaction details 3 | Type | Amount |
02/10/2019 | 1 | 2 | 3 | Credit | 157983.8 |
08/10/2019 | 4 | 5 | 6 | Credit | 226529.1 |
23/10/2019 | 16 | 17 | 18 | Credit | 2306805 |
24/10/2019 | 19 | 20 | 21 | Debit | 2000000 |
24/10/2019 | 22 | 23 | Debit | 580 | |
29/10/2019 | 25 | 26 | 27 | Credit | 155361.2 |
31/10/2019 | 28 | 29 | Debit | 35 | |
01/11/2019 | 30 | 31 | 32 | Credit | 2317497 |
Is it possible?
Thank you
Solved! Go to Solution.
The query below should work for you.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dVFbDsUQFNyL70bPw8FZi3T/22hpq+MmVyJMzMPQWpCdaRdiD1vgPq141Vg6DsfWwrVK339QAVaQp06VbOKRCvBtlWeAohjfT0QpV7LVgctqwRU9Enr4fSI0xmxAq4HwPwN5ylr9xFhYHMk2Xsw0c7wtX0n+ycMCymjxVFGbXAfuReWXqjQuwyV5iZimvKbp/LDjBA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Transaction Date" = _t, #"Transaction details" = _t, Credit = _t, Debit = _t]),
#"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Transaction Date", "Transaction details", "Credit", "Debit"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Credit", type number}, {"Debit", type number}, {"Transaction details", Int64.Type}}),
AddSum = Table.AddColumn(#"Changed Type", "Sum", each List.Sum({[Debit],-[Credit]})),
AddTranStart = Table.AddColumn(AddSum, "TransStart", each if [Transaction Date] <> null then [Transaction details] else null),
#"Filled Down" = Table.FillDown(AddTranStart,{"Transaction Date", "TransStart"}),
#"Grouped Rows" = Table.Group(#"Filled Down", {"Transaction Date", "TransStart"}, {{"Sum", each List.Sum([Sum]), type number},
{"TranDetails", each [Transaction details], type list}, {"Count", Table.RowCount, type number}}),
BreakList = List.Accumulate(List.Buffer({1..3}) ,#"Grouped Rows", (s,c) => Table.AddColumn(s, "Transaction details " & Text.From(c),each try [TranDetails]{c-1} otherwise null)),
AddType = Table.AddColumn(BreakList, "Type", each if [Sum] < 0 then "Credit" else "Debit"),
AddAmount = Table.AddColumn(AddType, "Amount", each Number.Abs([Sum])),
#"Removed Columns" = Table.RemoveColumns(AddAmount,{"TransStart", "Sum", "TranDetails", "Count"})
in
#"Removed Columns"
The query below should work for you.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dVFbDsUQFNyL70bPw8FZi3T/22hpq+MmVyJMzMPQWpCdaRdiD1vgPq141Vg6DsfWwrVK339QAVaQp06VbOKRCvBtlWeAohjfT0QpV7LVgctqwRU9Enr4fSI0xmxAq4HwPwN5ylr9xFhYHMk2Xsw0c7wtX0n+ycMCymjxVFGbXAfuReWXqjQuwyV5iZimvKbp/LDjBA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Transaction Date" = _t, #"Transaction details" = _t, Credit = _t, Debit = _t]),
#"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Transaction Date", "Transaction details", "Credit", "Debit"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Credit", type number}, {"Debit", type number}, {"Transaction details", Int64.Type}}),
AddSum = Table.AddColumn(#"Changed Type", "Sum", each List.Sum({[Debit],-[Credit]})),
AddTranStart = Table.AddColumn(AddSum, "TransStart", each if [Transaction Date] <> null then [Transaction details] else null),
#"Filled Down" = Table.FillDown(AddTranStart,{"Transaction Date", "TransStart"}),
#"Grouped Rows" = Table.Group(#"Filled Down", {"Transaction Date", "TransStart"}, {{"Sum", each List.Sum([Sum]), type number},
{"TranDetails", each [Transaction details], type list}, {"Count", Table.RowCount, type number}}),
BreakList = List.Accumulate(List.Buffer({1..3}) ,#"Grouped Rows", (s,c) => Table.AddColumn(s, "Transaction details " & Text.From(c),each try [TranDetails]{c-1} otherwise null)),
AddType = Table.AddColumn(BreakList, "Type", each if [Sum] < 0 then "Credit" else "Debit"),
AddAmount = Table.AddColumn(AddType, "Amount", each Number.Abs([Sum])),
#"Removed Columns" = Table.RemoveColumns(AddAmount,{"TransStart", "Sum", "TranDetails", "Count"})
in
#"Removed Columns"
Hello @Anonymous,
Amazing query. Can you please explain how does it work the below part?
#"Grouped Rows" = Table.Group(#"Filled Down", {"Transaction Date", "TransStart"}, {{"Sum", each List.Sum([Sum]), type number}, {"TranDetails", each [Transaction details], type list}, {"Count", Table.RowCount, type number}}), BreakList = List.Accumulate(List.Buffer({1..3}) ,#"Grouped Rows", (s,c) => Table.AddColumn(s, "Transaction details " & Text.From(c),each try [TranDetails]{c-1} otherwise null))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.