PQ - Table.Split - split single table to three tables by year - less flexible

[sandy666]

Table.Split - split single table to three tables by year - less flexible

Date	Country	City
21/03/2018	Sierra Leone	Feldkirchen in Kärnten
18/04/2018	Tonga	Curacautín
02/08/2017	Liechtenstein	Temuka
19/01/2018	Kazakhstan	Gouvy
01/06/2018	Botswana	Meerdonk
23/04/2017	Fiji	Monte Patria
06/04/2019	Uruguay	Moorsel
21/05/2017	Cape Verde	Juneau
06/08/2018	Nicaragua	Cartagena
21/03/2018	Ukraine	Oderzo

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    TS = Table.Split(Table.AddColumn(Source, "Year", each Date.Year([Date]), Int64.Type), 3)
in
    TS

use Add as New Query for each table from the list then load to the sheet

Date	Country	City
21/03/2018	Sierra Leone	Feldkirchen in Kärnten
18/04/2018	Tonga	Curacautín
02/08/2017	Liechtenstein	Temuka
Date	Country	City
19/01/2018	Kazakhstan	Gouvy
01/06/2018	Botswana	Meerdonk
23/04/2017	Fiji	Monte Patria
Date	Country	City
06/04/2019	Uruguay	Moorsel
21/05/2017	Cape Verde	Juneau
06/08/2018	Nicaragua	Cartagena
Date	Country	City
21/03/2018	Ukraine	Oderzo

example of the code for the first table, the rest is similar

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    TS = Table.Split(Table.AddColumn(Source, "Year", each Date.Year([Date]), Int64.Type), 3),
    TS1 = TS{0},
    TypeDate = Table.TransformColumnTypes(Table.RemoveColumns(TS1,{"Year"}),{{"Date", type date}})
in
    TypeDate

as you can see Table.Split trying to split source table to the same number of rows (here: 3) and the rest, but not for each year in own table
it's good to split by groups with the same number of rows


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
http://www.eileenslounge.com/viewtopic.php?p=324457#p324457
http://www.eileenslounge.com/viewtopic.php?p=324064#p324064
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg. ADd4m2zp_xDADd6Nnotj1C
s://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgySdtXqcaA27wQLd1t4AaABAg
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959
http://www.eileenslounge.com/viewtopic.php?f=30&t=41784
http://www.eileenslounge.com/viewtopic.php?p=323966#p323966
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960
http://www.eileenslounge.com/viewtopic.php?p=323894#p323894
http://www.eileenslounge.com/viewtopic.php?p=323843#p323843
http://www.eileenslounge.com/viewtopic.php?p=323547#p323547
http://www.eileenslounge.com/viewtopic.php?p=323516#p323516
http://www.eileenslounge.com/viewtopic.php?p=323517#p323517
http://www.eileenslounge.com/viewtopic.php?p=323449#p323449
http://www.eileenslounge.com/viewtopic.php?p=323226#p323226
http://www.eileenslounge.com/viewtopic.php?f=25&t=41702&p=323150#p323150
http://www.eileenslounge.com/viewtopic.php?p=323085#p323085
http://www.eileenslounge.com/viewtopic.php?p=322955#p322955
http://www.eileenslounge.com/viewtopic.php?f=30&t=41659
http://www.eileenslounge.com/viewtopic.php?p=322462#p322462
http://www.eileenslounge.com/viewtopic.php?p=322356#p322356
http://www.eileenslounge.com/viewtopic.php?p=321984#p321984
https://eileenslounge.com/viewtopic.php?f=30&t=41610
https://eileenslounge.com/viewtopic.php?p=322176#p322176
https://eileenslounge.com/viewtopic.php?p=322238#p322238
https://eileenslounge.com/viewtopic.php?p=322270#p322270
https://eileenslounge.com/viewtopic.php?p=322300#p322300
http://www.eileenslounge.com/viewtopic.php?p=322150#p322150
http://www.eileenslounge.com/viewtopic.php?p=322111#p322111
http://www.eileenslounge.com/viewtopic.php?p=322086#p322086
https://stackoverflow.com/questions/33868233/shell-namespace-not-accepting-string-variable-but-accepting-string-itself/77888851#77888851
http://www.eileenslounge.com/viewtopic.php?p=322084#p322084
http://www.eileenslounge.com/viewtopic.php?p=321822#p321822
http://www.eileenslounge.com/viewtopic.php?p=322424#p322424
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA