Sub table for every categorie

[NIELSVD34]

Hi,

I am trying to create sub-tables on different sheets from a master list. The master list will be added to overtime and I do not want to have to go into the sub-tables and add the lines by hand. Is there a way to automate this by using a formula. I would like to get an sub table for every different name in the categorie section, like one subtable with every 'balanceertoestel' and 'combinatietoestel' and 'draaitoestel' etc.

Thanks. Niels

 

[AlanSidman]

if this is what you are looking for since you did not provide a mocked up solution, please advise

Here is the power query mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Overzicht"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Categorie", type text}, {"Toestel", type text}, {"Producent", type text}, {"Typenummer", type any}, {"Prijs", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Categorie"}, {{"Total", each List.Sum([Prijs]), type nullable number}})
in
    #"Grouped Rows"

Categorie	Total
Balanceertoestel	300​
Combinatietoestel	8350​
Draaitoestel	3550​
Duikelrek	1375​
Glijbaan	2800​
Kabelbaan	1300​
Klim en Klauter	4300​
Schommel	2900​
Speeltoren	1000​
Veerwip	2150​

 

[NIELSVD34]

if this is what you are looking for since you did not provide a mocked up solution, please advise

Here is the power query mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Overzicht"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Categorie", type text}, {"Toestel", type text}, {"Producent", type text}, {"Typenummer", type any}, {"Prijs", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Categorie"}, {{"Total", each List.Sum([Prijs]), type nullable number}})
in
    #"Grouped Rows"

Categorie	Total
Balanceertoestel	300​
Combinatietoestel	8350​
Draaitoestel	3550​
Duikelrek	1375​
Glijbaan	2800​
Kabelbaan	1300​
Klim en Klauter	4300​
Schommel	2900​
Speeltoren	1000​
Veerwip	2150​

Hi, thanks for helping me. This is not exactly what I am looking for. The categorie explains which categorie something is in. I want for example another table with all the data of the ‘Combinatietoestel’ like: and when I add another row with the categorie ‘combinatietoestel’ it automatically adds to the other table. This for every single categorie type

Combinatietoestel	Combinatie Triaction	Kompan	TR219500C	950
	Combinatietoestel	Combinatie Hout	Kompan	Onbekend	900
	Combinatietoestel	Combinatietoestel	Ijreka	IJ2157-12 Delta S 4-12	1.000
	Combinatietoestel	Combinatietoestel	Proludic	J2646A	900
	Combinatietoestel	Houten tonnen met stelten	Onbekend	Onbekend	1.000
	Combinatietoestel	Klim- en klautertoestel	Acacia Robinia	2.12.28.5	1.250
	Combinatietoestel	Speelcombinatie	IJreka	Onbekend	950
	Combinatietoestel	Toestel RVS Glijbaan	Onbekend	Onbekend	1.400

 

[AlanSidman]

I don't see how what you are showing in Post #3 is any different from the source table in your workbook. What am I missing? Perhaps you need to offer up either a better example of what you are expecting your results to be or a better more detailed explanation.