Forum Discussion

PBIBeginner2022's avatar
PBIBeginner2022
Icon for Helper III rankHelper 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...
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • 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"
Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

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.

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    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"
    • PBIBeginner2022's avatar
      PBIBeginner2022
      Icon for Helper III rankHelper III
      4 years ago

      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's avatar
        Vijay_A_Verma
        Icon for Most Valuable Professional rankMost Valuable Professional
        4 years ago

        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.