Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Loop for each cust ID... do this

i have customer ID and I have dates that repeat.  i created a function that returns the 1st item in list and says, if item = item from list then null else 1... BUT, how do I use this on each set of C...
Need Help
  • spinfuzer's avatar
    spinfuzer
    2 years ago

    Group By first and then create the custom column in the grouped tables.

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDJUCDRUitWB840UAo3Q+MZgvhGavBFOeROl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [cust_id = _t, purchase_date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"cust_id", Int64.Type}, {"purchase_date", type text}}),
    #"Grouped Rows" = Table.Group(
        #"Changed Type", 
        {"cust_id"}, 
        {   
            "index", 
            each Table.AddColumn(
                _,
                "index",
                (x) => if x[purchase_date] = List.First( List.Distinct(_[purchase_date])) then null else 1)
        }
        )
in
    #"Grouped Rows"

     

spinfuzer's avatar
spinfuzer
Solution Sage
2 years ago

Group By first and then create the custom column in the grouped tables.

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDJUCDRUitWB840UAo3Q+MZgvhGavBFOeROl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [cust_id = _t, purchase_date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"cust_id", Int64.Type}, {"purchase_date", type text}}),
    #"Grouped Rows" = Table.Group(
        #"Changed Type", 
        {"cust_id"}, 
        {   
            "index", 
            each Table.AddColumn(
                _,
                "index",
                (x) => if x[purchase_date] = List.First( List.Distinct(_[purchase_date])) then null else 1)
        }
        )
in
    #"Grouped Rows"