Forum Discussion

ronhs's avatar
ronhs
Frequent Visitor
3 years ago
Solved

Tagging data base on dynamic time period

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_TIMEID
2022/12/1a
2021/12/3b

I want to genarate tags below:

ORDER_TIMEIDCUSTOMER_TYPE
2022/12/1aEXISTING
2021/12/3bNEW

Is there any faster way to tagging data?

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    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.

2 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    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.