cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Mixylplyx Regular Visitor
Regular Visitor

Guesstimate month on legacy data

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Guesstimate month on legacy data

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"

354.PNG

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.

 

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Guesstimate month on legacy data

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.

Mixylplyx Regular Visitor
Regular Visitor

Re: Guesstimate month on legacy data

The legacy data comes in like  this:

IDApproved MonthApproved Year
154null2018
453null2019
865null2020
42null2018
664null2019
632null2020
143null2018
113null2019
694null2020
875null2018
544null2019
14null2020

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.

Community Support Team
Community Support Team

Re: Guesstimate month on legacy data

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"

354.PNG

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.

 

View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors