PQ - Table.Split - split single table to three tables by year - less flexible
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 |
Code:
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
Code:
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