Forum Discussion
Missing Dates
I have two date columns, Report Date and Detach Date. I have some missing Detach Dates (filled with null). The Report Date column does not have any missing dates. Is there a function in M Language that will populate the missing dates. Here is a sample of the data. The null values need to have the actual detach date (The report date of 7/27/2015 has a Detach Date of 5/21/2020) and the null vlaue should have 5/21/2020 in the cells.
The data set I am working on have several thousand rows of data and I am missing thousands of the detach dates.
Can some one help?
Report Date DetachDate
| 7/27/2015 | null |
| 7/27/2015 | null |
| 7/27/2015 | null |
| 7/27/2015 | null |
| 7/27/2015 | null |
| 7/27/2015 | null |
| 7/27/2015 | null |
| 7/27/2015 | null |
| 7/27/2015 | 5/21/2020 |
| 7/27/2015 | null |
| 7/27/2015 | null |
| 7/27/2015 | 5/21/2020 |
| 7/27/2015 | 5/21/2020 |
| 7/27/2015 | null |
| 7/27/2015 | 5/21/2020 |
| 7/27/2015 | 5/21/2020 |
| 7/27/2015 | 5/21/2020 |
| 4/6/2018 | null |
| 4/6/2018 | null |
| 4/6/2018 | null |
| 4/6/2018 | null |
| 4/6/2018 | null |
| 4/6/2018 | null |
| 4/6/2018 | null |
| 4/6/2018 | null |
| 4/6/2018 | null |
| 4/6/2018 | null |
| 4/6/2018 | null |
| 4/6/2018 | null |
| 4/6/2018 | 4/2/2021 |
| 4/6/2018 | 4/2/2021 |
Hi BennyA ,
Consider using Fill up if the last record of each group [DetachDate] is not the null.
In other cases, consider grouping [report date] first and then using conditional judgment to replace null.
= Table.Group(YourPreviousStepName, {"Report Date"}, {{"DetachDate", each List.Transform([DetachDate],(x) => if x = null then List.Max([DetachDate]) else x )}})All codes in Adavance Editor.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc3AiIDQ1MlHaW80pwcpVidQS9oqm9kCGQbGdDEDBJNJ8cgVBkTfTOQhAWyDSNEzETfCBQOhrjFYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Report Date" = _t, DetachDate = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Report Date", type date}, {"DetachDate", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Report Date"}, {{"DetachDate", each List.Transform([DetachDate],(x) => if x = null then List.Max([DetachDate]) else x )}}), #"Expanded DetachDate" = Table.ExpandListColumn(#"Grouped Rows", "DetachDate") in #"Expanded DetachDate"Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
2 Replies
Hi BennyA ,
Consider using Fill up if the last record of each group [DetachDate] is not the null.
In other cases, consider grouping [report date] first and then using conditional judgment to replace null.
= Table.Group(YourPreviousStepName, {"Report Date"}, {{"DetachDate", each List.Transform([DetachDate],(x) => if x = null then List.Max([DetachDate]) else x )}})All codes in Adavance Editor.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc3AiIDQ1MlHaW80pwcpVidQS9oqm9kCGQbGdDEDBJNJ8cgVBkTfTOQhAWyDSNEzETfCBQOhrjFYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Report Date" = _t, DetachDate = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Report Date", type date}, {"DetachDate", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Report Date"}, {{"DetachDate", each List.Transform([DetachDate],(x) => if x = null then List.Max([DetachDate]) else x )}}), #"Expanded DetachDate" = Table.ExpandListColumn(#"Grouped Rows", "DetachDate") in #"Expanded DetachDate"Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
