Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
So I have my PowerQuery working perfectly but when I apply and load the "Apply Query Changes" comes back with the error above. I'm not sure what is causing this but I may have a suspicion. My query generates a seperate record for each existing record between a start and end date. I thus have a record for every day between these dates.
It all seems to work fine in the query, but the query does not load all the rows. It occurred to me that just maybe the above error was happening because the number of rows was blowing out the memory.
It's a bit of a hotch potch 'cos I'm trying to debug it but it looks like this.
let
//Define things for use later on.
MinDate = Record.Field(Table.Min(Source{[Name="RoadmapCoreData"]}[Content],"StartDate"),"StartDate"), //Minimum start date in the source
MaxDate = Record.Field(Table.Max(Source{[Name="RoadmapCoreData"]}[Content],"DueDate"),"DueDate"), //maximum due date in the source
YearsBetweenMinAndMax=Date.Year(MaxDate) - Date.Year(MinDate), //This will give us the number of years between the minimum and the maximum years
//Load the source date.
Source = SharePoint.Tables("https://emckclac.sharepoint.com/sites/its3/SOLUTIONSTEAM/roadmap"),
RoadmapCoreData1 = Source{[Name="RoadmapCoreData"]}[Content],
NoOfDaysOfFunding= Table.AddColumn( RoadmapCoreData1,"NoDAYSFunding",each Duration.Days([DueDate]-[StartDate])),
//This will add a row for every month between the start of MinDate and the end of MaxDate to the table
PutDateTypesRight = Table.TransformColumnTypes(NoOfDaysOfFunding,{{"DueDate", type date}, {"StartDate", type date}}),
AddDays =
Table.ExpandListColumn(
Table.AddColumn(PutDateTypesRight,"Day", each List.Generate(()=>#date(Date.Year(MinDate),1,1), each _< #date(Date.Year(MaxDate)+1,1,1),each Date.AddDays(_,1))),"Day"),
AddFinancialYearStart = Table.AddColumn(AddDays, "FinancialYearStart", each if Date.Month([Day]) >= 8 then Date.Year([Day]) else Date.Year([Day])-1),
//Now work out what months will need some budget to do the work
FindActiveDays = Table.AddColumn(AddFinancialYearStart, "BudgetType", each if [Day] >= Date.StartOfMonth([StartDate]) then if Date.AddDays([Day],1)<=Date.EndOfMonth([DueDate]) then "Money" else "REMOVEME" else "REMOVEME"),
RemoveNonActiveDays = Table.SelectRows(FindActiveDays, each ([BudgetType] = "Money")),
CalculateBudget = Table.AddColumn(RemoveNonActiveDays, "Budget", each (([CostLow]+[CostHigh])/2) / [NoDAYSFunding]),
#"Added Custom" = Table.AddColumn(CalculateBudget , "Status", each if [StageValue]="Archived" then
if [Completed]=true then "Complete" else "No Longer Required" else
if [StageValue]="In-Flight" then "In Progress" else
if [StageValue]="Closing" then "In Progress" else
if [StageValue]="Forming" then "Preparing to Start" else "Waiting for Start"),
in
#"Added Custom"
I'm trying to use Months because there is no DateDiff function for working out the number of months between and when I create a column using a formula the query becomes very slow. If someone has a way around that it might help.
Many thanks for your help
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |