Forum Discussion
Question on Extract Group Values
Hi all,
I have a sample dataset that has 3 columns, ID, Food-A and Food-B. I would like to create a new column called Last Meal (Yellow color). I would like to exact the group of value by Sample column. For each Sample group, I want to determine the Last Meal column value from Food-A and Food-B.
The rule is if all values in Food-A is NA within the same sample, then display the Food-B set value in Last Meal column for the same sample, otherwise display Food-A column value within the same sample.
For example in sample A1, Food-A has 3 values are beef, lamb and NA (not all values are NA) so we will display Food-A as the Last meal within A1 sample. This is the same for sample A4.
For sample A5, all the values in Food-A are NA so we will display Food-B values as the Last meal.
Below is the data and expected value in Last meal (Yellow color):
I have tried using Conditional Column in Power Query Editor is not possible to filter within each sample. Using If-else statement is unable to filter by Sample too.
Thanks for helping.
8 Replies
ryan_mayu I am trying to digest your solution.
Can you explain a bit why we need to use Maxx to know whether column A of the Table is empty ('table'[A]="") ?
Is the code below means using the Filter context, I am checking is each row is the same value as previous row (using EARLIER) and if the current row is the same as previous row in the Sample column then check whether column A is not NA?Table'[Sample]=EARLIER('Table'[Sample])&&'Table'[A]<>"NA"max to check if there is not NA value in the same sample,. If we can find any other value except NA, that will return reuslt, if we can't find, that will return blank.
this is following your below logic:
The rule is if all values in Food-A is NA within the same sample
ryan_mayu Another I want to clarify with you.
The output of the Filter (to create the virtual table, I mean this FILTER(Sheet1, Sheet1[Sample]=EARLIER(Sheet1[Sample] && Sheet1[Food-A]=""), let's take A1 as example. Originally, it has 3 records, when applied the fillter function on whether Food-A column is NA, is the A1 record remain as 3 (beef, lamb and NA) or 2 (beef and lamb).
