Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I have a table, with so many rows and coloumns,
i need to create a calculate coloumn, to segregate "Original and Duplicate entries"
Certain coloumns
GlobalService
DunsNumber
OpportunityID
Opportunity
PipelineOpen_constant
if the above coloumns , for the lines(Rows) are equal, then mark these as duplicate, except 1line(which is original)
that means if there are 3 lines same, mark 2 of them as duplicate and 1 as original.
let me know if this is possible to do and also i want a table to be created for those entries.
Solved! Go to Solution.
Hi @vjnvinod ,
I have created a sample for your reference, please check the following steps as below.
1. Insert an index column in power query. M code for your reference.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSK1YGRSUhkMg4yBUymgsk0pdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [catgeory = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"catgeory", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1)
in
#"Added Index"
2. Close and apply, then wen can get the excepted result by a calculated column.
Column =
VAR inde = 'Table'[Index]
VAR previous = inde - 1
RETURN
IF (
CALCULATE (
MAX ( 'Table'[catgeory] ),
FILTER ( 'Table', 'Table'[Index] = previous )
) = 'Table'[catgeory],
"Duplicated",
"Original"
)
Pbix as attached.
Hi @vjnvinod ,
I have created a sample for your reference, please check the following steps as below.
1. Insert an index column in power query. M code for your reference.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSK1YGRSUhkMg4yBUymgsk0pdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [catgeory = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"catgeory", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1)
in
#"Added Index"
2. Close and apply, then wen can get the excepted result by a calculated column.
Column =
VAR inde = 'Table'[Index]
VAR previous = inde - 1
RETURN
IF (
CALCULATE (
MAX ( 'Table'[catgeory] ),
FILTER ( 'Table', 'Table'[Index] = previous )
) = 'Table'[catgeory],
"Duplicated",
"Original"
)
Pbix as attached.
Hi @vjnvinod ,
This is achievable.
Concatenate =
//you may also use "&" instead of COMBINEVALUES() to concatenate text strings
COMBINEVALUES (
" ",
'Table'[GlobalService],
'Table'[DunsNumber],
'Table'[OpportunityID],
'Table'[Opportunity],
'Table'[PipelineOpen_constant]
)
KeepThisRow = CALCULATE ( MIN ( 'Table'[Index/DateTimeStamp] ), ALLEXCEPT ( 'Table', 'Table'[Concatenate] ) ) = 'Table'[Index/DateTimeStamp]
Another way to do this, you don't want to keep all other rows except the first one based on several columns is do Remove Duplicates in Power Query which is very similar to Excel. In Power Query, select all the columns in your list. Right click and then select Remove Duplicates.
Proud to be a Super User!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
88 | |
82 | |
65 | |
64 | |
58 |
User | Count |
---|---|
171 | |
113 | |
110 | |
73 | |
73 |