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


DateCountryCity


21/03/2018Sierra LeoneFeldkirchen in Kärnten


18/04/2018TongaCuracautín


02/08/2017LiechtensteinTemuka


19/01/2018KazakhstanGouvy


01/06/2018BotswanaMeerdonk


23/04/2017FijiMonte Patria


06/04/2019UruguayMoorsel


21/05/2017Cape VerdeJuneau


06/08/2018NicaraguaCartagena


21/03/2018UkraineOderzo



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


DateCountryCity


21/03/2018Sierra LeoneFeldkirchen in Kärnten


18/04/2018TongaCuracautín


02/08/2017LiechtensteinTemuka



DateCountryCity


19/01/2018KazakhstanGouvy


01/06/2018BotswanaMeerdonk


23/04/2017FijiMonte Patria



DateCountryCity


06/04/2019UruguayMoorsel


21/05/2017Cape VerdeJuneau


06/08/2018NicaraguaCartagena



DateCountryCity


21/03/2018UkraineOderzo


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