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 kee...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    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

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.