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

K

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
450
Office Version
  1. 365
Platform
  1. Windows
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...:
IngredientQuantityUnitCarb/unitCarbohydrate
Sandwich, seeded bread, cheddar cheese1.1g0.2770.3
Raisins, dried1g0.620.6
Grapes, green1.3g0.1520.2
25/02/2025 21:341740519240Total1.1
IngredientQuantityUnitCarb/unitCarbohydrate
Biscuits, digestive, with oats, plain1g0.6640.7
Cheese, Cheddar type, '30% less fat'5g0.0080
26/02/2025 05:221740547320Total0.7
IngredientQuantityUnitCarb/unitCarbohydrate
Chia seeds soaked in milk + tspn honey1324 ml jar2525
26/02/2025 07:531740556380Total25
IngredientQuantityUnitCarb/unitCarbohydrate
Bread, seeded2slice18.537
Cheese, Camembert60g-3.00E-050
Cheese, spreadable, medium fat, soft, w5g0.0350.2
Mayonnaise, standard, retail1.5g0.0240


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 timeDayTimeIngredientQuantityUnitCarb/unitCarbohydrate
25/02/2025 21:3425/02/202521:34Sandwich, seeded bread, cheddar cheese1.1g0.2770.3
25/02/2025 21:3425/02/202521:34Raisins, dried1g0.620.6
25/02/2025 21:3425/02/202521:34Grapes, green1.3g0.1520.2
26/02/2025 05:2226/02/20255:22Biscuits, digestive, with oats, plain1g0.6640.7
26/02/2025 05:2226/02/20255:22Cheese, Cheddar type, '30% less fat'5g0.0080.0
26/02/2025 07:5326/02/20257:53Chia seeds soaked in milk + tspn honey1324 ml jar2525
26/02/2025 13:2426/02/202513:24Bread, seeded2325 ml jar2525
26/02/2025 13:2426/02/202513:24Cheese, Camembert3326 ml jar2525
26/02/2025 13:2426/02/202513:24Cheese, spreadable, medium fat, soft, w4327 ml jar2525
26/02/2025 13:2426/02/202513:24Mayonnaise, standard, retail5328 ml jar2525
 
Sort by date Sort by votes
Book1
ABCDEFGH
1Date TimeDayTimeIngredientQuantityUnitCarb/unitCarbohydrate
225/02/2025 21:3425/02/202521:34:00Sandwich, seeded bread, cheddar cheese1.1g0.2770.3
325/02/2025 21:3425/02/202521:34:00Raisins, dried1g0.620.6
425/02/2025 21:3425/02/202521:34:00Grapes, green1.3g0.1520.2
526/02/2025 05:2226/02/202505:22:00Biscuits, digestive, with oats, plain1g0.6640.7
626/02/2025 05:2226/02/202505:22:00Cheese, Cheddar type, '30% less fat'5g0.0080
726/02/2025 07:5326/02/202507:53:00Chia seeds soaked in milk + tspn honey1324 ml jar2525
Table1


Probably a bit longwinded but here it is
Power Query: 
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"
 
Upvote 0
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 TimeDayTimeIngredientQuantityUnitCarb/unitCarbohydrate
25/02/2025 21:3425/02/202521:34:00Sandwich, seeded bread, cheddar cheese1.1g0.2770.3
25/02/2025 21:3425/02/202521:34:00Raisins, dried1g0.620.6
25/02/2025 21:3425/02/202521:34:00Grapes, green1.3g0.1520.2
26/02/2025 05:2226/02/202505:22:00Biscuits, digestive, with oats, plain1g0.6640.7
26/02/2025 05:2226/02/202505:22:00Cheese, Cheddar type, '30% less fat'5g0.0080
26/02/2025 07:5326/02/202507:53:00Chia seeds soaked in milk + tspn honey1324 ml jar2525
 
Upvote 0
Not sure if this is any faster or more efficient than Kerryx

Power Query: 
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"
 
Upvote 0
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
 
Last edited:
Upvote 0
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


Power Query: 
= 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
 
Upvote 0
ok tweaked and removed the 1/0 column was using it to filter out the total column but duh relaised i could have filtered out on Total
Originally lost the last few rows as the other sections had a total field at bottom of section with exception of the last group, so after doing the fill up, just did a fill down to populate the date field.
Raisins, dried1g0.620.6
Grapes, green1.3g0.1520.2
25/02/20251740519240Total1.1
IngredientQuantityUnitCarb/unitCarbohydrate
Biscuits, digestive, with oats, plain1g0.6640.7
Cheese, Cheddar type, '30% less fat'5g0.0080
26/02/20251740547320Total0.7
IngredientQuantityUnitCarb/unitCarbohydrate
Chia seeds soaked in milk + tspn honey1324 ml jar2525
26/02/20251740556380Total25
IngredientQuantityUnitCarb/unitCarbohydrate
Bread, seeded2slice18.537
Cheese, Camembert60g-0.000030
Cheese, spreadable, medium fat, soft, w5g0.0350.2
Mayonnaise, standard, retail1.5g0.0240


Power Query: 
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"}),
    #"Filled Up" = Table.FillUp(#"Reordered Columns",{"Ingredient - Copy"}),
    #"Filled Down" = Table.FillDown(#"Filled Up",{"Ingredient - Copy"}),
    #"Filtered Rows2" = Table.SelectRows(#"Filled Down", each ([Unit] <> "Total")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows2",{{"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"

Book1
ABCDEFGH
1Date TimeDayTimeIngredientQuantityUnitCarb/unitCarbohydrate
225/02/2025 21:3425/02/202521:34:00Sandwich, seeded bread, cheddar cheese1.1g0.2770.3
325/02/2025 21:3425/02/202521:34:00Raisins, dried1g0.620.6
425/02/2025 21:3425/02/202521:34:00Grapes, green1.3g0.1520.2
526/02/2025 05:2226/02/202505:22:00Biscuits, digestive, with oats, plain1g0.6640.7
626/02/2025 05:2226/02/202505:22:00Cheese, Cheddar type, '30% less fat'5g0.0080
726/02/2025 07:5326/02/202507:53:00Chia seeds soaked in milk + tspn honey1324 ml jar2525
826/02/2025 07:5326/02/202507:53:00Bread, seeded2slice18.537
926/02/2025 07:5326/02/202507:53:00Cheese, Camembert60g-0.000030
1026/02/2025 07:5326/02/202507:53:00Cheese, spreadable, medium fat, soft, w5g0.0350.2
1126/02/2025 07:5326/02/202507:53:00Mayonnaise, standard, retail1.5g0.0240
Table1
 
Upvote 0
alex78 said:
try..
Click to expand...
Hi @alex78 , thanks for your reply

Just trying to understand it - 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?)
Power Query: 
a = DateTime.FromText(List.Last([Column1]), [Format = "dd/MM/yyyy HH:mm"]),


I get an error with the last 3 rows: DataFormat.Error: We couldn't parse the input provided as a DateTime value.

1741015106558.png
 
Upvote 0
@alex78 ,

I realised too late to edit my last post - you're quite right, I missed the bottom row on the input table.

In should have been like this:
IngredientQuantityUnitCarb/unitCarbohydrate
Sandwich, seeded bread, cheddar cheese1.1g0.2770.3
Raisins, dried1g0.620.6
Grapes, green1.3g0.1520.2
25/02/2025 21:341740519240Total1.1
IngredientQuantityUnitCarb/unitCarbohydrate
Biscuits, digestive, with oats, plain1g0.6640.7
Cheese, Cheddar type, '30% less fat'5g0.0080
26/02/2025 05:221740547320Total0.7
IngredientQuantityUnitCarb/unitCarbohydrate
Chia seeds soaked in milk + tspn honey1324 ml jar2525
26/02/2025 07:531740556380Total25
IngredientQuantityUnitCarb/unitCarbohydrate
Bread, seeded2slice18.537
Cheese, Camembert60g-3.00E-050
Cheese, spreadable, medium fat, soft, w5g0.0350.2
Mayonnaise, standard, retail1.5g0.0240
26/02/2025 13:241740576240Total37.2


Power Query: 
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])


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?)
Power Query: 
a = DateTime.FromText(List.Last([Column1]), [Format = "dd/MM/yyyy HH:mm"]),
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Power Query to clean up statement data from multiple to single rows
Replies
8
Views
186
kcmuppet
K
N
  • Solved
Separate One Cell Data into Multiple Rows
Replies
6
Views
1K
Fluff
Fluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top