cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Based on duplicated value, replace valus in another column

BATCHPO
15
#5
24
36

 

Hello, 

I'm looking for the solution how to replace the value (#) in one column (BATCH) with the another value (1) from the same column based on duplicated value (5) in another column (PO). 

Here is what i want to get: 

BATCHPO
15
15
24
3

4

 

thank you in advance for any tip, 

Maria

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Memorable Member
Memorable Member

Re: Based on duplicated value, replace valus in another column

given that the AIB answer is correct, if you are better off with the PBI GUI, these are the steps you can follow to get the result:

 

 

image.png

 

 

image.png

 

order before column2 acsending, then column1 descending

 

image.png

 

finally, fill down column1

 

 

image.png

 

image.png

View solution in original post

3 REPLIES 3
Highlighted
Super User III
Super User III

Re: Based on duplicated value, replace valus in another column

Hi @ShiMaria 

Place this code in a blank query to see the steps. The #"Added Custom" step is the important one:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJVitWJVlKGs4yALBMwyxjIMlOKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BATCH = _t, PO = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"BATCH", type text}, {"PO", Int64.Type}}),
    
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [BATCH] = "#" then List.Max(Table.SelectRows(#"Changed Type", (inner)=> inner[PO]=[PO] and (inner)[BATCH]<>"#")[BATCH])  else [BATCH]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"BATCH"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "BATCH"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"BATCH", "PO"})
in
    #"Reordered Columns"

 

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

Highlighted
Frequent Visitor

Re: Based on duplicated value, replace valus in another column

may be I incert it wrongly, but it doesn't make any difference to my tables.. 

Highlighted
Memorable Member
Memorable Member

Re: Based on duplicated value, replace valus in another column

given that the AIB answer is correct, if you are better off with the PBI GUI, these are the steps you can follow to get the result:

 

 

image.png

 

 

image.png

 

order before column2 acsending, then column1 descending

 

image.png

 

finally, fill down column1

 

 

image.png

 

image.png

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors