Power Query to clean up statement data from multiple to single rows

[kcmuppet]

Hi,

In the example below I have a text description field that is split/spilled over 3 rows, and also a 4 columns of numbers that need to be on the same row.

What's the best way to transform the table so that the rows are merged? Should it be some sort of next unpivot?

Tax Calcs to 5 Apr2024.xlsx
	A	B	C	D	E	F	G
1	From this:
2	Date of Sale	Sale Quantity	Stock, Matching Details	Proceeds	Cost	Net Loss	Net Gain
3	14/11/2023	35	ADVANCED MICRO DEVICES INC
4			COM STK USD0.01	3341.21
5			08 Pool of 35		2508.59		832.62
6	14/11/2023	134	APPLE INC
7			COM USD0.00001	19997.59
8			08 Pool of 134		2423.16		17574.43
9	21/02/2024	2500	ATOME PLC
10			ORD GBP0.002 (AIM)	1252.55
11			08 Pool of 2,500		2000	747.45
12	21/07/2023	59	BEYOND MEAT
13			USD0.0001	679
14			08 Pool of 59		5044.67	4365.67
15
16
17	To this:
18	Date of Sale	Sale Quantity	Stock, Matching Details	Proceeds	Cost	Net Loss	Net Gain
19	14/11/2023	35	ADVANCED MICRO DEVICES INC COM STK USD0.01 08 Pool of 35	3341.21	2508.59		832.62
20	14/11/2023	134	APPLE INC COM USD0.00001 08 Pool of 134	19997.59	2423.16		17574.43
21	21/02/2024	2500	ATOME PLC ORD GBP0.002 (AIM) 08 Pool of 2,500	1252.55	2000	747.45
22	21/07/2023	59	BEYOND MEAT USD0.0001 08 Pool of 59	679	5044.67	4365.67
Table008 (Page 16) (2)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZFNa4QwEIb/yuCpBclmJhOjR1dDka4frHahiNeeCv7/WzOmthQqhiG8QzIzT97Mc4J8QbyQJpOkibFhK+tH2VW+hrap7j3U/tFUfoSmq8Lhbyzp/C2rvoVxeoW3sdZKo/QxjIrwv8s6h2FdP2H9gG1aCLI6V7aISW5IZbQV/EFDw8I2DDd/ghIxwpL5WBSF+2l+iBK7CwuTUZjFBJ11rNhsNYQXTULDEVkLztS3HobbEU5/r+HlOggOwVPZtM/SlSwpa0+IKI0jZJjelGOn2O4FguN2c7b3Xf1734Vf8+V0gLM7I8Zk7syT3TSrmVXmgmKT2aiSZfkC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date of Sale" = _t, #"Sale Quantity" = _t, #"Stock, Matching Details" = _t, Proceeds = _t, Cost = _t, #"Net Loss" = _t, #"Net Gain" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date of Sale", type date}, {"Sale Quantity", Int64.Type}, {"Stock, Matching Details", type text}, {"Proceeds", type number}, {"Cost", type number}, {"Net Loss", type number}, {"Net Gain", type number}})
in
    #"Changed Type"

 

[RoryA]

Here's one way:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZFNa4QwEIb/yuCpBclmJhOjR1dDka4frHahiNeeCv7/WzOmthQqhiG8QzIzT97Mc4J8QbyQJpOkibFhK+tH2VW+hrap7j3U/tFUfoSmq8Lhbyzp/C2rvoVxeoW3sdZKo/QxjIrwv8s6h2FdP2H9gG1aCLI6V7aISW5IZbQV/EFDw8I2DDd/ghIxwpL5WBSF+2l+iBK7CwuTUZjFBJ11rNhsNYQXTULDEVkLztS3HobbEU5/r+HlOggOwVPZtM/SlSwpa0+IKI0jZJjelGOn2O4FguN2c7b3Xf1734Vf8+V0gLM7I8Zk7syT3TSrmVXmgmKT2aiSZfkC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date of Sale" = _t, #"Sale Quantity" = _t, #"Stock, Matching Details" = _t, Proceeds = _t, Cost = _t, #"Net Loss" = _t, #"Net Gain" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date of Sale", type date}, {"Sale Quantity", Int64.Type}, {"Stock, Matching Details", type text}, {"Proceeds", type number}, {"Cost", type number}, {"Net Loss", type number}, {"Net Gain", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Grouper", each if [Date of Sale] <> null then [Index] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Grouper"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"}),
    #"Filled Down1" = Table.FillDown(#"Removed Columns",{"Date of Sale", "Sale Quantity"}),
    #"Grouped Rows" = Table.Group(#"Filled Down1", {"Date of Sale", "Sale Quantity", "Grouper"}, {{"Stock Details", each Text.Combine([#"Stock, Matching Details"], " "), type text}, {"Proceeds", each List.Sum([Proceeds]), type nullable number}, {"Cost", each List.Sum([Cost]), type nullable number}, {"Net Loss", each List.Sum([Net Loss]), type nullable number}, {"Net Gain", each List.Sum([Net Gain]), type nullable number}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows",{"Grouper"})
