Date transformation

Power Query
1

Dear Colleagues,

In the excel table I have imported in powerBI, I have 2 columns in my transformation view - Month (Oct) and Year (2022). However, they both are either text or numbers. I would like to create a custom column using query editor/transformation steps which should show the date in date format as 31.10.2022 - so basically the last date of the month and year combination.

Can you please guide me what steps should I follow to achieve this?

Thank you in advance,
Nimish

2

Hi @a_nimish,

Give something like this a go:
Note you can copy the full code into a New Blank query.

let
    Source = Table.FromColumns(
        {
            {"Oct"},
            {2022}
        },  type table [Month = text, Year = Int64.Type]
    ),
    EOM = Table.AddColumn( Source, "Custom", each 
        Date.EndOfMonth( 
            Date.FromText( [Month] & Number.ToText([Year]) )
        )
    )
in
    EOM

.
It returns this result.

image

I hope this is helpful.