Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Custom column to create a new customer flag using group by function.

Hi all, I am an apprentice to M language, but I am aware of ETL using Power query. I need help to add a custom column using group by in order to flag each transaction if the customer is new or not...
Need Help
Alex_T's avatar
Alex_T
Frequent Visitor
4 years ago

Hi Anonymous ,

 

Hopefully this will help...

 

New_customer_table

let

_reference_date = #date(2021,01,01),

Source = PQ_Test,

Custom1 = Table.Group(Source,"Unique ID",{

{"New Customer", each if List.Min([Sku Count]) = 0 and List.Min([Invoice Date]) >= _reference_date then "Yes" else "No"}

})

in

Custom1

 

Using Table.Groups you will obviously be creating a separate reference table of customer status. You can create a new column with a join.

 

New_PQ_Test_table

let

Source = Table.NestedJoin(PQ_Test, {"Unique ID"}, New_customer_table,

{"Unique ID"}, "Join_table", JoinKind.LeftOuter),

Expanded_Join_table = Table.ExpandTableColumn(Source, "Join_table",

{"New Customer"}, {"Join_table.New Customer"})

in

Expanded_Join_table

 

You need a new table for this as joining onto the existing PQ_Test will create a circular reference, to avoid seeing both original and new tables in the report, deselect "Enable load"

 

Let me know if this works for you.

 

Best wishes, Alex

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Alex_T 

    Thank you, I appreciate your help.
    I guess I am one step closer to achieving what I need to.


    I forgot to mention a prerequisite step in my orginal post. Kindly help me on this.
    In order to derive the New Customer flag, I need to first derive the sku count in PQ as a new custom coulmn.
    The Sku count is dependent on the Unique ID that I have already derived.

    Request/Questions

    1) Can you please guide me - how to derive the Sku count in PQ? logic for SKu count is in the sample file.
    2)The table in the sample file is already loaded into PQ in my computer for the weekly tasks. Must I still include the line "Source = PQ_Test," in the query editor?


    Thank you!