Calculated Column removing out repeated values

[Abdel_BCN]

Hi, 

I hope someone can help me with the following problem:

I have two columns from a calculated table.

I'd like to have a third calculated column with DAX that only takes one value % Percent from each Code, the expected table is 

I've tried with creating Index to compare each row with the previouse but I don't actually have a context to create the index with.

Many thanks!

[v-xinruzhu-msft]

Hi @Abdel_BCN 

1.You can handle it in power query.

Put the following code to Advanced Editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WijA0VNJRMjbRM7BQVYrVIU7ACChgaGqgZ25AiogxSMTQVM/QgkiRWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, #"Percent%" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Percent%", Percentage.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Code"}, {{"Count", each _, type table [Code=nullable text, #"Percent%"=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Rank",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Code", "Percent%", "Rank"}, {"Code.1", "Percent%", "Rank"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "Custom", each if [Rank] = 1 then [#"Percent%"] else null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Custom", Percentage.Type}, {"Rank", Int64.Type}})
in
    #"Changed Type1"

Output

2.You can create a index column in power query

Then create a calculated column

Column = var a=COUNTROWS(FILTER('Table (2)',[Code]<>EARLIER('Table (2)'[Code])&&[Index]<=EARLIER('Table (2)'[Index])))
return IF([Index]-a=1,[Percent%])

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Abdel_BCN]

Hi @v-xinruzhu-msft ,

thanks for your reply and the effort you made.

the thing is that this table is a calculated table and can't work with it using power query.

not sure if there is away on adding the index column using DAX.

Many thanks,

my best regards