cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mixylplyx Helper I
Helper I

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
Community Support

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
Community Support

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 Helper I
Helper I

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
Community Support

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors