Forum Discussion
Custom column formula for a Power BI report
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!
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?