sandy666
05-14-2020, 09:50 PM
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
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
http://www.eileenslounge.com/viewtopic.php?p=324457#p324457 (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=324064#p324064)
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960 (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)
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg.ADd4m2zp_xDADd6Nnotj 1C (https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg.ADd4m2zp_xDADd6Nnotj 1C)
s://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgySdtXqcaA27wQLd1t4AaABAg (s://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgySdtXqcaA27wQLd1t4AaABAg)
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959 (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?f=30&t=41784)
http://www.eileenslounge.com/viewtopic.php?p=323966#p323966 (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=323959#p323959)
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960 (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=323894#p323894)
http://www.eileenslounge.com/viewtopic.php?p=323843#p323843 (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=323547#p323547)
http://www.eileenslounge.com/viewtopic.php?p=323516#p323516 (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=323517#p323517)
http://www.eileenslounge.com/viewtopic.php?p=323449#p323449 (http://www.eileenslounge.com/viewtopic.php?p=323449#p323449)
http://www.eileenslounge.com/viewtopic.php?p=323226#p323226 (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?f=25&t=41702&p=323150#p323150)
http://www.eileenslounge.com/viewtopic.php?p=323085#p323085 (http://www.eileenslounge.com/viewtopic.php?p=323085#p323085)
http://www.eileenslounge.com/viewtopic.php?p=322955#p322955 (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?f=30&t=41659)
http://www.eileenslounge.com/viewtopic.php?p=322462#p322462 (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=322356#p322356)
http://www.eileenslounge.com/viewtopic.php?p=321984#p321984 (http://www.eileenslounge.com/viewtopic.php?p=321984#p321984)
https://eileenslounge.com/viewtopic.php?f=30&t=41610 (https://eileenslounge.com/viewtopic.php?f=30&t=41610)
https://eileenslounge.com/viewtopic.php?p=322176#p322176 (https://eileenslounge.com/viewtopic.php?p=322176#p322176)
https://eileenslounge.com/viewtopic.php?p=322238#p322238 (https://eileenslounge.com/viewtopic.php?p=322238#p322238)
https://eileenslounge.com/viewtopic.php?p=322270#p322270 (https://eileenslounge.com/viewtopic.php?p=322270#p322270)
https://eileenslounge.com/viewtopic.php?p=322300#p322300 (https://eileenslounge.com/viewtopic.php?p=322300#p322300)
http://www.eileenslounge.com/viewtopic.php?p=322150#p322150 (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=322111#p322111)
http://www.eileenslounge.com/viewtopic.php?p=322086#p322086 (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 (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=322084#p322084)
http://www.eileenslounge.com/viewtopic.php?p=321822#p321822 (http://www.eileenslounge.com/viewtopic.php?p=321822#p321822)
http://www.eileenslounge.com/viewtopic.php?p=322424#p322424 (http://www.eileenslounge.com/viewtopic.php?p=322424#p322424)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
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
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
http://www.eileenslounge.com/viewtopic.php?p=324457#p324457 (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=324064#p324064)
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960 (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)
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg.ADd4m2zp_xDADd6Nnotj 1C (https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg.ADd4m2zp_xDADd6Nnotj 1C)
s://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgySdtXqcaA27wQLd1t4AaABAg (s://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgySdtXqcaA27wQLd1t4AaABAg)
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959 (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?f=30&t=41784)
http://www.eileenslounge.com/viewtopic.php?p=323966#p323966 (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=323959#p323959)
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960 (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=323894#p323894)
http://www.eileenslounge.com/viewtopic.php?p=323843#p323843 (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=323547#p323547)
http://www.eileenslounge.com/viewtopic.php?p=323516#p323516 (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=323517#p323517)
http://www.eileenslounge.com/viewtopic.php?p=323449#p323449 (http://www.eileenslounge.com/viewtopic.php?p=323449#p323449)
http://www.eileenslounge.com/viewtopic.php?p=323226#p323226 (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?f=25&t=41702&p=323150#p323150)
http://www.eileenslounge.com/viewtopic.php?p=323085#p323085 (http://www.eileenslounge.com/viewtopic.php?p=323085#p323085)
http://www.eileenslounge.com/viewtopic.php?p=322955#p322955 (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?f=30&t=41659)
http://www.eileenslounge.com/viewtopic.php?p=322462#p322462 (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=322356#p322356)
http://www.eileenslounge.com/viewtopic.php?p=321984#p321984 (http://www.eileenslounge.com/viewtopic.php?p=321984#p321984)
https://eileenslounge.com/viewtopic.php?f=30&t=41610 (https://eileenslounge.com/viewtopic.php?f=30&t=41610)
https://eileenslounge.com/viewtopic.php?p=322176#p322176 (https://eileenslounge.com/viewtopic.php?p=322176#p322176)
https://eileenslounge.com/viewtopic.php?p=322238#p322238 (https://eileenslounge.com/viewtopic.php?p=322238#p322238)
https://eileenslounge.com/viewtopic.php?p=322270#p322270 (https://eileenslounge.com/viewtopic.php?p=322270#p322270)
https://eileenslounge.com/viewtopic.php?p=322300#p322300 (https://eileenslounge.com/viewtopic.php?p=322300#p322300)
http://www.eileenslounge.com/viewtopic.php?p=322150#p322150 (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=322111#p322111)
http://www.eileenslounge.com/viewtopic.php?p=322086#p322086 (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 (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=322084#p322084)
http://www.eileenslounge.com/viewtopic.php?p=321822#p321822 (http://www.eileenslounge.com/viewtopic.php?p=321822#p321822)
http://www.eileenslounge.com/viewtopic.php?p=322424#p322424 (http://www.eileenslounge.com/viewtopic.php?p=322424#p322424)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)