Forum Discussion

Clément's avatar
Clément
Regular Visitor
8 years ago
Solved

Power BI formula

Hello,

 

I would like to find a formula in Power BI that have as argument a table and that returns another table with unique values for a particular column.
Could you please help me?

Thanks in advance

  • freder1ck's avatar
    freder1ck
    8 years ago

    Clement,

     

    Let me know if this is the result you need:

    'Table' is initial data. 'Results' is new table. 

     

    New Table:
    Results = DISTINCT(Table[id.])

     

    Add columns twice:

     

    City = CALCULATE(
    FIRSTNONBLANK('Table'[City], 1),
    FILTER('Table', 'Table'[id.] = 'Results'[id.]))

     

    Date = CALCULATE(
    FIRSTNONBLANK('Table'[Date], 1),
    FILTER('Table', 'Table'[id.] = 'Results'[id.]))

     

    Fred

     

    id.CityDate
    1Marseille3/6/2014 0:00
    2Nice1/31/2015 0:00
    3Paris12/23/2016 0:00

7 Replies

    • Clément's avatar
      Clément
      Regular Visitor
      8 years ago

      Hello Frederick,

       

      This formula returns a one-column table and I would like to return all the columns of the first table without any duplicated values for a special column.
      Can you help please?

       

      Clément

      • freder1ck's avatar
        freder1ck
        Kudo Kingpin
        8 years ago

        Sure. Can you show an example of the data you would start with, and what the result would be?

         

        Thanks, 

        Fred

         

        If you want to keep all columns but remove duplicates for one column, you could remove duplicate rows in the query editor.