Forum Discussion

BBIUser's avatar
BBIUser
Helper IV
3 years ago

Custom column formula for a Power BI report

There is an existing Power BI Query which is used\source for a Power BI report. Custom Column  if [Session Admin]= "Not assigned Session" then 0 else [Quantity] * 95   How does the formula change...
Need Help
Tips and Tricks
Tutorial Requests
BBIUser's avatar
BBIUser
Helper IV
3 years ago

ImkeF Mahesh0016 Thanks for your responses!

I get the same error and the formula did not work

if [Session Admin]= "Not assigned Session" then 0 else [Quantity] * (if [Date] > #date(2021,6,30) then 100 else 95)

Or

Table.AddColumn(#"Renamed Columns", "Amount", each

if [Session Admin]= "Not assigned Session" then 0 else [Quantity] * (if [Date] > #date(2021,6,30) then 100 else 95) --> Here the only difference between Mahesh formula and mine is I have 'Renamed columns' instead of 'Changed Type'

 

Expression.Error: The Date operation failed because the resulting value falls outside the range of allowed values.

 

The error says outside the range but I have data unitl 11/30/2022 and all records displays error. Below are some screenshots. Is it somethign to do with date and time format???

 

     

 

Please let me know if you need any more info.

Thanks!

 

 

jennratten's avatar
jennratten
Super User
3 years ago

Hello - it looks like your date column may have varying formats.  Please try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc30jcyMDJUMDSyMjAAIgVHXyUdpaDM5AyF8MScktQiIM8IhIGKdA0NlWJ1opUs9Q0tsenyyy9RSCwuzkzPS01RCE4FsvLzgMKmcO1GYO1GUO1GeC21gOgy0jWAWApzAFBdflJmnkIwUGEVkGcJV2eEpM4It3PM4BqMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Session Admin" = _t, Quantity = _t, Column1 = _t]),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(Source, {{"Date", each Text.BeforeDelimiter(_, " "), type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter",{{"Date", type date}, {"Quantity", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each 
if [Session Admin] = "Not assigned Session" then 0 else 
[Quantity] * ( if Date.From([Date]) > #date(2021,6,30) then 100 else 95 ) 
)
in
    #"Added Custom"