I'm pulling data from SQL and am putting in a calculated date column. The data source only gives me a week start date column, there is another column that I can use to calculate the day of the week; there is a description that will end with 1Total, 2Total, 3Total, etc. where 1 refers to Monday.
I use the following code to extract the number from this description and then use it in a Date.AddDays function to get a proper date for that row of data.
Table.AddColumn(Source, "Date", each Date.AddDays([Week StartDate],Number.FromText(Text.Start(Text.End([Name],6),1))-1), type date)
There's no problem with this step when looking at the preview when editing the query. It looks fine and pulls through as expected. But when I actually run the query the column comes through blank.
I initially thought that it was to do with lazy evaluation of the query. One of the steps in my query was to delete the [Name] column in the above code as it was unneccsary after this date has been calculated. I thought it might be that it was not evaluating the add column step properly until after the [Name] column had been deleted.
However I have altered the query so it leaves in this column and it makes no difference 😞
