Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Display only first column value in a table with duplicate values

Good day!

 

I am trying to figure out how to show only the first value of the column. I have a table that contains duplicate values for InvoiceID and InvoiceAmount for each productID. I want to keep the InvoiceID column with all it's values, but the InvoiceAmount should only display the first value, basically I want to get rid of the duplicate value using the measure.

Example:

 

InvoiceID InvoiceAmount productID
A0246605 999999 item0010
A0246605 999999 Item0079
A0246605 999999 Item0081
A0246605 999999 Item0093
A0246605 999999 Item0166
A0246605 999999 Item0191
A0246605 999999 Item0102
A0246605 999999 Item0470
A0246605 999999 Item0001

 

The result  I am looking for:

 

InvoiceID InvoiceAmount productID
A0246605 999999 item0010
A0246605               Item0079
A0246605               Item0081
A0246605             Item0093
A0246605             Item0166
A0246605             Item0191
A0246605             Item0102
A0246605             Item0470
A0246605             Item0001

 

Thanks in advance!

 

Need Help

5 Replies

  • swise001's avatar
    swise001
    Continued Contributor
    5 years ago

    Anonymous 

     

    Here's a solution using Power Query and your sample data table: 

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQwMjEzMzBVsAQDhcyS1FwDA0MDpVgdTElPsKS5JR5JC0M8kpbGuCUNzczwSFriMdbQwAi3pIk5Pq8YAI2NBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"InvoiceID InvoiceAmount productID" = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "InvoiceID InvoiceAmount productID", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"InvoiceID InvoiceAmount productID.1", "InvoiceID InvoiceAmount productID.2", "InvoiceID InvoiceAmount productID.3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"InvoiceID InvoiceAmount productID.1", "InvoiceID"}, {"InvoiceID InvoiceAmount productID.2", "InvoiceAmount"}, {"InvoiceID InvoiceAmount productID.3", "productID"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"InvoiceAmount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"InvoiceID"}, {{"All", each _, type table [InvoiceID=nullable text, InvoiceAmount=nullable number, productID=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "WithIndex", each Table.AddIndexColumn([All],"Index",1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"WithIndex"}),
    #"Expanded WithIndex" = Table.ExpandTableColumn(#"Removed Other Columns", "WithIndex", {"InvoiceID", "InvoiceAmount", "productID", "Index"}, {"InvoiceID", "InvoiceAmount", "productID", "Index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded WithIndex", "Invoice Amount", each if [Index] = 1 then [InvoiceAmount] else ""),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"InvoiceAmount", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"InvoiceID", "Invoice Amount", "productID"})
in
    #"Reordered Columns"

     

    Just Copy Paste this into the Advanced Editor for a new query.  A similar pattern could be applied to your table.   

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , See if these measures can help

     

    sumx(values(Table[InvoiceID]), min(Table[InvoiceAmount]))

    sumx(values(Table[InvoiceID]),Firstnonblankvalue(productID, min(Table[InvoiceAmount])))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you for the quick reply amitchandak

      I checked the suggested measures and both of them returned the same InvoiceAmount for each row:

       

      InvoiceIDproductIDMeasureMeasure 3InvoiceAmount
      A0246605item0010999999 999999 999999 
      A0246605Item0079999999 999999 999999 
      A0246605Item0081999999 999999 999999 
      A0246605Item0093999999 999999 999999 
      A0246605Item0166999999 999999 999999 
      A0246605Item0191999999 999999 999999 
      A0246605Item0102999999 999999 999999 
      A0246605Item0470999999 999999 999999 
      A0246605Item0001999999 999999 999999 

       

      Measure "Measure" - sumx(values(Table[InvoiceID]), min(Table[InvoiceAmount]))

      Measure "Measure 3" - sumx(values(Table[InvoiceID]),Firstnonblankvalue(productID, min(Table[InvoiceAmount])))

       

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    5 years ago

    hi  Anonymous 

    If you want to create a measure, just try this measure

    Measrue = 
var _firstrow=CALCULATE(MIN('Table'[productID]),ALLEXCEPT('Table','Table'[InvoiceID],'Table'[InvoiceAmount])) 
return
CALCULATE(MIN('Table'[InvoiceAmount]),FILTER('Table','Table'[productID]=_firstrow))

    Result:

    and here is sample pbix file, please try it.

     

    Regards,

    Lin