cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate IV
Advocate IV

Calculated 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 😞

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Advocate IV
Advocate IV

Re: Calculated column looks correct on preview but blank on refresh (dataflow)

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)

View solution in original post

7 REPLIES 7
Highlighted
Community Support
Community Support

Re: Calculated column looks correct on preview but blank on refresh (dataflow)

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Highlighted
Advocate IV
Advocate IV

Re: Calculated column looks correct on preview but blank on refresh (dataflow)

Here'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.

 

NameWeek StartDateExpected Result
Value1Total20/05/201920/05/2019
Value2Total20/05/201921/05/2019
Value3Total20/05/201922/05/2019
Value4Total20/05/201923/05/2019
Value5Total20/05/201924/05/2019
Value6Total20/05/201925/05/2019
Value7Total20/05/201926/05/2019
Transactions1Total20/05/201920/05/2019
Transactions2Total20/05/201921/05/2019
Transactions3Total20/05/201922/05/2019
Transactions4Total20/05/201923/05/2019
Transactions5Total20/05/201924/05/2019
Transactions6Total20/05/201925/05/2019
Transactions7Total20/05/201926/05/2019
Community Support
Community Support

Re: Calculated column looks correct on preview but blank on refresh (dataflow)

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Highlighted
Advocate IV
Advocate IV

Re: Calculated column looks correct on preview but blank on refresh (dataflow)

Hi @v-shex-msft 

 

No computed entities in my dataflow

Entities 1.png

 

Entities 2.png

 

Highlighted
Community Support
Community Support

Re: Calculated column looks correct on preview but blank on refresh (dataflow)

HI @Veles ,

I can't reproduce this, maybe you can open a support ticket for this.

submit a support ticketsubmit a support ticket

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Highlighted
Advocate IV
Advocate IV

Re: Calculated column looks correct on preview but blank on refresh (dataflow)

Thanks @v-shex-msft I've raised a ticket to look into this

Highlighted
Advocate IV
Advocate IV

Re: Calculated column looks correct on preview but blank on refresh (dataflow)

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)

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors