-
Notifications
You must be signed in to change notification settings - Fork 5
/
Copy pathtodo_SpreadsheetBI.txt
256 lines (146 loc) · 9.87 KB
/
todo_SpreadsheetBI.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
ReportingSheet and Index
---------------------------
- Link Index sheet to sheet headers (rather than hardcode)
- this allows for dynamic headers e.g. with date
- does not an index recreate each time header is updated on sheeet
- There is duplication in ReportingSheet.Create and ReportingSheet.CreateFromExistingSheet --> look into combining in one sub
- Instead of referencing range names (previously set) would it be bettter to get the range by set as a class property get?
TODO - OTHER
---------------
Maybe forget about schema files as there are too many other operations regularly performed on files eg add dates joins etc.
Still worth considering storing single power query along with data though .
Create schema file from PowerBI add in?
- Create new file
- Store schema sheets in PowerBi file and then copy across to the new file
The power query library is buried in the code - wondering if I need to go back to moving into its own clearly lableled repository again
- It would also be better to get the power queries organised into folders
Work on powerquery - fn_Headings
Work on powerQuery = fn_Consolidate
How do I get these P
Remove table formatting - best handled via book.xlsxt template
When formatting pivot allow selections of one of the custom styles
Also add to template ?
https://www.xelplus.com/smart-uses-of-custom-formatting/
Maybe also link the ReportSheet design to cell styles (rather than capturing details in a userform)
When formatting numbers etc and cells in a table are selected, format the entire column in that table
When returning to index from another sheet it would be good to go to that sheets listing on the index
InsertIndex page gets slow as workbook grows - any way to improve?
Tempted to move Return to Index button (on reporting Sheets) slightly to the right
Try to generate reports on file Test
- > looks as if only final report is being saved - others being overwritten?
I have noticed SpeadsheetBI file itself being saved down in same folder where power querie files are exported?
Test function CreateStorageFromPowerQuery (in ListStorage class)
There are some implied dependencies between ListStorage and ReportingSheet that should be cleaned up, for example the row / column where to locate the listobject
Look at mr excel lambdas
https://t.co/EIJ0mh1eXq?amp=1
Work on Lambda function wizard (lambdas are now being stored in the add-in)
- Maybe experiment with the refedit in teh class rather than textbox (should be an easy change) and see if any issues
- Import Standard Power Queries currently broken due to no delimiter in lambda query
--> Maybe also transition the standard power queries to a git repo? Could potentially just evaluate straight from. Web?
Why does sum StackArray or SUM AppendTwoColumns not work
Can I create a custom ETF data type (via power query)
- price history
- performance history
- holdings
- distributions
- maybe combine with Lambdas eg to calc cumulative return, value of holding on given date etc?
Look at dynamic array accumulation
https://techcommunity.microsoft.com/t5/excel/ways-of-performing-accumulation-with-dynamic-arrays/m-p/2329035
See todos of other projects that have been rolled up into SpreadsheetBI
EntryPoints and Core for DataModel not really linked to Menu. Reconsider best way to use. Can make date model unstable?
Out of memory / crashing
* If excel remains stable add back other add ins one by one (maybe download latest version)
* Potenitially issue is with name manager?
(i) DAX studio
(ii) Power Pivot Utilities
(iii) Name manager
(iv) Rubber Duck VBA
Not major issue - but why is the insert index page / reporting sheet so slow?
Add an additional menu items that runs looper and also adds (updates) the index page
Revisit name manger - can filter by Lambda functions?
TableLooper should be ready for implementation - test out with PowerBI roll forward
It may be time for a restructure or the Roll forward
- seperate data and reporting sheets
Generate and import query descriptions with other metadata
Lots of the below apply to the pivot report creation which is now sort of abandoned (maybe?) --> rather following approach of having different workbooks picking up shared power quwery text files
------------------------------------------------------------------------------------------
How about:
- All pivots are excel based
- All sources are dax queries in central file
- queries have text that can be replaced (acting as parameters)
- queries are stored in client workbook and copied to central workbook for refresh. Data is then copied into client workbook
Reduce VBA adn get rid of userform - drive report creation from a worksheet?
Need an easier ways to produce boolean filter arrays, maybe some sort of replace function
- Function that converts something like below
<<Array>> = <MyText>
TableArray class --> move something like this into the column property get. Note the zero in rowindex paramater
WorksheetFunction.Transpose(WorksheetFunction.Index(this.data, 0, 4))
--> tih successfully produces a one dimenstional array (rather than an n by 1 dimensional array)
Continue updating ReportingData.ReadUniqueSortedReportCategories
Sub testArrayMath()
Dim a, b, rv
a = Array(True, True, True, True, True)
b = Array(False, True, False, True, True)
rv = Evaluate("{" & Join(a, ";") & "}*{" & Join(b, ";") & "}")
End Sub
Join does not work with variant arrays read from range as they have 2 dimensions although 2nd dimension is 1?
Does this help?
https://stackoverflow.com/questions/22766184/assign-1d-array-from-2d-array-in-vba/22769603
May be worth considering splitting out a "DataArray" class seperate to ListStorage. The latter is used for raw storage only, while the former for in memory data table
Can consider keeping the dataArray really simple with just properties to return table and headers item in col etc. All lookups etc can simply be handled in main code body.
Update the filter functionality of liststorage
- create functionality to return entire table (filtered or not = default) as a variant
- Don't create the filtered listobject until the filter function is called
- Add an optional paramarry to the filter function to specify which columns will be returned
- Do I really need the filtered list of listobject to be represented on the grid or can it reside in memory.
--> if I maintain in memory how can I perform functions such as xlookup?
- If progressing with the above should I shift the entire workings of liststorage (including the unfiltered components) to in-memory?
This works!
Sub TestVariantArray()
Dim wkb As Workbook
Dim rng As Range
Dim v As Variant
Dim colProperty As Variant
Dim colValue As Variant
Dim unq As Variant
Dim rv As Variant
Set wkb = Application.Workbooks("PivotReportExample.xlsm")
Set rng = wkb.Sheets("ReportPRoperties").ListObjects("tbl_ReportProperties").DataBodyRange
v = rng
colProperty = rng.Columns(4)
colValue = rng.Columns(5)
rv = WorksheetFunction.Xlookup("Category", colProperty, colValue)
End Sub
ufPivotReportGenerator.RefreshListBox --> how do I get the ReportType for each item to be displayed in ReportList - almost may need to add extra functionality to Listobject class
- Below works to filter columns - why?
* =FILTER(tbl_ReportProperties_Filtered, tbl_ReportProperties_Filtered[#Headers]="DataType")
* Look at filter function definition
* See step above
Add an All option in reports listbox so that all reports can be run for a specific category
Look at this (see also reference to Lambda editor)
https://techcommunity.microsoft.com/t5/excel/ways-of-performing-accumulation-with-dynamic-arrays/m-p/2329035
Read this
https://techcommunity.microsoft.com/t5/excel-blog/announcing-lambda-turn-excel-formulas-into-custom-functions/ba-p/1925546
Listobject class (and calling subs) --> need to protect against errors generated with null data including in transpose functions
- Remove TableLooper from SpreadsheetBI? Process is better in seperate spreadsheet "Spreadsheet_Iterative_Model"
- Power Query changing to quotestyleCSV seems to preserve line breaks if there are in quotes. All fields need to be in quotes
- The pipe delimited spreadsheet metadata DAX measures are not generating correctly when there are newlines in the measures
Maybe best option is to remove newlines before writing to the text file
The Human readable DAX measure file is fine
The same issue occures in teh listobject metadata exports (both formulas and values) when there is an Alt-enter newline in the cell
Look into the new-ish Excel data types (creatable via power query)
https://techcommunity.microsoft.com/t5/excel-blog/announcing-power-query-data-types/ba-p/1782903
Look into Lambda function when available --> Maybe move to insiders build
- Create a readme
- For each power query is it possible to save down whether it is loaded to the data model
In the date table - is there an issue with using list.dates - see comment from Ken Puls here
https://blog.crossjoin.co.uk/2016/06/03/creating-tables-in-power-bipower-query-m-code-using-table/
Website / Twitter
------------------
- Set up Wordpress Blog website
- Consider below theme, used by https://xxlbi.com/ as per code inspector
https://wordpress.org/themes/twentysixteen/ maybe rather TwentyTwenty or something in between
Resources to revisit
-------------------
* Excel meets Lambda (Andy Gordon)
https://www.youtube.com/watch?v=7tFq-9Zvk3M&t=