the starting point is the following table in which entries are made for events on specific days (journal).
| Entity | Event | Date | Amount |
|---|---|---|---|
| 0123 | acquisition | 05.05.2015 | 10,000.00 |
| 0123 | capital increase | 30.11.2015 | 1,000.00 |
| 0123 | write-off | 31.12.2017 | -4,000.00 |
| 0123 | write-up | 31.12.2019 | 3,000.00 |
This journal is loaded into Power Query to be enhanced with additional information from other sources.
The goal is a Power Pivot table in which the amounts are summarized as at 31.12. of each year (Subtotals).
| Year | Entity | Event | Date | Amount |
|---|---|---|---|---|
| 2015 | 0123 | aquisition | 05.05.2015 | 10,000.00 |
| 2015 | 0123 | capital increase | 30.11.2015 | 1,000.00 |
| 2015 Subtotal | 0123 | 11,000.00 | ||
| 2016 Subtotal | 0123 | 11,000.00 | ||
| 2017 | 0123 | write-off | 31.12.2017 | -4,000.00 |
| 2017 Subtotal | 0123 | 7,000.00 | ||
| 2018 Subtotal | 0123 | 7,000.00 | ||
| 2019 | 0123 | write-up | 31.12.2019 | 3,000.00 |
| 2019 Subtotal | 0123 | 10,000.00 | ||
| 2020 Subtotal | 0123 | 10,000,00 |
The question is how to insert rows in Power Query for years where no activity (event) has occurred (no entry in the journal) so that a subtotal can be shown in Power Pivot as of 31.12. of each year.
I hope I could explain my issue in an understandable way. Thanks in advance for your help!
Kind regards,
Joerg
See if something like this works for you. There are shorter, more confusing ways to do it
Get minimum year of all the data, and maximum year of all the data, and create a table of all combinations of years and entities. See if those are being used. If not, merge that year and entity back into the original table with month=dec day=31
there is a bit of self-merging etc, which requires pasting this into home...advanced... since not all of it can be done in the user interface
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Entity", Int64.Type}, {"Event", type text}, {"Date", type date}, {"Amount", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date])),
// Create table of all possible Entities and Years
DateList = {Date.Year(List.Min(#"Added Custom"[Date])) .. Date.Year(List.Max(#"Added Custom"[Date]))},
Entities = Table.AddColumn(Table.Distinct(Table.SelectColumns(#"Added Custom",{"Entity"})),"Year", each DateList),
#"Expanded Year" = Table.ExpandListColumn(Entities, "Year"),
// Find unique Data and merge into original data set
#"Merged Queries" = Table.NestedJoin(#"Expanded Year",{"Year", "Entity"},#"Added Custom",{"Year", "Entity"},"Table2",JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Date"}, {"Date2"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Table2", each ([Date2] = null)),
#"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Date", each #date([Year],12,31), type date),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Date2", "Year"}),
#"Appended Query" = Table.Combine({#"Changed Type", #"Removed Columns" })
in #"Appended Query"