Summarize table

[Kristofferaabo]

HI,

I would like to create a simple table based on another table, it must pull following columns where "Column A" should be uniques (like an excel pivot table)

Column A (unique values)

Colunm B (Can be first)

Column C (Can be first)

Column D (Can b first)

The purpose is to use this as a master table and thereby link other tables to its unique column A values

Thanks

Kristoffer

[v-yuta-msft]

Hi Kristofferaabo,

So you want to duplicate values in column A, right? You can refer to this case:

https://community.powerbi.com/t5/Desktop/How-can-I-remove-duplicate-values-from-a-single-column-with...

Regards,

Jimmy Tao

[Kristofferaabo]

Hi @v-yuta-msft

Thanks for your reply. No I'm nor interested to have duplicates in my new column (column A).

This new table must only return distinct values from the other table

Thanks

Kristoffer

[v-yuta-msft · Accepted solution]

Hi Kristofferaabo,

To create a new table, in dax, you can use summarize(), selectcolumns(), addcolumns().

To achieve distinct values in column A oin the calculate table, you can use summarize function like pattern below:

calculate table =
UNION (
    SUMMARIZE (
        old_table1,
        old_table1[column_need_to_be_distinct],
        "column1", SUM ( old_table1[column1] )
    ),
    SUMMARIZE (
        old_table2,
        old_table2[column_need_to_de_distinct],
        "column2", SUM ( old_table2[column2] )
    )
)

Also refer to DAX summarize here:https://msdn.microsoft.com/en-us/query-bi/dax/summarize-function-dax

Regards,

Jimmy Tao

[MattAllington]

Why not do this with groupby in Power Query

[Kristofferaabo]

Hi @MattAllington

Thats looks interesting, can Column A be unique still?

How would this look?

Thanks

K