Custom derived Date Columns

[cmajka]

Hi!

Looking for some suggestions on building two custom date columns that show month and year. The problem is that the source data file only has 1 column that relates to date, and the values in that column could be CURRENT MTD or PRIOR MTD. The 'PRIOR MTD' value signifies the Prior Year MTD figure.

These queries will be refreshed on a monthly basis and we can assume that the CURRENT MTD column is for the most recent completed month, i.e., today is 7/17/2018 so the CURRENT MTD column would equal June for month and 2018 for year. In this example the PRIOR MTD would equal June for Month and 2017 for year.

Thanks!

Chris

 

[AliGW]

Sorry - I'm not clear what you are trying to do. What does the end result look like?

 

[cmajka]

I'm trying to create a year column and a month column based on the column in the source table. The problem is that the column in the source table only contains values laballed as 'Current MTD' or 'Prior MTD'. There is no other column or data to identify the month or year.

 

[cmajka]

adding a sample file

adding a sample file

 

[AliGW]

Right - I've got you now. Are you saying that you want the equivalent of TODAY() in Power Query?

Is this any use?

DateTime.Date(DateTime.LocalNow())

 

[cmajka]

that is along the lines of what I was thinking. It almost gets me there. However, it would need to be like month of today -1 mont...if that makes sense. The reason is because we don't receive our data file until after the close of the month.

 

[p45cal]

I know it's too late, just practising:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"LINE OF BUSINESS", type text}, {"METRIC", type text}, {"STATE", type text}, {"PERIOD", type text}, {"COMPANY", type text}, {"Value", Int64.Type}}),
    AddedCustom1 = Table.AddColumn(ChangedType, "aDate", each Date.AddYears(Date.StartOfMonth(DateTime.LocalNow())-#duration(1,0,0,0),if Text.Contains([PERIOD], "CURRENT") then 0 else -1)),
    AddedCustom = Table.AddColumn(AddedCustom1, "Month", each Date.MonthName([aDate])),
    AddedCustom2 = Table.AddColumn(AddedCustom, "Year", each Date.Year([aDate])),
    RemovedColumns = Table.RemoveColumns(AddedCustom2,{"aDate"})
in
    RemovedColumns