Power Query to converting data to a proper table from a nested table

[kcmuppet]

I'm having trouble getting data into a proper tabular format using power query because the source has a variable number of rows that need to be transformed into a singe row

What's the best route to get data from this format...:

Ingredient	Quantity	Unit	Carb/unit	Carbohydrate
Sandwich, seeded bread, cheddar cheese	1.1	g	0.277	0.3
Raisins, dried	1	g	0.62	0.6
Grapes, green	1.3	g	0.152	0.2
25/02/2025 21:34	1740519240	Total		1.1
Ingredient	Quantity	Unit	Carb/unit	Carbohydrate
Biscuits, digestive, with oats, plain	1	g	0.664	0.7
Cheese, Cheddar type, '30% less fat'	5	g	0.008	0
26/02/2025 05:22	1740547320	Total		0.7
Ingredient	Quantity	Unit	Carb/unit	Carbohydrate
Chia seeds soaked in milk + tspn honey	1	324 ml jar	25	25
26/02/2025 07:53	1740556380	Total		25
Ingredient	Quantity	Unit	Carb/unit	Carbohydrate
Bread, seeded	2	slice	18.5	37
Cheese, Camembert	60	g	-3.00E-05	0
Cheese, spreadable, medium fat, soft, w	5	g	0.035	0.2
Mayonnaise, standard, retail	1.5	g	0.024	0

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pVNNk9sgDP0rjGd6qjfBYOw0x2Y6nR566Ncp3YMStDFdG3sAN+N/X4EddzfXPYBA8yTek8TxmP0Aq6/m3OTMI2rU7OQQdM7ODWoNLlr0mOVZsSlov9DiG1HXycrsMT9m38F4Y33OtDOoI3QFVmI2CffZwYAEuzhEmzLKFVioGSkSUqgtF1vBhWKi2MsyguuSq+KDKDldfvYBWrILrRjyxVJabdAGcn4bwQYTJjr+siZ6DuBO2/H/uW8m7SBgiv1o/Hk0ISowF/TB/MWcXU1oWA/RO7Rg7GtdVZlsneIPqUY5Oyw1C9NAt98j56KW/B1r0Xv2BGH2UKBaE3G+i3ZWXa2qudoLcVNd1lLcqb69/BbVh8ZAarpnvodnar2xrDPtM3vPgh8sa3qL0yJbipJ1LfsDji5Czds96Xqv5I20quTujvQS8aZOzcM5j2pMScu35pwGdLeJvORdT6DD7oQupqv4UvcHSYX/9MDVWvsb3A/xBTi1dO6I5djFxpG/f6L9+rp3Ur2Y2a8w9dbSV4hZAv0qcMTUYQDTpjl9ESjK+eHHfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ingredient = _t, Quantity = _t, Unit = _t, #"Carb/unit" = _t, Carbohydrate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ingredient", type text}, {"Quantity", type text}, {"Unit", type text}, {"Carb/unit", type text}, {"Carbohydrate", type text}})
in
    #"Changed Type"

Into a 'proper' table this?:

Date time	Day	Time	Ingredient	Quantity	Unit	Carb/unit	Carbohydrate
25/02/2025 21:34	25/02/2025	21:34	Sandwich, seeded bread, cheddar cheese	1.1	g	0.277	0.3
25/02/2025 21:34	25/02/2025	21:34	Raisins, dried	1	g	0.62	0.6
25/02/2025 21:34	25/02/2025	21:34	Grapes, green	1.3	g	0.152	0.2
26/02/2025 05:22	26/02/2025	5:22	Biscuits, digestive, with oats, plain	1	g	0.664	0.7
26/02/2025 05:22	26/02/2025	5:22	Cheese, Cheddar type, '30% less fat'	5	g	0.008	0.0
26/02/2025 07:53	26/02/2025	7:53	Chia seeds soaked in milk + tspn honey	1	324 ml jar	25	25
26/02/2025 13:24	26/02/2025	13:24	Bread, seeded	2	325 ml jar	25	25
26/02/2025 13:24	26/02/2025	13:24	Cheese, Camembert	3	326 ml jar	25	25
26/02/2025 13:24	26/02/2025	13:24	Cheese, spreadable, medium fat, soft, w	4	327 ml jar	25	25
26/02/2025 13:24	26/02/2025	13:24	Mayonnaise, standard, retail	5	328 ml jar	25	25

 

[Kerryx]

Book1
	A	B	C	D	E	F	G	H
1	Date Time	Day	Time	Ingredient	Quantity	Unit	Carb/unit	Carbohydrate
2	25/02/2025 21:34	25/02/2025	21:34:00	Sandwich, seeded bread, cheddar cheese	1.1	g	0.277	0.3
3	25/02/2025 21:34	25/02/2025	21:34:00	Raisins, dried	1	g	0.62	0.6
4	25/02/2025 21:34	25/02/2025	21:34:00	Grapes, green	1.3	g	0.152	0.2
5	26/02/2025 05:22	26/02/2025	05:22:00	Biscuits, digestive, with oats, plain	1	g	0.664	0.7
6	26/02/2025 05:22	26/02/2025	05:22:00	Cheese, Cheddar type, '30% less fat'	5	g	0.008	0
7	26/02/2025 07:53	26/02/2025	07:53:00	Chia seeds soaked in milk + tspn honey	1	324 ml jar	25	25
Table1

