Custom column formula for a Power BI report

[BBIUser]

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 if I have to consider date and month? say for example, 

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

It is throwing Error if I use [Date] > 6/30/2021.

I want to actually use the 'Date' column from the screenshot.

Also, not sure if I need to enter the 12:00:00 AM time in the formula that is displayed as part of the 'Date' column.

Appreciated your help!

Thanks!

[Mahesh0016]

[ImkeF]

Hi @BBIUser ,
please try the following syntax instead:
if [Session Admin]= "Not assigned Session" then 0 else [Quantity] * (if [Date] > #date(2012,6,30) then 100 else 95)

[BBIUser]

@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]

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"

[BBIUser]

@jennratten Appreciate your response.

May be this screenshot should help to understand more.

The custom column is within a table's applied steps. So, the formula that you provided is not working in this custom column.

Example: 

let
    Source = Table.FromRows

is changing to something like this with error 

Thanks for your help!

[jennratten]

Hello -

Can you please try creating a new blank query and open the Advanced Editor (button on the ribbon).  The replace the entire contents of the Advanced Editor with this script below?  After you do this, in the formula bar, you will see the portion of the script below that is outlined in RED will be gone.

It will look like this:

Script to replace all contents in the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc30jcyMDJUMDSyMjAAIgVHXyUdpaDM5AyF8MScktQiIM9IKVYnWslS39ASm1q//BKFxOLizPS81BSF4FQgKz8PKGwK1mQE1WSE1wILiFqg2bqGhiDZ/KTMPIVgoHQVkGeJJGuE20IzpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Session Admin" = _t, Quantity = _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"

Explanation:

Based on the screensnip below that you provided, it appeared as though the date/time values in your column were inconsistently formatted, with some being date/time and some being a date only as yyyy-mm.  Please let me know if this assumption is incorrect and if so, can you please provide more information about the date values shown in your screenship with the yyyy-mm values?

Based on this assumption, the script I provided actually includes three new steps.  These can be consolidated but I left them separate so they could be easily understood. 

The script begins with the date column being formatted as text.  It is important to begin this way if the values in the column have varying formats. 

A step is then added to extract the portion of text that appears before the space, which leaves only the date (from both formats) and omits the time. 

The proper types are then applied.  

The new column can then be added, using the transformed dates.

[BBIUser]

@jennratten I am not a coding person here. So I apologize for this long converstions.

The below should answer your question about yyyy-mm column which is part of "Custom1" step and also will give you the full picture of the model.

I have around 14 tables in the model. The "Amount" I am trying to change under 'Custom' within the script is in one of this table . That table name is 'All Time'. Below is the entire 'All time' script from Advance Editor. 

let
    Source = Table.Combine({tblTimeD1, tblTimeD_FY20, tblFY21, TimeDFY22, TimeDFY23}),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Task", "Fund", "Appr", "Bud", "Project"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns",{"Activity"},qryAllProgs,{"Activity"},"tblProgs",JoinKind.LeftOuter),
    #"Expanded tblPrograms" = Table.ExpandTableColumn(#"Merged Queries", "tblProgs", {"Descr", "Accnt Descr", "Session Admin", "Appn", "Bud ID", "Bud Descr"}, {"tblProgs.Descr", "tblProgs.Account Descr", "tblProgs.Session Admin", "tblProgs.Appn", "tblProgs.Bud ID", "tblProgs.Bud Descr"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded tblPrograms",{{"tblProgs.Session Admin", "Session Administrator"}}),

    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Charged Amount", each if [Session Administrator]= "Not assigned Session" then 0 else [Quantity] * 95),

    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Year-Month", each "" & Number.ToText(Date.Year([Date])) & "-" & Text.PadStart(Number.ToText(Date.Month([Date])), 2, "0") & ""),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Fiscal Year", each if [Date] <= #datetime(2019, 6, 30, 0, 0, 0) then "FY19" else if [Date] <= #datetime(2020, 6, 30, 0, 0, 0) then "FY20" else if [Date] <= #datetime(2021, 6, 30, 0, 0, 0) then "FY21" else if [Date] <= #datetime(2022, 6, 30, 0, 0, 0) then "FY22" else if [Date] <= #datetime(2023, 6, 30, 0, 0, 0) then "FY23" else "FY??"),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom2",{{"Charged Amnt", Currency.Type}})
in
    #"Changed Type"

So, how do I insert your script in my script above to make it work?

Should I still create a new blank query and write a script? Thanks!

[jennratten]

Hello again - if you add the sample script to a blank query you will see a complete working example and it will give you a deeper understanding.  That being said, to integrate this solution into your script, please do the following:

Replace this part of your script:

   #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Charged Amount", each if [Session Administrator]= "Not assigned Session" then 0 else [Quantity] * 95),

With this:

    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Renamed Columns", {{"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", "Charged Amount", each 
if [Session Administrator] = "Not assigned Session" then 0 else 
[Quantity] * ( if Date.From([Date]) > #date(2021,6,30) then 100 else 95 ) 
),

If this results in an error, please do the following:  Go to the Renamed Columns step of your query.  Look at the the values in the date column.  Do any have errors?  Are they all formatted the same way? Click in the space in a cell that has an error.  This will display the error message.  What is the error message?