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
Bookmarks