Forum Discussion
Solved
Row duplication and updation
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
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"
2 Replies
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"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
