How to create a new table base on another table column with condition

[kentchiu]

Hi,

I am a new Power BI user and am having difficulty creating a table based on another table row with conditions.

What I would like to achieve is creating a new table with a distinct row of unique ISSUE_KEY with the latest created date) .

Table A:

CREATED = Date type

ISSUE_KEY = Character String type

New table:

Contains a unique row of the ISSUE_KEY with the latest CREATED date

- Notice there are duplicate rows of ISSUE_KEY with different CREATED dates (could be 2 or more)
- In the sample table above, there are 2 ISSUE_KEY with different CREATED dates. The new table should have the latest date.

Thank you so much for your help.

--Kent

[xifeng_L · Accepted solution]

Hi @kentchiu 

You can try below DAX Table expression.

New Table = 
FILTER (
    Table,
    VAR CurCreated = Table[CREATED]
    VAR LatestCreated = CALCULATE (MAX(Table[CREATED]),ALLEXCEPT (Table,Table[ISSUE_KEY]))
    RETURN
    CurCreated = LatestCreated
)

But in your this case, the best method is use the PowerQuery to create the new table, which only need two step.

Step1. Sort by ISSUE_KEY and CREATED (The CREATED need to sort in descending)

Step2. Perform deduplication on on the table based on ISSUE_KEY

Because the deduplication operation in PowerQuery will only retain the first occurrence of the row.

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[kentchiu]

Thank you both @sanalytics and @xifeng_L  for the solution to my inquiry. They seem to work with both proposed approach solutions.  I have tried them and it works as I expected.

[v-xianjtan-msft]

Hi @kentchiu 

First of all, thanks to xifeng_L and sanalytics for their positive response!

I agree with the approach offered by both of them, and if their replies helped you solve your problem, please consider accepting their replies as a solution that will benefit others who are experiencing similar problems.

If you need any other help, please feel free to contact me.

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sanalytics · Accepted solution]

Hello @kentchiu 

You can achieve this by using both DAX and PQ.
Below is the DAX query

VAR _1 =
	ADDCOLUMNS(
		SUMMARIZE(
			Source,
			Source[ISSUE_KEY],
			Source[Issue Status Name]
		),
		"@Created",CALCULATE( MAX(Source[CREATED]) )
	)
	RETURN
		_1

PQ solution

let
    Source = Source,
    #"Grouped Rows" = Table.Group(Source, {"ISSUE_KEY", "Issue Status Name"}, {{"Created", each List.Max([CREATED]), type nullable datetime}})
in
    #"Grouped Rows"

Below attached file

https://we.tl/t-tJvge3FSTD 

Let me know if it helps

Regards,

sanalytics

If it is your solution then please like and accept it as solution

[xifeng_L · Accepted solution]

Hi @kentchiu 

You can try below DAX Table expression.

New Table = 
FILTER (
    Table,
    VAR CurCreated = Table[CREATED]
    VAR LatestCreated = CALCULATE (MAX(Table[CREATED]),ALLEXCEPT (Table,Table[ISSUE_KEY]))
    RETURN
    CurCreated = LatestCreated
)

But in your this case, the best method is use the PowerQuery to create the new table, which only need two step.

Step1. Sort by ISSUE_KEY and CREATED (The CREATED need to sort in descending)

Step2. Perform deduplication on on the table based on ISSUE_KEY

Because the deduplication operation in PowerQuery will only retain the first occurrence of the row.

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~