Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |