Guesstimate month on legacy data

[Anonymous]

I am working with bad legacy data and trying to merge it with (relatively) good data. One of the fields is Approved Date. In the legacy data, this is null, but we have it in the recent data. I can get the year from another column, but the only way to get a month is take the sequential IDs of the legacy data and group them into 12 buckets in ascending order of ID. Ultimately, the legacy and recent data need to have both year and month values for comparisons elswhere. What's the best way to do this?

TIA

[dax]

Hi Mixylplyx,

If possible, could you please inform me your legacy and good data table, and your expecting outputs? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

The legacy data comes in like  this:

ID	Approved Month	Approved Year
154	null	2018
453	null	2019
865	null	2020
42	null	2018
664	null	2019
632	null	2020
143	null	2018
113	null	2019
694	null	2020
875	null	2018
544	null	2019
14	null	2020

I need to come up with the best guess of which month these entries came in. The IDs are given out sequentially as they come in. So my plan is to take the count of rows by year, divde by 12, then assign a month accordingly. So, if there are 600 entries for a year, then the first 50 are assigned to the first month, 51-100 are assigned to the second month, and so on. Each year can have a different number of entries. The ids may not be perfectly sequential either, meaning that IDs 1-13 are there, 14 is not, then 15-29, then 30 is not. However, I do know that a higher numbered ID came in later than a lower numbered ID.

[dax · Accepted solution]

Hi Mixylplyx,

It seems that you want to create month based on year and index, right? If so, you could try to achieve this by M code like below(I devide 2 , you could replace this by 12 in your sample)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc67DYAwDEXRXVyniL84s1jZIKJjfxA0IF5l6co6elXEbtRoP9a6jnROmq3IXD913DXD31X68ysAiDAAhAoA2BQIzGhCDANCbg4EN7SBf8A8AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"Approved Month" = _t, #"Approved Year" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Approved Month", type text}, {"Approved Year", Int64.Type}}),
    #"sort table"=Table.Sort( #"Changed Type",{{"Approved Year",Order.Ascending},{"ID",Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"sort table", "Index", 1, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Approved Year"}, {{"group", each _, type table [ID=number, Approved Month=text, Approved Year=number, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([group],"subindex",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"ID", "subindex"}, {"Custom.ID", "Custom.subindex"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each Number.Round([Custom.subindex]/2,0,RoundingMode.Up)),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Custom", "month"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"group", "Custom.subindex"})
in
    #"Removed Columns"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.