Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

"Nullable Object must have a value" Error while creating a custom Date table

Experincing issues on creating a custom table, Have the following frown (error) message on executing the code below.

 

Error Message:
Nullable Object must have a value.

Timestamp:
2019-05-28T07:45:42.6324632Z

Session ID:
d7da4490-16ee-4037-b51c-5cbb0ffacc2a

Release:
May 2019

Product Version:
2.69.5467.1751 (19.05) (x64)

Stack Trace:
at System.ThrowHelper.ThrowInvalidOperationException(ExceptionResource resource)
at Microsoft.Mashup.Client.UI.Shared.HtmlRenderers.ColumnProfiling.ColumnQualityViewModel.get_ValidFraction()
at Microsoft.Mashup.Client.UI.Shared.HtmlRenderers.ColumnProfiling.ColumnQualityBarRenderer.GetBarViewModels(ColumnQualityViewModel viewModel)
at Microsoft.Mashup.Client.UI.Shared.HtmlRenderers.ColumnProfiling.ColumnQualityBarRenderer.Render(IHtmlTextWriter writer, ColumnQualityViewModel viewModel)
at Microsoft.Mashup.Client.UI.Shared.HtmlRenderers.ColumnProfiling.ColumnQualityRenderer.Render(IHtmlTextWriter writer, ColumnQualityViewModel columnQualityViewModel, Boolean renderDetails)
at Microsoft.Mashup.Client.UI.Shared.HtmlRenderers.ColumnProfiling.ColumnProfileRenderer.RenderColumnQuality(IHtmlTextWriter writer, IReadonlyColumnProfile columnProfile, Int32 columnWidth, Boolean renderColumnQualityDetails)
at Microsoft.Mashup.Client.UI.Shared.HtmlRenderers.ColumnProfiling.ColumnProfileRenderer.Render(IHtmlTextWriter writer, Int32 columnIndex, IReadonlyColumnProfile columnProfile, Int32 columnOffset, Int32 columnWidth, Boolean renderColumnQualityDetails, Boolean renderColumnDistribution)
at Microsoft.Mashup.Client.UI.Shared.HtmlControls.Results.Table.HtmlTableRenderer.RenderColumnProfiles(IHtmlTextWriter writer, Range columnRange)
at Microsoft.Mashup.Client.UI.Shared.PageManager.HtmlTablePageRenderer.RenderColumnProfilePage(IHtmlTextWriter writer, PageColumn pageColumn, Int32 pageColumnIndex)
at Microsoft.Mashup.Client.UI.Shared.PageManager.HtmlGridPageRenderer.ExecuteWithHtmlWriter(Action`1 renderAction)
at Microsoft.Mashup.Client.UI.Shared.HtmlControls.Results.Grid.GridResultHtmlControl.JsPageContainerWrapper.AppendPages(PageHtmlProvider pageHtmlProvider, IEnumerable`1 indexesOfPagesToAppend)
at Microsoft.Mashup.Client.UI.Shared.HtmlControls.Results.Grid.GridResultHtmlControl.JsPageContainerWrapper.UpdatePages(PageHtmlProvider pageHtmlProvider, IEnumerable`1 indexesOfPagesToRemove, IEnumerable`1 indexesOfPagesToAppend, IEnumerable`1 indexesOfPagesToPrepend)
at Microsoft.Mashup.Client.UI.Shared.HtmlControls.Results.Table.TableResultHtmlControl.RenderColumnProfilePages(HtmlGridPageRenderer gridPageRenderer, GridPages gridPagesToRender, GridPageDiffs gridPageDiffs)
at Microsoft.Mashup.Client.UI.Shared.HtmlControls.Results.Table.TableResultHtmlControl.OnProfileCompleted(Object sender, ProfileComputedEventArgs eventArgs)
at System.EventHandler`1.Invoke(Object sender, TEventArgs e)
at Microsoft.Mashup.Host.Models.EventHandlerExtensions.RaiseEvent[TEventArgs](EventHandler`1 handler, Object sender, TEventArgs args)
at Microsoft.Mashup.Client.UI.Shared.Models.Profiling.TableProfileEvaluator.TableProfileEvaluationRunner.OnEvaluationCompleted(ITableProfileEvaluation currentEvaluation, IEnumerable`1 computedColumnProfiles)
at Microsoft.Mashup.Client.UI.Shared.Models.Profiling.Evaluations.TableProfileEvaluationBase.OnEvaluationCompleted(PreviewReference previewReference, Action`1 completedCallback, Action failedCallback)
at Microsoft.Mashup.Host.Document.Evaluation.PreviewEvaluationQueue.<>c__DisplayClass48_0.<OnPreviewComplete>b__0()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

OS Version:
Microsoft Windows NT 10.0.17134.0 (x64 en-US)

CLR Version:
4.7 or later [Release Number = 461808]

Peak Virtual Memory:
38.1 GB

Private Memory:
523 MB

Peak Working Set:
668 MB

IE Version:
11.706.17134.0

User ID:
5564446e-xxxx-4049-9f51-6d64d53781e0

Workbook Package Info:
1* - en-US, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.

Telemetry Enabled:
True

Model Default Mode:
Empty

Snapshot Trace Logs:
C:\Users\xxxxxx\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot821894869.zip

Enabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_NewWebTableInference
PBI_showIncrementalRefreshPolicy
PBI_showManageAggregations
PBI_improvedFilterExperience
PBI_qnaLiveConnect
PBI_keyDrivers

Disabled Preview Features:
PBI_SpanishLinguisticsEnabled

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

DPI Scale:
100%

Supported Services:
Power BI

 

 

Query1 =

let ftable = (StartDate as date, EndDate as date, Fnumber as number) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate, DayCount, #duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type text),
InsertYearNumber = Table.AddColumn(InsertYear, "YearNumber", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYearNumber, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]),type text),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Month([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each (([Year] * 1000) + ([MonthOfYear] * 100) + [DayOfMonth])) ,
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date],"MMMM"),type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([YearNumber])) ,
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth,"QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([YearNumber]) ),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date]),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date], type date),
InsertNumber = Table.AddColumn(InsertWeekEnding, "WeekNumber", each Date.WeekOfYear([Date]),
InsertMonthnYear = Table.AddColumn(InsertNumber, "MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100),
InsertQuarternYear = Table.AddColumn(InsertMonthnYear, "QuarternYear", each ([Year] * 10000) + ([QuarterOfYear] * 100))
in InsertQuarternYear 

in ftable;

  • Hi Anonymous,

     

    There are issues when you define the type of steps InsertYearNumber and InsertMonth, please change text to number and test again: 

     

    (StartDate as date, EndDate as date, Fnumber as number) as table =>
    
    let
    
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
    Source = List.Dates(StartDate, DayCount, #duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type number),
    InsertYearNumber = Table.AddColumn(InsertYear, "YearNumber", each Date.Year([Date])),
    InsertQuarter = Table.AddColumn(InsertYearNumber, "QuarterOfYear", each Date.QuarterOfYear([Date])),
    InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]),type number),
    InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Month([Date])),
    InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each (([Year] * 1000) + ([MonthOfYear] * 100) + [DayOfMonth])),
    InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date],"MMMM"),type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([YearNumber])),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth,"QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([YearNumber]) ),
    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
    InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", type text)),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date], type date)),
    InsertNumber = Table.AddColumn(InsertWeekEnding, "WeekNumber", each Date.WeekOfYear([Date])),
    InsertMonthnYear = Table.AddColumn(InsertNumber, "MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100),
    InsertQuarternYear = Table.AddColumn(InsertMonthnYear, "QuarternYear", each ([Year] * 10000) + ([QuarterOfYear] * 100))
    
    
    in 
    InsertQuarternYear

     

     

    Best Regards,
    Qiuyun Yu 

2 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support

    Hi Anonymous,

     

    There are issues when you define the type of steps InsertYearNumber and InsertMonth, please change text to number and test again: 

     

    (StartDate as date, EndDate as date, Fnumber as number) as table =>
    
    let
    
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
    Source = List.Dates(StartDate, DayCount, #duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type number),
    InsertYearNumber = Table.AddColumn(InsertYear, "YearNumber", each Date.Year([Date])),
    InsertQuarter = Table.AddColumn(InsertYearNumber, "QuarterOfYear", each Date.QuarterOfYear([Date])),
    InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]),type number),
    InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Month([Date])),
    InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each (([Year] * 1000) + ([MonthOfYear] * 100) + [DayOfMonth])),
    InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date],"MMMM"),type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([YearNumber])),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth,"QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([YearNumber]) ),
    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
    InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", type text)),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date], type date)),
    InsertNumber = Table.AddColumn(InsertWeekEnding, "WeekNumber", each Date.WeekOfYear([Date])),
    InsertMonthnYear = Table.AddColumn(InsertNumber, "MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100),
    InsertQuarternYear = Table.AddColumn(InsertMonthnYear, "QuarternYear", each ([Year] * 10000) + ([QuarterOfYear] * 100))
    
    
    in 
    InsertQuarternYear

     

     

    Best Regards,
    Qiuyun Yu 

  • Anonymous's avatar
    Anonymous
    Not applicable

    Thanks Quiyun Yu. The solution worked. Good day