I am creating a regular report using PowerQuery in Excel that has pivoted data. The pivot has the latest four quarters as column names in date order (e.g. 30/09/2018, 31/12/2018, 31/03/2019, 30/06/2019). The values in each date column are numeric. Every time the report is run, the column names are going to change, because...new dates!
The rows represent different entities (e.g. Bank of Mum, Bank of Dad, Bank of Grandpa). I need to create a few derived columns that calculate the differences between the latest quarter columns (last column) and previous quarter columns (eg 3 month movement, 6 month movement). Here is some sample data:
This is what the data would look like before the pivot:
Entity_Name Quarter Date Value
Bank of Dad 30/09/2018 4
Bank of Dad 31/12/2018 2
Bank of Dad 31/03/2019 3
Bank of Dad 30/06/2019 3
Bank of Grandpa 30/09/2018 4
Bank of Grandpa 31/12/2018 6
Bank of Grandpa 31/03/2019 1
Bank of Grandpa 30/06/2019 2
Bank of Mum 30/09/2018 5
Bank of Mum 31/12/2018 5
Bank of Mum 31/03/2019 4
Bank of Mum 30/06/2019 4
And after the pivot:
Entity_Name 30/09/2018 31/12/2018 31/03/2019 30/06/2019
Bank of Grandpa 4 6 1 2
Bank of Mum 5 5 4 4
Bank of Dad 4 2 3 3
And here is where I would want to get to (the latest quarter minus three months ago and six months ago):
Entity_Name Movement (3 months) Movement (6 months)
Bank of Grandpa 1 -4
Bank of Mum 0 -1
Bank of Dad 0 1
I have had a good hunt around the internet and I have found similar but not quite there examples (usually to do with renaming columns or filtering values from columns). My problem is actually a lot more simple than what I have found, so I feel like this should be relatively straightforward and I am just missing something obvious that noone else has needed to question. I am a pandas/python girl working in PowerQuery for the first time (I need to hand this one off to a client for regular running, so I need the report to 'talk' their language - i.e. excel.) PowerQuery does some things really well (joins and group by in excel? - cool!). Other things are a bit of fight - like this!
I have looked at this problem two ways - first, try to do this before the pivot so I am not wrestling with changing column names. This is a curly one. It might be more simple if there was only one entity, but the multiple entities make it tricky. My preferred approach, consistent with how this report was previously written (in excel, not using power query) is to derive columns from the pivoted data. To do this I will need to reference columns by position when I add the new columns.
If I can solve the problem of getting a column returned that holds the values of another column via a positional reference, then I am 99% of the way there, so I am looking for help to solve that particular part of the problem.
i.e. get to this point by using a positional reference for the column (and not the explicit column name):
Entity_Name 30/09/2018 ... Values_in_col_1
Bank of Dad 4 ... 4
Bank of Grandpa 4 ... 4
Bank of Mum 5 ... 5
This is what I have tried:
let
Source = Excel.CurrentWorkbook(){[Name="Table14"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Entity name", type text}, {"Quarter date", type date}, {"Value", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Entity name", Order.Ascending}, {"Quarter date", Order.Ascending}}),
#"PrevTable" = Table.Pivot(Table.TransformColumnTypes(#"Sorted Rows", {{"Quarter date", type text}}, "en-NZ"), List.Distinct(Table.TransformColumnTypes(#"Sorted Rows", {{"Quarter date", type text}}, "en-NZ")[#"Quarter date"]), "Quarter date", "Value", List.Sum),
#"Column names" = Table.ColumnNames(#"PrevTable"),
#"Test 1" = Table.AddColumn(#"PrevTable", "Test1", each #"Column names"{1}),
#"Test 2" = Table.AddColumn(#"Test 1", "Test2", each Table.SelectRows(#"PrevTable", each(Record.Field(_,Table.ColumnNames(#"PrevTable"){1}))))
in
#"Test 2"
Test 1: I have managed to get to a point where I can create a list of column names and reference them by index - but what gets returned is a column of values that are the column name, not the actual values! Haha.
Test 2: I have also tried this other thing to see if I can retrieve row values (trying to rework code I found that was also meant to filter), but that returns a table with no columns in it.
Here is the table I get from that code:
Entity_Name 30/09/2018 ... Test1 Test2
Bank of Dad 4 ... 30/09/2018 [Table]
Bank of Grandpa 4 ... 30/09/2018 [Table]
Bank of Mum 5 ... 30/09/2018 [Table]
And If I click on the 'Table' values I get this type of response:
Expression.Error: We cannot convert the value 4 to type Logical.
Any help would be much appreciated - so many tools to learn, so little brain!
