Replace certain weeks in months Power BI Query

[PBIBeginner2022]

Hi everyone,

I have a column in Power BI Query with dates and weeks in text format, like this : 

I want to create the same column with date format BUT I want delete the weeks (Wxx) and replace the smallest W (for exemple W24) by the second most recent date. For exemple, I want replace W24 by 2022.06. Moreover I need a solution which is automatically because new datas is comming every weeks with the extractions.

Thanks in advance for any help !

[Vijay_A_Verma]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc5LCsMwDATQu2gdgi3n12XS5tN1FgUb3/8a1UggWu+GhzRSKbRSRxw49mGRtEaqXaHN8QHkf4wBmBqMkjZbfzoy0NZfhtwHnbT13VEnB8XDMQHHBgdJx9TgKOlcGpwk3fbS6TgD2fHD6LvTD6Ar2yuXAnqyXXwroCPPVOsX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Date = _t, Column2 = _t]),
    FilteredOnDates = Table.SelectRows(Source, each not Text.StartsWith([Date], "W")),
    Weeks = Table.SelectRows(Source, each Text.StartsWith([Date], "W")),
    SecondLastDate = List.First(List.LastN(List.Sort(FilteredOnDates[Date]),2)),
    MinW = List.Min(List.Sort(Weeks[Date])),
    Custom1 = Table.ReplaceValue(Source,each [Date],each if Text.StartsWith([Date],"W") then if [Date]=MinW then SecondLastDate else null else [Date],Replacer.ReplaceValue,{"Date"}),
    #"Filtered Rows" = Table.SelectRows(Custom1, each [Date] <> null),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date", type date}})
in
    #"Changed Type"

[PBIBeginner2022]

Thanks for your answer @Vijay_A_Verma ,

I have a problem with your solution. When I copy/paste your code I loose the data of my table. I need to add your code to the existing code of my table.

[Vijay_A_Verma]

After source line, delete all lines in your code and paste following. Replace Date with your column name.

FilteredOnDates = Table.SelectRows(Source, each not Text.StartsWith([Date], "W")),
    Weeks = Table.SelectRows(Source, each Text.StartsWith([Date], "W")),
    SecondLastDate = List.First(List.LastN(List.Sort(FilteredOnDates[Date]),2)),
    MinW = List.Min(Weeks[Date]),
    Custom1 = Table.ReplaceValue(Source,each [Date],each if Text.StartsWith([Date],"W") then if [Date]=MinW then SecondLastDate else null else [Date],Replacer.ReplaceValue,{"Date"}),
    #"Filtered Rows" = Table.SelectRows(Custom1, each [Date] <> null),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date", type date}})
in
    #"Changed Type"

[PBIBeginner2022]

@Vijay_A_Verma 

I already have step on my table. I can't use your code in my table to add the 3 extra columns you made for my source table

[Vijay_A_Verma]

You can fit in my code after last step of your code. Just replace Date with your Date column and in first line of my code i.e. FilteredOnDates replace Source with your last step. No other column will be coming into your code. That was just for illustration purpose.

Otherwise, if you need help, paste your code here and mention what column is your Date column where you want this operation.

[PBIBeginner2022]

[Vijay_A_Verma · Accepted solution]

Remove last 2 lines in your code i.e. 

in
#"Duplication de la colonne"

Put a comma at the end of line #"Correction orthographe Extract_Month"

Paste following code after #"Correction orthographe Extract_Month" line

    FilteredOnDates = Table.SelectRows(#"Duplication de la colonne", each not Text.StartsWith([Extract_Month], "W")),
    Weeks = Table.SelectRows(#"Duplication de la colonne", each Text.StartsWith([Extract_Month], "W")),
    SecondLastDate = List.First(List.LastN(List.Sort(FilteredOnDates[Extract_Month]),2)),
    MinW = List.Min(Weeks[Extract_Month]),
    Custom1 = Table.ReplaceValue(#"Duplication de la colonne",each [Extract_Month],each if Text.StartsWith([Extract_Month],"W") then if [Extract_Month]=MinW then SecondLastDate else null else [Date],Replacer.ReplaceValue,{"Extract_Month"}),
    #"Filtered Rows" = Table.SelectRows(Custom1, each [Extract_Month] <> null),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Extract_Month", type date}})
in
    #"Changed Type"

[PBIBeginner2022]

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]

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.

[PBIBeginner2022]

@Vijay_A_Verma 

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

[Vijay_A_Verma]

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.

[PBIBeginner2022]

@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"

[Vijay_A_Verma]

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

[PBIBeginner2022]

@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"

[Vijay_A_Verma]

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"

[PBIBeginner2022]

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..

[Vijay_A_Verma]

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

[PBIBeginner2022]

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.