Scribd
Upload
7 views4 pages

Power Query: Keep First Duplicate, Replace Nulls

This document provides a guide for using Power Query to keep the first instance of duplicate ID + Amount rows, blanking out later duplicates and replacing null values with zero. It includes step-by-step instructions on loading data, using the Advanced Editor, and pasting the necessary M code. The full M code is provided, along with instructions on adjusting the previous step name and loading the final table into Excel.

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
7 views4 pages

Power Query: Keep First Duplicate, Replace Nulls

This document provides a guide for using Power Query to keep the first instance of duplicate ID + Amount rows, blanking out later duplicates and replacing null values with zero. It includes step-by-step instructions on loading data, using the Advanced Editor, and pasting the necessary M code. The full M code is provided, along with instructions on adjusting the previous step name and loading the final table into Excel.

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 Guide: Keeping First Duplicate, Blanking Others, Replacing

Null with Zero

Introduction
This document explains where to paste the M code in Power Query, after which step, and
includes the full M code needed. This will:

- Keep only the FIRST instance of duplicate ID + Amount rows

- Blank out all later duplicates without deleting rows

- Replace blank (null) values with zero

Step 1: Load Data into Power Query


1. Open Excel

2. Go to: Data → Get Data → From Table/Range

3. Load your table (must contain ID and Amount columns)

Step 2: Open Advanced Editor


In Power Query:

Home → Advanced Editor

You will see default code like:

let

Source = [Link]{[Name="Table1"]}[Content],

#"Changed Type" = [Link](Source, {...})

in

#"Changed Type"

Step 3: Where to Paste the M Code


Paste the M code AFTER your last step.
Example:

If your last step is #"Changed Type", then use:

AddIndex = [Link](#"Changed Type", "RowIndex", 1, 1),

Full M Code
Copy and paste this entire block (update #"PreviousStep" to match your query):

```

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

```

Step 4: Adjust the PreviousStep Name


Your last step may be named something like:

- #"Changed Type"

- #"Filtered Rows"
- #"Removed Columns"

Replace:

Source = #"PreviousStep"

with the actual name:

Source = #"Changed Type"

Step 5: Close & Load


After pasting the code:

1. Click Done

2. Click Close & Load

Your table will now:

- Keep the first duplicate

- Blank later duplicates

- Replace null with zero

You might also like