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...:
Into a 'proper' table this?:
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 |
Power Query:
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 |