in
    #"Removed Columns1"

 

[alex78]

try

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZFNa4QwEIb/yuCpBclmJhOjR1dDka4frHahiNeeCv7/WzOmthQqhiG8QzIzT97Mc4J8QbyQJpOkibFhK+tH2VW+hrap7j3U/tFUfoSmq8Lhbyzp/C2rvoVxeoW3sdZKo/QxjIrwv8s6h2FdP2H9gG1aCLI6V7aISW5IZbQV/EFDw8I2DDd/ghIxwpL5WBSF+2l+iBK7CwuTUZjFBJ11rNhsNYQXTULDEVkLztS3HobbEU5/r+HlOggOwVPZtM/SlSwpa0+IKI0jZJjelGOn2O4FguN2c7b3Xf1734Vf8+V0gLM7I8Zk7syT3TSrmVXmgmKT2aiSZfkC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date of Sale" = _t, #"Sale Quantity" = _t, #"Stock, Matching Details" = _t, Proceeds = _t, Cost = _t, #"Net Loss" = _t, #"Net Gain" = _t]),
_1 = (x)=> Table.FromRows({List.Transform(Table.ToColumns(x), each Text.Combine(_,""))}, Value.Type(Source)),
_2 = Table.Combine(Table.Group(Source, "Date of Sale", {"x", _1}, 0, (x,y)=> Byte.From(x <> "" and y <> ""))[x])
in _2

Regards,

 

[kcmuppet]

Thank you @RoryA !

The Table.FillDown makes it look easy - I wish I'd thought of that!.

 

[kcmuppet]

_1 = (x)=> Table.FromRows({List.Transform(Table.ToColumns(x), each Text.Combine(_,""))}, Value.Type(Source)),
_2 = Table.Combine(Table.Group(Source, "Date of Sale", {"x", _1}, 0, (x,y)=> Byte.From(x <> "" and y <> ""))[x])
in _2

Thanks for this solution too @alex78 . Do you have time to explain how it works?

 

[AlienSx]

let
    source = your_table,
    data = Table.Split(source, 3),
    cols = List.Buffer(Table.ColumnNames(source)),
    tx = [Date of Sale = List.First, #"Stock, Matching Details" = (x) => Text.Combine(x, " ")],
    fx = (tbl) => List.Transform(cols, (x) => Record.FieldOrDefault(tx, x, List.Sum)(Table.Column(tbl, x))), 
    result = Table.FromList(List.Transform(data, fx), (x) => x, Value.Type(source))
in
    result

 

[kcmuppet]

let
    source = your_table,
    data = Table.Split(source, 3),
    cols = List.Buffer(Table.ColumnNames(source)),
    tx = [Date of Sale = List.First, #"Stock, Matching Details" = (x) => Text.Combine(x, " ")],
    fx = (tbl) => List.Transform(cols, (x) => Record.FieldOrDefault(tx, x, List.Sum)(Table.Column(tbl, x))),
    result = Table.FromList(List.Transform(data, fx), (x) => x, Value.Type(source))
in
    result

Thanks for your solution too @AlienSx.

I think it's splitting the table at every 3 rows then creating a list of column headers. Then after that, I can't work it out. Do you have time to explain?

 

[AlienSx]

Thanks for your solution too @AlienSx.

I think it's splitting the table at every 3 rows then creating a list of column headers. Then after that, I can't work it out. Do you have time to explain?

...after that I transform a list of tables into the list of rows (each table is transformed into single row). To do that we need to get single value out of each column of our tables. For column with date we just take first item. For text column we combine text. For all other columns we just sum values in the column.
tx: record with 2 fields - date and text columns. Field values are functions - List.First and Text.Combine respectively. Default transformation will be List.Sum so we may use it for all other columns in Record.FieldOrDefault function.
fx: function that gets a table as argument and return a list of row values. It simply transforms a list of column names by calling each column by its name >> find transformation function using column name in record tx via Record.FieldOrDefault >> apply this function to the list of column values received using Table.Column function.
result: simple Table.FromList function that takes a result of List.Transform (list of rows), just do nothing and assign original table column names and types.

 

[kcmuppet]

...after that I transform...

Thank you