Replace certain weeks in months Power BI Query

When you have a special character in column name, then you will need to use [#"Column"]. Hence replace [Extract_Month - Copier] with [#"Extract_Month - Copier"] everywhere in your code.

Hi Vijay_A_Verma ,

I try your solution but I still have a problem as you can see in line number 6 : 

Vijay_A_Verma 

I'm very sorry but your solution don't function with my code :

Please copy your code in text format here by clicking </> in the editor. I will modify the code for you.

You can remove Source line from your code to maintain confindentiality. Once I send you back the code, you can reinsert Source line into the code.

Vijay_A_Verma 

Attached here the source code :

_TSupplyPlan_Histo = Source{[Schema="",Item="TSupplyPlan_Histo"]}[Data],
#"Ajout PN_Plant" = Table.AddColumn(_TSupplyPlan_Histo, "PN_Plant", each [Material] &"_"& [Plant]),
#"Correction orthographe Extract_Month" = Table.RenameColumns(#"Ajout PN_Plant",{{"Extact_Month", "Extract_Month"}}),
#"Duplication de la colonne Extract_Month" = Table.DuplicateColumn(#"Correction orthographe Extract_Month", "Extract_Month", "Extract_Month - Copier"),
#"Type date" = Table.TransformColumnTypes(#"Duplication de la colonne Extract_Month",{{"Extract_Month - Copier", type date}}),
#"Colonnes renommées" = Table.RenameColumns(#"Type date",{{"Extract_Month - Copier", "Extract_Month bis"}})
in
#"Colonnes renommées"

This doesn't contain steps given by me. Please give me the complete code which you showed in the picture.

Vijay_A_Verma ,

_TSupplyPlan_Histo = Source{[Schema="",Item="TSupplyPlan_Histo"]}[Data],
#"Ajout PN_Plant" = Table.AddColumn(_TSupplyPlan_Histo, "PN_Plant", each [Material] &"_"& [Plant]),
#"Correction orthographe Extract_Month" = Table.RenameColumns(#"Ajout PN_Plant",{{"Extact_Month", "Extract_Month"}}),
#"Duplication de la colonne Extract_Month" = Table.DuplicateColumn(#"Correction orthographe Extract_Month", "Extract_Month", #"Extract_Month - Copier"),
FilteredOnDates = Table.SelectRows(#"Duplication de la colonne", each not Text.StartsWith([#"Extract_Month - Copier"], "W")),
Weeks = Table.SelectRows(#"Duplication de la colonne", each Text.StartsWith([#"Extract_Month - Copier"], "W")),
SecondLastDate = List.First(List.LastN(List.Sort(FilteredOnDates[#"Extract_Month - Copier"]),2)),
MinW = List.Min(Weeks[#"Extract_Month - Copier"]),
Custom1 = Table.ReplaceValue(#"Duplication de la colonne",each [#"Extract_Month - Copier"],each if Text.StartsWith([#"Extract_Month - Copier"],"W") then if [#"Extract_Month - Copier"]=MinW then SecondLastDate else null else [Date],Replacer.ReplaceValue,{#"Extract_Month - Copier"}),
#"Filtered Rows" = Table.SelectRows(Custom1, each [#"Extract_Month - Copier"] <> null),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{#"Extract_Month - Copier", type date}})
in
#"Changed Type"

Use this code. Replace Source statement

let
Source = xyz,
_TSupplyPlan_Histo = Source{[Schema="",Item="TSupplyPlan_Histo"]}[Data],
#"Ajout PN_Plant" = Table.AddColumn(_TSupplyPlan_Histo, "PN_Plant", each [Material] &"_"& [Plant]),
#"Correction orthographe Extract_Month" = Table.RenameColumns(#"Ajout PN_Plant",{{"Extact_Month", "Extract_Month"}}),
#"Duplication de la colonne Extract_Month" = Table.DuplicateColumn(#"Correction orthographe Extract_Month", "Extract_Month", "Extract_Month - Copier"),
FilteredOnDates = Table.SelectRows(#"Duplication de la colonne Extract_Month", each not Text.StartsWith([#"Extract_Month - Copier"], "W")),
Weeks = Table.SelectRows(#"Duplication de la colonne Extract_Month", each Text.StartsWith([#"Extract_Month - Copier"], "W")),
SecondLastDate = List.First(List.LastN(List.Sort(FilteredOnDates[#"Extract_Month - Copier"]),2)),
MinW = List.Min(Weeks[#"Extract_Month - Copier"]),
Custom1 = Table.ReplaceValue(#"Duplication de la colonne Extract_Month",each [#"Extract_Month - Copier"],each if Text.StartsWith([#"Extract_Month - Copier"],"W") then if [#"Extract_Month - Copier"]=MinW then SecondLastDate else null else [#"Extract_Month - Copier"],Replacer.ReplaceValue,{"Extract_Month - Copier"}),
#"Filtered Rows" = Table.SelectRows(Custom1, each [#"Extract_Month - Copier"] <> null),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Extract_Month - Copier", type date}})
in
#"Changed Type"

Thanks Vijay_A_Verma ,

But I need to keep all W... in first column and have blank line in the new colomn for the W..

In Query steps pane on right side, delete #"Filtered Rows" step (don't do it in Advanced Editor)

Hi Vijay_A_Verma ,

Finally, to solve my problem, I will need to duplicate the "Extract_Month" column into "Extract_Month bis" by replacing all values starting with a "W" by blank.