Hi all,
I have a data set that's similar to the following (but over 4000 rows)
Project Data Category Data Type Source Destination Time Period Amount Index
A Cash Flow Baseline Source1 DestinationA Q1YYYY ##### 1
B Expected Value Actual Source2 DestinationA Q2YYYY ##### 2
C Spend Down Forecast Source3 DestinationB Q1YYYY ##### 3
The data that I have in the "Amount" Column is cumulative. I need to make it into specific data, individual for each time period. The code I have to do that is the following:
#"test" = Table.AddColumn(#"PreviousStep","Specific",
each if [Data Category] <> "Fund Budget"
then if [Funding Source] = #"PreviousStep"{[Index]-2}[Funding Source] and [Funding Destination] = #"PreviousStep"{[Index]-2}[Funding Destination] and [Project] = #"PreviousStep"{[Index]-2}[Project] and [Data Category] = #"PreviousStep"{[Index]-2}[Data Category] and [Data Type] = #"PreviousStep"{[Index]-2}[Data Type]
then [Amount] - #"PreviousStep"{[Index]-2}[Amount]
else if [Data Type] = "Forecast"
then [Amount] - Number.From(List.Last(List.RemoveNulls(List.Generate(()=>[x = 0, y = null], each [x] < List.Count(Table.Column(#"PreviousStep", "Amount")), each [x = [x] + 1, y = each if #"PreviousStep"{[x]}[Project] = [Project] and #"PreviousStep"{[x]}[Data Category] = [Data Category] and #"PreviousStep"{[x]}[Data Type] = "Actual" and #"PreviousStep"{[x]}[Funding Source] = [Funding Source] and #"PreviousStep"{[x]}[Funding Destination] =[Funding Destination] then #"PreviousStep"{[x]}[Amount] else null], each [y]))))
else [Amount]
else [Amount])Basically, the code is supposed to work as follows. As the query creates a new column, it checks first to see the Data Category- anything that's part of a "Fund Budget" does NOT get unfolded into a specific number, it just stays in cumulative form. Next, it checks to see if the Data Type, Category, Source, and Destination match between the row in question and the previous row (all of the data is listed in chunks ordered by time period). If they do, the specific amount is calculated just by subtracting the cumulative amount from the previous time period from the cumulative amount of the current time period.
The next part is the tricky bit. For Forecasted data, the specific period amount for the first point (i.e. the data that doesn't match the immediate precious row) should be calculated by subtracting out the cumulative from the Actual data of the previous time period. My idea of how to do this involves using List.Generate to separate out the Actual data that matches the Data Category, Source, and Destination of each of the Forecast initial points and grabbing the last of that list to subtract from the Forecast amount. So far, the code seems to do what it's supposed to.
But when I click "Close & Apply" the model takes ages to actually update (as in 2 hours and counting). It didn't do this until I added the Forecast specific if-then-else statement, and I understand that adding a bit more time to the update and apply process (it's a pretty complex generation trick, after all) but 2 hours? I find that a little ridiculous.
So do you all see something that maybe I missed that's making this way more difficult than it needs to be? Is there a way to speed up this process? Unfortunately I am restricted to using power query for this- I thought about moving it into Python initially but due to restrictions from the top brass I have to do all of my transformations in Power BI.
Thanks,
Jason
