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

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's avatar
PBIBeginner2022
Icon for Helper III rankHelper III
4 years ago

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