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.
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 😞
Solved! Go to Solution.
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)
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)
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
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.
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
HI @Veles ,
I can't reproduce this, maybe you can open a support ticket for this.
Regards,
Xiaoxin Sheng
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.