Scribd
Upload
3 views2 pages

Power Query M: Handle Duplicates & Nulls

The document provides Power Query (M) code to manage duplicate entries in a dataset by keeping the first instance of duplicates while blanking the rest. It also replaces any null values with zero and removes a helper column used for processing. The code includes steps for adding an index, grouping data, transforming columns, and cleaning the final output.

Uploaded by

Dinesh Singh
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
3 views2 pages

Power Query M: Handle Duplicates & Nulls

The document provides Power Query (M) code to manage duplicate entries in a dataset by keeping the first instance of duplicates while blanking the rest. It also replaces any null values with zero and removes a helper column used for processing. The code includes steps for adding an index, grouping data, transforming columns, and cleaning the final output.

Uploaded by

Dinesh Singh
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Power Query M Code: Keep First Duplicate and Replace Null with Zero

Here is the Power Query (M) code that keeps the first instance of duplicates, blanks the rest,
and replaces null with zero:

```
let
Source = #"PreviousStep",

// Add row index to preserve original order


AddIndex = [Link](Source, "RowIndex", 1, 1),

// Group by ID + Amount and add occurrence number


GroupAddIndex =
[Link](AddIndex, {"ID","Amount"}, {
{"Data", (t)=> [Link](t, "Occ", 1, 1), type table}
}),

// Expand back
Expand = [Link](
GroupAddIndex, "Data",
{"ID","Amount","RowIndex","Occ"}
),

// Keep first duplicate, blank others


BlankDuplicates =
[Link](
Expand,
{
{"ID", each if [Occ] = 1 then _ else null},
{"Amount", each if [Occ] = 1 then _ else null}
}
),

// Replace nulls with 0


ReplaceNulls =
[Link](BlankDuplicates, null, 0, [Link], {"ID","Amount"}),

// Remove helper column


Clean = [Link](ReplaceNulls, {"Occ"})
in
Clean
```

You might also like