Forum Discussion

zebra's avatar
zebra
Helper II
2 years ago

Creating custom column or measure

Hey All,   I am having hard time in creating custom column/measure according to following scnario. Suppose I have product table as follows   Id parent_id Name Price Updated_At 1 null ...
Need Help
zebra's avatar
zebra
Helper II
2 years ago

thanks, this is working, can you provide me M code also so i can make the custom column using query editor?

AlienSx's avatar
AlienSx
Super User
2 years ago

HI, zebra here goes M

let
    Source = your_product_table,
    values = {"decreasing", "unchanged", "increasing"},
    f = (tbl as table) as table =>
        [prices = List.LastN(tbl[Price], 2),
        comp = Value.Compare(prices{1}? ?? prices{0}, prices{0}) + 1,
        add = Table.AddColumn(tbl, "Trend", (x) => if x[parent_id] = null then values{comp} else null)][add],
    group = Table.Group(Source, {"Name"}, {{"all", each f(Table.Sort(_, "Updated_At"))}}),
    expand = Table.ExpandTableColumn(group, "all", {"Id", "parent_id", "Price", "Updated_At", "Trend"})
in
    expand

 

  • zebra's avatar
    zebra
    Helper II
    2 years ago

    the above M code gives me the following error

    Expression.Error: A cyclic reference was encountered during evaluation.
    • AlienSx's avatar
      AlienSx
      Super User
      2 years ago

      zebra probably because you are trying to add new column with this code. Don't do that. Imagine you have your product table in PQ as separate query named "your_product_table". Then create a blank query with my code and see what happens. Check this video created by ImkeF especially for you.