Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Happy Friday,
Can anyone suggest me some idea? I want to compare Balance column rows,script should compare same positive and same negative number (Example:Compare postive 100 with -100 and remove those Rows).
Balance |
100 |
200 |
300 |
400 |
-100 |
-200 |
-700 |
Result |
Balance |
300 |
400 |
-700 |
Solved! Go to Solution.
@Anonymous
Try this Calculated table
Please see attached file with your sample data as well
Calculated Table 2 = VAR temp1 = ADDCOLUMNS ( Table2, "ABS", ABS ( Table2[Balance] ) ) VAR temp2 = ADDCOLUMNS ( temp1, "MyFilter", COUNTX ( FILTER ( temp1, [ABS] = EARLIER ( [ABS] ) && [Map] = EARLIER ( [Map] ) ), 1 ) ) VAR temp3 = FILTER ( temp2, [MyFilter] = 1 ) RETURN SELECTCOLUMNS ( temp3, "Balance", [Balance], "Map", [Map] )
I get this result
@Anonymous
With Power Query, you can do something like this
Please see attached file for steps
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Balance", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "ABS", each Number.Abs([Balance])), #"Grouped Rows" = Table.Group(#"Added Custom", {"ABS"}, {{"Countrows", each Table.RowCount(_), type number}, {"ALL", each _, type table}}), #"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"Balance"}, {"Balance"}), #"Filtered Rows" = Table.SelectRows(#"Expanded ALL", each ([Countrows] = 1)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"ABS", "Countrows"}) in #"Removed Columns"
@Anonymous
Also you can use following Calculated Table using DAX
From the Modelling Tab>>New Table
Calculated Table = VAR temp1 = ADDCOLUMNS ( Table1, "ABS", ABS ( Table1[Balance] ) ) VAR temp2 = ADDCOLUMNS ( temp1, "MyFilter", COUNTX ( FILTER ( temp1, [ABS] = EARLIER ( [ABS] ) ), 1 ) ) VAR temp3 = FILTER ( temp2, [MyFilter] = 1 ) RETURN SELECTCOLUMNS ( temp3, "Balance", [Balance] )
Hi Zubair,
I just recieve my dataset and it has duplicate values in Balance Column but i found two columns Sign and Map.
Balance | Sign | Map |
100 | Y | USA |
200 | Y | USA |
300 | Y | China |
400 | Y | China |
-100 | N | USA |
-200 | N | USA |
-700 | N | China |
100 | Y | China |
Balance | Sign | Map | Comments | Result |
100 | Y | USA | Same negative and posituve number and Sign should be Y&N and Region should be same | Delete |
-100 | N | USA | Same negative and posituve number and Sign should be Y&N and Region should be same | Delete |
100 | Y | China | Keep |
@Anonymous
Try this Calculated table
Please see attached file with your sample data as well
Calculated Table 2 = VAR temp1 = ADDCOLUMNS ( Table2, "ABS", ABS ( Table2[Balance] ) ) VAR temp2 = ADDCOLUMNS ( temp1, "MyFilter", COUNTX ( FILTER ( temp1, [ABS] = EARLIER ( [ABS] ) && [Map] = EARLIER ( [Map] ) ), 1 ) ) VAR temp3 = FILTER ( temp2, [MyFilter] = 1 ) RETURN SELECTCOLUMNS ( temp3, "Balance", [Balance], "Map", [Map] )
I get this result
Thank you so much Zubair.You really saved my Days.
User | Count |
---|---|
128 | |
108 | |
99 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |