Forum Discussion
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) and replace the smallest W (for exemple W24) by the second most recent date. For exemple, I want replace W24 by 2022.06. Moreover I need a solution which is automatically because new datas is comming every weeks with the extractions.
Thanks in advance for any help !
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"
18 Replies
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"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.
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"
