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

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

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