CODEWITHSUNDEEP
datepowerbipowerquery
Frank
Frank

Reputation: 55

Add a column with the month of today as 'MMM-YY' in Power BI Power Query

I created a table manually in Power BI. To this table I want to add a column containing the month of today in the format 'MMM-YY'. So if today's date is 08/29/2023 then I want the result to be 'Aug-23'.

I've tried adding one column, named Expo Date Month, where I want to get the result 'Aug-23' like:

let
    Source = Table.FromRows(... in type table [#"Hedge Name" = _t, Contract = _t, Position = _t, #"Formula type" = _t),
    #"Added Custom" = Table.AddColumn(Source, "Expo Date Month", each DateTime.ToText(DateTime.LocalNow(), [Format="MMM-yy"]))
in
    #"Added Custom"

This gives me Expression Error: We cannot convert the value of type Record to type Text

Does somebody know what's going wrong?

Upvotes: 1

Views: 1115

Answers (2)

Frank
Frank

Reputation: 55

For me the answer from horseyride didn't work, but I managed to get a somewhat cumbersome way to achieve it like:

#"Added Custom" = Table.AddColumn(Source, "Expo Date Year", each Date.Year(DateTime.LocalNow())),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Expo Date Year", type text}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type", "Expo Date Month", each Text.Start(Date.MonthName(DateTime.LocalNow()),3)& "-" &Text.End([Expo Date Year],2))

Hopefully it helps someone!

Upvotes: 1

horseyride
horseyride

Reputation: 21373

Add column, custom column, with formula

= DateTime.ToText(DateTime.LocalNow(), [Format="MMM-yy"])

sample full code:

#"Added Custom" = Table.AddColumn(#"PriorStepNameHere, "monthyear", each DateTime.ToText(DateTime.LocalNow(), [Format="MMM-yy"]))

Upvotes: 1

Related Questions