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

 

[alex78]

With that correct your solution doesn't produce an error, but still trying to understand your solution- do you have time to explain?
1. Demote headers (not sure why exactly)
2. Take the last item in a list from becomes Column 1? (How does it know that'll be a date?)

Hi @kcmuppet,

1. Demote headers in order to group table with "Ingredient" using very helpful fourth/fifth arguments (See Table.Group - PowerQuery M)
2. DateTime.FromText function converts a text into datetime if text matches specified format (See DateTime.FromText - PowerQuery M)

Hope this help.

Regards,

 

[AlienSx]

let 
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pVNNk9sgDP0rjGd6qjfBYOw0x2Y6nR566Ncp3YMStDFdG3uANON/X4EddzftLQcQaJ7Ee5LY77NP9uRQG7Qhy7MvZ7DBhJGOP6yJnh24w/r899w3o3YQMHvM99k3sPpijk3OPKJGzQ4OQefs2KDW4KJFjxRZrAraT7T4StR1sjKl+ArGG+tzpp1BHaELsBKTSbiPDgYkGJFFmzLKBVioCSkSUqg1F2vBhWKi2MoyguuSq+KdKDldvvcBWrIzrRhyTw3eG388mxAVmBP6YH5jzi4mNKyH6B1aMPa1rqpMtk7xu1SjnO3mmoVxoNvPM+eilvwNa9F79gRh8lCgWhJxvol2Ul0tqrnaCnFVXdZS3Ki+vnyP6l1jIDXdM9/DM7XeWNaZ9pm9ZcEPljW9xXGWLUXJupb9AkcXoabtlnS9VfJKWlVyc0N6jrirU9NwTqMaU9LyrTmmAd2sIi950xPosDugi+kqPtf9QVLhPzxwtdT+CvdDfAEOLZ07YnnuYuPI3z/RfnndO6lezOxnGHtr6SvELIF+FThi6jCAadOcvggU5f+aXsitWEa9rv4ZdVnHpx7/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    group = Table.Group(
        Source, 
        "Column1", 
        {
            {"date_time", (x) => Table.Last(x)[Column1]},
            {"x", (x) => Table.PromoteHeaders(Table.RemoveLastN(x, 1))}
         },
         GroupKind.Local, 
         (s, c) => Number.From(c = "Ingredient")
    )[[date_time], [x]], 
    split = Table.SplitColumn(
        group, 
        "date_time", 
        (x) => ((dt) => {dt, Date.From(dt), Time.From(dt)})(DateTime.From(x, "fr")), 
        {"Date time", "Date", "Time"}
    ), 
    z = Table.ExpandTableColumn(split, "x", Record.ToList(Source{0}))
in 
    z

 

[kcmuppet]

@AlienSx , thanks for your solution. Would you mind explaining how it works?

It seems to be first grouping by the date_time it finds in column1, but I don't understand why Table.Last(x)[Column1]? Then after that also I'm struggling to follow

 

[AlienSx]

It seems to be first grouping by the date_time it finds in column1,

no, it starts new group when it finds "Ingredient"

but I don't understand why Table.Last(x)[Column1]?

it's just one of Table.Group aggregations - take last row of the table (as record) and the value of it's "Column1" field.

Then after that also I'm struggling to follow

after that we simply split date_time column into 3 columns: datetime, date and time. And Table.Expand table column in the end.