Forum Discussion
PBIBeginner2022
4 years agoHelper III
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...
- 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
4 years agoMost Valuable Professional
In Query steps pane on right side, delete #"Filtered Rows" step (don't do it in Advanced Editor)
PBIBeginner2022
4 years agoHelper III
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.