Probably a bit longwinded but here it is

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ingredient", type any}, {"Quantity", type any}, {"Unit", type text}, {"Carb/unit", type any}, {"Carbohydrate", type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Quantity] <> "Quantity")),
    #"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows", "Ingredient", "Ingredient - Copy"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Ingredient - Copy", type datetime}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type1", {{"Ingredient - Copy", null}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Replaced Errors",{"Ingredient - Copy", "Ingredient", "Quantity", "Unit", "Carb/unit", "Carbohydrate"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "Custom", each if[#"Ingredient - Copy"]<>null then 1 else 0),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom",{"Custom", "Ingredient - Copy", "Ingredient", "Quantity", "Unit", "Carb/unit", "Carbohydrate"}),
    #"Filled Up" = Table.FillUp(#"Reordered Columns1",{"Ingredient - Copy"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Up", each ([Custom] = 0) and ([#"Ingredient - Copy"] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Ingredient - Copy", "Date Time"}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns", "Date Time", "Date Time - Copy"),
    #"Reordered Columns2" = Table.ReorderColumns(#"Duplicated Column1",{"Date Time", "Date Time - Copy", "Ingredient", "Quantity", "Unit", "Carb/unit", "Carbohydrate"}),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Reordered Columns2", {{"Date Time - Copy", type text}}, "en-IE"), "Date Time - Copy", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Date Time - Copy.1", "Date Time - Copy.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date Time - Copy.1", type date}, {"Date Time - Copy.2", type time}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Date Time - Copy.1", "Day"}, {"Date Time - Copy.2", "Time"}})
in
    #"Renamed Columns1"

 

[kcmuppet]

Thanks @Kerryx

So following it through, it looks like you:

1. first filter out the interim heading rows,
2. then create a column with only the date times in it
3. then fill them upwards into the other rows

I'm not sure what custom column is for that flags the dates with 1s. ?

It seems to we've lost some of the original data (rows from Bread, seeded to Mayonnaise) at Table.SelectRows(#"Filled Up", each ([Custom] = 0) and ([#"Ingredient - Copy"] <> null)) leaving us only with:

Date Time	Day	Time	Ingredient	Quantity	Unit	Carb/unit	Carbohydrate
25/02/2025 21:34	25/02/2025	21:34:00	Sandwich, seeded bread, cheddar cheese	1.1	g	0.277	0.3
25/02/2025 21:34	25/02/2025	21:34:00	Raisins, dried	1	g	0.62	0.6
25/02/2025 21:34	25/02/2025	21:34:00	Grapes, green	1.3	g	0.152	0.2
26/02/2025 05:22	26/02/2025	05:22:00	Biscuits, digestive, with oats, plain	1	g	0.664	0.7
26/02/2025 05:22	26/02/2025	05:22:00	Cheese, Cheddar type, '30% less fat'	5	g	0.008	0
26/02/2025 07:53	26/02/2025	07:53:00	Chia seeds soaked in milk + tspn honey	1	324 ml jar	25	25

 

[alansidman]

Not sure if this is any faster or more efficient than Kerryx

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Quantity] <> "Quantity")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "DateTime", each if [Unit]="Total" then [Ingredient] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"DateTime"}),
    #"Filled Down" = Table.FillDown(#"Filled Up",{"DateTime"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Filled Down",{{"DateTime", type datetime}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "DateTime", "DateTime - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Duplicated Column", {{"DateTime - Copy", type text}}, "en-US"), "DateTime - Copy", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"DateTime - Copy.1", "DateTime - Copy.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"DateTime - Copy.1", "Date"}, {"DateTime - Copy.2", "Time"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Time", type time}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([Unit] <> "Total")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"DateTime", "Date", "Time", "Ingredient", "Quantity", "Unit", "Carb/unit", "Carbohydrate"})
in
    #"Reordered Columns"

 

[kcmuppet]

Thanks @alansidman

I get an error at #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"DateTime - Copy.1", "Date"}, {"DateTime - Copy.2", "Time"}}),

DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
2/25/2025

I'm thinking it might be because of a difference of date formats in our respective Excel configuration (I'm using international dd mm yyyy).

The step before = Table.SplitColumn(Table.TransformColumnTypes(#"Duplicated Column", {{"DateTime - Copy", type text}}, "en-US"), "DateTime - Copy", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, false), {"DateTime - Copy.1", "DateTime - Copy.2"}) produces a column with m/dd/yyyy and only a single digit for the month.

I got it work by splitting by the number of characters instead of delimitered with a space (using the menus), which produced this code: = Table.SplitColumn(Table.TransformColumnTypes(#"Duplicated Column", {{"DateTime - Copy", type text}}, "en-GB"), "DateTime - Copy", Splitter.SplitTextByPositions({0, 10}, false), {"DateTime - Copy.1", "DateTime - Copy.2"})

Thanks again

 

[alex78]

Hi @kcmuppet,
Is the input/output data correct? i.e: Input Last row with 26/02/2025 13:24 date is missing and incorrect Quantity-Unit-Carb/unit-Carbohydrate output values from last group , right?
if so, try

= Table.Combine(Table.Group(Table.DemoteHeaders(Source), "Column1", {"x", each 
    [
    a = DateTime.FromText(List.Last([Column1]), [Format = "dd/MM/yyyy HH:mm"]), 
    b = Table.RemoveLastN(Table.Skip(_)),
    c = Table.FromColumns(List.Zip(List.Split(List.Repeat({a, Date.From(a), Time.From(a)}, Table.RowCount(b)),3)) & Table.ToColumns(b), 
        {"Date time", "Day", "Time"} & Table.ColumnNames(Source))
    ] [c]}, 0, (x,y)=> Byte.From(y = "Ingredient"))[x])

Regards