Tagging data base on dynamic time period

[ronhs]

I have a dataset which include two columns, ID and order time.
I want to create a new column to tag two type of value.
The definition of value is below:
New : before current order time never appear in previous time.
Existing : exclude "New" tag data.

I'm trying to create a loop like structure to filter time period and merge filter data to genarate what I want, but it's look like so inefficient...

sample data is below:

ORDER_TIME	ID
2022/12/1	a
2021/12/3	b

I want to genarate tags below:

ORDER_TIME	ID	CUSTOMER_TYPE
2022/12/1	a	EXISTING
2021/12/3	b	NEW

Is there any faster way to tagging data?

[v-yanjiang-msft · Accepted solution]

Hi @ronhs ,

According to your description, here's my solution. Add a custom column in Power Query.

if [ORDER_TIME]=List.Min(#"Changed Type"[ORDER_TIME])then"New"else"EXISTING"

Get the result:

Here's the whole M syntax, you can copy-paste it in a blank query to see the details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI3BCIlHaVEpVgdsIghSMQYKJKkFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ORDER_TIME = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ORDER_TIME", type date}, {"ID", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "CUSTOMER_TYPE", each if [ORDER_TIME]=List.Min(#"Changed Type"[ORDER_TIME])then"New"else"EXISTING")
in
    #"Added Custom"

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[lbendlin]

You can do that with measures. Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523