Adding records in new table based on distinct row with computation

[nujkram]

Good day everyone, I'm new with Power BI just wanted to ask how can I add the distinct items from one table into another.

I wanted to save all the distinct group_items in [Items] Table to [Duration] Table and compute the total created time for each group_items. See sample image below. I've been searching all day now still no chance.

[v-juanli-msft · Accepted solution]

Hi @nujkram

First in query editor

add two custom columns (Add Column->Custom Column)

Custom=DateTime.Time([created])

Custom.1= #time(Time.Hour([Custom]),0,0)

Second in Data View, create two calculated columns

duration = [Custom]-[Custom.1]

sumduration = CALCULATE(SUM(Sheet5[duration]),ALLEXCEPT(Sheet5,Sheet5[items]))

Second create a new table and create a calculated column in this new table

Table = SUMMARIZE(Sheet5,Sheet5[items],Sheet5[sumduration])

id = RANKX(ALL('Table'),'Table'[items],,ASC)

Best regards

maggie

[nujkram]

Thank you so much @v-juanli-msft (maggie). Works like a charm!