Forum Discussion

PBIBeginner2022's avatar
PBIBeginner2022
Helper III
4 years ago
Solved

Replace certain weeks in months Power BI Query

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) a...
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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"