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 presen...
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"
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