I would like to add custom column base on specific conditions

[Anonymous]

Hello, 
I have an Excel table that I want to import in the power query.  The sheet contains the orders that are received every month. and they are almost always the same. please have a look at the tables. 

Account name	Sales amount	Month	Priority level
Casey M	454	April	Low
Shawn P	665	April	High
Dafne l	215	April	High
Kelly R	154	April	Low
Vincent D	255	April	Mid
Casey M	454	May	Low
Shawn P	100	May	Low
Dafne l	215	May	High
Kelly R	154	May	High
Vincent D	255	May	Mid
Casey M	454	June	Low
Shawn P	100	June	Low
Dafne l	215	June	High
Kelly R	154	June	Low
Vincent D	255	June	Mid

They are not always the same. Sales amount might change or priority level might change. I want to add a new custom column, that would add the value of "1" in the last month if there was a change to the same account name in the next month.
For example. In April, Shawn P's Priority was high but it changed to Low in May. I want to add value 1 to a new column in Shawn P's April Row

[Anonymous · Accepted solution]

Hi @Anonymous ,

I suggest you to add a MonthSort column in Power Query Editor and then add a custom column.

Custom Column:

let _Nextlevel = 
List.Max(
let 
_AccountName = [Account name],
_MonthSort = [MonthSort]
in
Table.SelectRows(#"Added Conditional Column", each _AccountName = [Account name] and [MonthSort] = _MonthSort+1)[Priority level])
in
if _Nextlevel <> null and [Priority level] <> _Nextlevel then 1 else null

Result is as below.

Best Regards,
Rico Zhou

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

[audreygerred]

Rather than adding a column into the data, I would suggest creating a date in the fact table instead of just month and then adding a date table, then assigning the priority a numerical ranking (for example High = 1, Med = 2, Low = 3) through a dimension table and join. Next, I created a measure that compares the rank MoM, then have icons showing in the table rather than the change MoM%.