Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
nathan_kul
New Member

Custom column of non duplicates

Say I have a column of ID values in Power BI with many duplicates for each ID value. If I wanted to make a custom column where each ID only appears once and duplicates will have Blank() value. How would I code that?

3 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Hi @nathan_kul ,

 

Please refer to the following steps. You can adjust the Source step to match your actual data source.

let
    Source = Table.FromRecords({[ID = 1], [ID = 2], [ID = 2], [ID = 3], [ID = 3], [ID = 3]}),
    #"Grouped Rows" = Table.Group(Source, {"ID"}, {{"Data", each Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type)}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Index"}, {"Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "ID2", each if [Index]=0 then [ID] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

View solution in original post

dufoq3
Super User
Super User

Hi @nathan_kul, different approach here:
I've used @Anonymous sample data.

 

Output

dufoq3_0-1725545768792.png

 

let
    Source = Table.FromRecords({[ID = 1], [ID = 2], [ID = 2], [ID = 3], [ID = 3], [ID = 3]}),
    GroupedRows = Table.Group(Source, {"ID"}, {{"All", each Table.FromColumns(Table.ToColumns(_) & {{[ID]{0}?}}, Value.Type(_ & #table(type table[Unique ID=Int64.Type],{}))), type table}}),
    Combined = Table.Combine(GroupedRows[All])
in
    Combined

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

Omid_Motamedise
Super User
Super User

lovely question, use this formula to reach this result

Omid_Motamedise_0-1725577829767.png

 

 

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if List.Contains(List.FirstN(Source[ID],[Index]),[ID]) then "" else [ID])
in
    #"Added Custom"

 

 

If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h

View solution in original post

3 REPLIES 3
Omid_Motamedise
Super User
Super User

lovely question, use this formula to reach this result

Omid_Motamedise_0-1725577829767.png

 

 

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if List.Contains(List.FirstN(Source[ID],[Index]),[ID]) then "" else [ID])
in
    #"Added Custom"

 

 

If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h
dufoq3
Super User
Super User

Hi @nathan_kul, different approach here:
I've used @Anonymous sample data.

 

Output

dufoq3_0-1725545768792.png

 

let
    Source = Table.FromRecords({[ID = 1], [ID = 2], [ID = 2], [ID = 3], [ID = 3], [ID = 3]}),
    GroupedRows = Table.Group(Source, {"ID"}, {{"All", each Table.FromColumns(Table.ToColumns(_) & {{[ID]{0}?}}, Value.Type(_ & #table(type table[Unique ID=Int64.Type],{}))), type table}}),
    Combined = Table.Combine(GroupedRows[All])
in
    Combined

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Anonymous
Not applicable

Hi @nathan_kul ,

 

Please refer to the following steps. You can adjust the Source step to match your actual data source.

let
    Source = Table.FromRecords({[ID = 1], [ID = 2], [ID = 2], [ID = 3], [ID = 3], [ID = 3]}),
    #"Grouped Rows" = Table.Group(Source, {"ID"}, {{"Data", each Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type)}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Index"}, {"Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "ID2", each if [Index]=0 then [ID] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors