Row duplication and updation

[Mohan1005]

Hi,

I need help in power query to achieve the following scenario. if we are comparing current month id's with previous months id's and if some id's are not present in the current month and present in previous month i need these records to be inserted as duplicates in the current month with updation of month column.

please find the tables for better understanding.

Existing Table
Month	Year	ID
4	2019	1
4	2019	2
5	2019	1
5	2019	3
6	2019	4
6	2019	5

Updated Table
Month	Year	ID
4	2019	1
4	2019	2
5	2019	1
5	2019	2
5	2019	3
6	2019	1
6	2019	2
6	2019	3
6	2019	4
6	2019	5

Thanks and Regards in advance

[Zubair_Muhammad · Accepted solution]

@Mohan1005 

Please try this one

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlHSUTIyMLQEUoZKsTooAkZgAVN0FUgCxmABM4SACbqAqVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Month = _t, Year = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"Year", Int64.Type}, {"ID", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let myIDs=Table.SelectRows(#"Changed Type",(x)=>(x)[Month]<=[Month] 
and (x)[Year]<=[Year])[ID]
in {List.Min(myIDs)..List.Max(myIDs)}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ID"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"Month", "Year"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Duplicates", "Custom")
in
    #"Expanded Custom"

[Mohan1005]

Hi Zubair,

Great Work. The solution provided from your end is working fine with the integer column. But if i'm trying the same with Text column this cant be applied. 
kindly guide me through a solution to over come this barrier.

please find the sample data for the same.

Year	Month	Project Id	Yes/No	YTD Yes/No
2019	4	836-01	1	1
2019	4	4396-01	0	0
2019	4	4658-01	0	0
2019	5	4658-01	1	0
2019	5	4960-01	1	1
2019	5	5086-36	NULL	1
2019	6	5086-36	NULL	1
2019	6	594-01	1	1
2019	6	597-01	1	1

Thanks and Regards in advance