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

kcmuppet · Friday at 11:12 AM

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

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

Kerryx · Friday at 12:10 PM

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

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"

kcmuppet · Friday at 1:20 PM

Thanks @Kerryx

So following it through, it looks like you:

first filter out the interim heading rows,
then create a column with only the date times in it
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 · Friday at 2:53 PM

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"

kcmuppet · Saturday at 7:36 PM

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 · Saturday at 3:54 AM

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

Kerryx · Saturday at 4:28 AM

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, dried	1	g	0.62	0.6
Grapes, green	1.3	g	0.152	0.2
25/02/2025	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	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	1740556380	Total		25
Ingredient	Quantity	Unit	Carb/unit	Carbohydrate
Bread, seeded	2	slice	18.5	37
Cheese, Camembert	60	g	-0.00003	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 = 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

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

8

26/02/2025 07:53

26/02/2025

07:53:00

Bread, seeded

2

slice

18.5

37

9

26/02/2025 07:53

26/02/2025

07:53:00

Cheese, Camembert

60

g

-0.00003

0

10

26/02/2025 07:53

26/02/2025

07:53:00

Cheese, spreadable, medium fat, soft, w

5

g

0.035

0.2

11

26/02/2025 07:53

26/02/2025

07:53:00

Mayonnaise, standard, retail

1.5

g

0.024

0

Table1

kcmuppet · 2025-03-03T10:23:42-0500

alex78 said:
try..

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.

kcmuppet · 2025-03-03T11:06:59-0500

@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:



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
26/02/2025 13:24	1740576240	Total		37.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"]),

kcmuppet · 2025-03-03T11:10:53-0500

Kerryx said:
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

many Thanks

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

kcmuppet

Active Member

Kerryx

Well-known Member

kcmuppet

Active Member

alansidman

Well-known Member

kcmuppet

Active Member

alex78

Board Regular

Kerryx

Well-known Member

kcmuppet

Active Member

kcmuppet

Active Member

kcmuppet

Active Member

Similar threads

Share this page

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

Active Member

Well-known Member

Active Member

Well-known Member

Active Member

Board Regular

Well-known Member

Active Member

Active Member

Active Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock