Forum Discussion
Advocate VCalculated column looks correct on preview but blank on refresh (dataflow)
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 :(
Support have come back with a solution that appears to work.
The original code to add the column in was this:
Table.AddColumn(Source, "Date", each Date.AddDays([Week StartDate],Number.FromText(Text.Start(Text.End([Name],6),1))-1), type date)
They said to wrap the Date.AddDays() with Date.From() like so:
Table.AddColumn(Source, "Date", each Date.From(Date.AddDays([Week StartDate],Number.FromText(Text.Start(Text.End([Name],6),1))-1)), type date)
7 Replies
Hi Veles ,
I'm not so clear for your data structure. Can you please explain more about these?
How to Get Your Question Answered Quickly
In addition, you mentioned 'name' field. Did this field from datasource or calculate fields based on m query formula?
Regards,
Xiaoxin Sheng
VelesHere's a sample of the data source. Name and Week StartDate are columns on the data source. All other columns are irrelevant for this scenario.
The number contained in the middle of the Name is equivalent of the weekday number (where 1 = Monday). I'm using this to calculate what the actual date is of that bit of data.
I have absolutely no issue with this calculation, it works fine when building the query and viewing it in the query editor. The problem is when I run the query the column comes out blank, all the values I was seeing when building the query don't appear.
Name Week StartDate Expected Result Value1Total 20/05/2019 20/05/2019 Value2Total 20/05/2019 21/05/2019 Value3Total 20/05/2019 22/05/2019 Value4Total 20/05/2019 23/05/2019 Value5Total 20/05/2019 24/05/2019 Value6Total 20/05/2019 25/05/2019 Value7Total 20/05/2019 26/05/2019 Transactions1Total 20/05/2019 20/05/2019 Transactions2Total 20/05/2019 21/05/2019 Transactions3Total 20/05/2019 22/05/2019 Transactions4Total 20/05/2019 23/05/2019 Transactions5Total 20/05/2019 24/05/2019 Transactions6Total 20/05/2019 25/05/2019 Transactions7Total 20/05/2019 26/05/2019 HI Veles ,
Is there a 'lightning' icon on your entity?
If this is a case, it seems like you're using computed entities, please take a look at official document to know the limitations:
Using computed entities on Power BI Premium
Regards,
Xiaoxin Sheng
- Veles
Support have come back with a solution that appears to work.
The original code to add the column in was this:
Table.AddColumn(Source, "Date", each Date.AddDays([Week StartDate],Number.FromText(Text.Start(Text.End([Name],6),1))-1), type date)
They said to wrap the Date.AddDays() with Date.From() like so:
Table.AddColumn(Source, "Date", each Date.From(Date.AddDays([Week StartDate],Number.FromText(Text.Start(Text.End([Name],6),1))-1)), type date)
