Add column with sixth previous month

[PBIBeginner2022]

Hi everyone,

I need to add a new custom column with sixth previous month in Power Query. I thinked to use that formula but it don't function :

= Table.AddColumn(#"Type modifié", "Extract_month M-6", each if Date.IsInPreviousNMonths([Extract_Month bis], -6) then [Extract_Month bis] else null)

Could you help me please.

Thanks in advance for any help

[JamesRobson]

Hi,

 Add a new step with the following (amend as per column headers)

= Table.AddColumn(#"Changed Type", "6months", each Date.AddMonths([Date],-6))

Thanks,

[jennratten]

Hello - please post an example of your data and the response you are getting after applying your formula.

[PBIBeginner2022]

Thanks @jennratten  for your answer,

I want this in Power Query :

Extract_Month bis	Extract_Month -6
juil-22	null
juil-22	null
juil-22	null
juil-22	null
juin-22	null
juin-22	null
juin-22	null
mai-22	null
avr-22	null
mars-22	null
mars-22	null
févr-22	févr-22
févr-22	févr-22
févr-22	févr-22
févr-22	févr-22
févr-22	févr-22
janv-22	null
déc-21	null
nov-21	null
nov-21	null
nov-21	null
nov-21	null

I have no result with my formula above

[jennratten]

Here you are...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyirNzNE1MlKK1SGanUeInZuYCWMmlhUhRIuKsbHTDq9EKCKHk5WYVwZjpxxemaxrZAhm5+WXEceMBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Extract_Month bis" = _t]),
    ExtractMonth6= Table.AddColumn ( Source, "Extract_Month -6", let varMonths = List.Distinct ( Source[Extract_Month bis] ) in each if [Extract_Month bis] = varMonths{5} then [Extract_Month bis] else null )
in
    ExtractMonth6

[PBIBeginner2022]

@jennratten thanks for your answer,

But I would just add a new custom column in my table which have already lot of column .... I can't use this code I think.

[jennratten]

Hello again - the solution above creates a table that matches your post "I want this in Power Query".  If you are wanting something different, can you please describe the expected results?  

[PBIBeginner2022]

@jennratten 

I have table with lots of column like "Extract_ Month bis". I want added a new column called "Extract_month -6" with the same result that I send you above.

[jennratten]

Hello - the result you are requesting matches the outcome of the solution provided.  I have shown them side-by-side below.