Rows to Columns

[Anonymous]

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

[Anonymous · Accepted 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"

[Anonymous]

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))