Book1 | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | Month | As of Date | Capacity | ||||||||||
2 | Jan | 8-Dec | 654,541 | Sum of Capacity | As of Date | ||||||||
3 | Jan | 1-Dec | 624,409 | Month | 8-Dec | 1-Dec | 24-Nov | 17-Nov | Grand Total | ||||
4 | Jan | 24-Nov | 651,927 | Jan | 654541 | 624409 | 651927 | 626109 | 2556986 | ||||
5 | Jan | 17-Nov | 626,109 | Feb | 591093 | 563715 | 588339 | 565605 | 2308752 | ||||
6 | Jan | 10-Nov | 603,571 | Mar | 656568 | 630145 | 655954 | 635932 | 2578599 | ||||
7 | Feb | 8-Dec | 591,093 | Grand Total | 1902202 | 1818269 | 1896220 | 1827646 | 7444337 | ||||
8 | Feb | 1-Dec | 563,715 | ||||||||||
9 | Feb | 24-Nov | 588,339 | ||||||||||
10 | Feb | 17-Nov | 565,605 | ||||||||||
11 | Feb | 10-Nov | 533,425 | ||||||||||
12 | Mar | 8-Dec | 656,568 | ||||||||||
13 | Mar | 1-Dec | 630,145 | ||||||||||
14 | Mar | 24-Nov | 655,954 | ||||||||||
15 | Mar | 17-Nov | 635,932 | ||||||||||
16 | Mar | 10-Nov | 601,829 | ||||||||||
17 | |||||||||||||
18 | |||||||||||||
Sheet1 |
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"As of Date", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([As of Date] <> #date(2022, 11, 10))),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Filtered Rows", {{"As of Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Filtered Rows", {{"As of Date", type text}}, "en-US")[#"As of Date"]), "As of Date", " Capacity")
in
#"Pivoted Column"
Book5 | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | Month | As of Date | Capacity | ||||||||||
2 | Jan | 12/8/2022 | 654541 | Sum of Capacity | As of Date | ||||||||
3 | Jan | 12/1/2022 | 624409 | Month | 12/8/2022 | 12/1/2022 | 11/24/2022 | 11/17/2022 | Grand Total | ||||
4 | Jan | 11/24/2022 | 651927 | Jan | 654541 | 624409 | 651927 | 626109 | 2556986 | ||||
5 | Jan | 11/17/2022 | 626109 | Feb | 591093 | 563715 | 588339 | 565605 | 2308752 | ||||
6 | Jan | 11/10/2022 | 603571 | Mar | 656568 | 630145 | 655954 | 635932 | 2578599 | ||||
7 | Feb | 12/8/2022 | 591093 | Grand Total | 1902202 | 1818269 | 1896220 | 1827646 | 7444337 | ||||
8 | Feb | 12/1/2022 | 563715 | ||||||||||
9 | Feb | 11/24/2022 | 588339 | Month | 12/8/2022 | 12/1/2022 | 11/24/2022 | 11/17/2022 | |||||
10 | Feb | 11/17/2022 | 565605 | Feb | 591093 | 563715 | 588339 | 565605 | |||||
11 | Feb | 11/10/2022 | 533425 | Jan | 654541 | 624409 | 651927 | 626109 | |||||
12 | Mar | 12/8/2022 | 656568 | Mar | 656568 | 630145 | 655954 | 635932 | |||||
13 | Mar | 12/1/2022 | 630145 | ||||||||||
14 | Mar | 11/24/2022 | 655954 | ||||||||||
15 | Mar | 11/17/2022 | 635932 | ||||||||||
16 | Mar | 11/10/2022 | 601829 | ||||||||||
17 | |||||||||||||
Sheet1 |
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ChangedTypeDateOnly = Table.TransformColumnTypes(Source,{{"As of Date", type date}})
in
ChangedTypeDateOnly
let
Source = #"01_Data",
#"Removed Other Columns" = Table.SelectColumns(Source,{"As of Date"}),
#"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"As of Date", Order.Descending}}),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows"),
#"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 1, 1, Int64.Type),
FilteredLast_n_Dates = Table.SelectRows(#"Added Index", each [Index] <= 4),
MergedQueriesDateWithData = Table.NestedJoin(FilteredLast_n_Dates, {"As of Date"}, #"01_Data", {"As of Date"}, "01_Data", JoinKind.LeftOuter),
#"Expanded 01_Data" = Table.ExpandTableColumn(MergedQueriesDateWithData, "01_Data", {"Month", " Capacity"}, {"Month", " Capacity"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded 01_Data",{"Index"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Month", type text}, {" Capacity", Int64.Type}})
in
#"Changed Type"
20221221 PQ Last 4 Dates shansakhi.xlsm | |||||
---|---|---|---|---|---|
L | M | N | |||
2 | As of Date | Month | Capacity | ||
3 | 8/12/2022 | Mar | 656568 | ||
4 | 8/12/2022 | Feb | 591093 | ||
5 | 8/12/2022 | Jan | 654541 | ||
6 | 1/12/2022 | Mar | 630145 | ||
7 | 1/12/2022 | Feb | 563715 | ||
8 | 1/12/2022 | Jan | 624409 | ||
9 | 24/11/2022 | Mar | 655954 | ||
10 | 24/11/2022 | Jan | 651927 | ||
11 | 24/11/2022 | Feb | 588339 | ||
12 | 17/11/2022 | Mar | 635932 | ||
13 | 17/11/2022 | Feb | 565605 | ||
14 | 17/11/2022 | Jan | 626109 | ||
Data |
What does this mean ?My data is loaded as connection with pivot.