Forum Discussion
Defining a variable in a custom column?
Hello,
I have data that I am appending into one table - one table has a submission column (number) the other table does not so these rows are importing as 'null.' I would like to change all of the 'null's to the most recent submission number (Example - Current highest submission = 4, I want all the nulls to equal 4 but when the next submission is submitted I would like for it to automatically refresh).
However, I keep getting an error when I try:
If( IsBlank('LE + R&O'[Submission], MAX('LE + R&O'[Submission]),'LE + R&O'[Submission]))
Error: Token Literal Expected
I am wondering if it is possible for me to define the most recent submission as a variable and use that instead? I attmped this formula but I am getting the error: Token Eof expected...
Var RECENT_SUB = MAX('LE + R&O'[Submission])
If( IsBlank('LE + R&O'[Submission], RECENT_SUB,'LE + R&O'[Submission]))
Is there a better way to do this?
4 Replies
Hi Anonymous ,
Firstly, base on your code, I'm afraid that MAX('LE + R&O'[Submission]) is not the power query language, that should be dax function and there is no variable function in power query either. So which way do you want to achieve your output? Power Query or Dax expression?
In addition, I have a little confused about your scenario, could you please share some data sample and your desired output so that we could help further on it.
Best Regards,
Cherry
I'm honestly not sure what the difference is between Power Query vs Dax - I have been attempting to manipulate the data in the Queries instead of creating Measures.
Here is an example of what the data looks like - I am appending the LE table to the RO table and the RO table does not have a submission number because I do not need to see past versions, just the current version.
LE/RO Value Submission RO 100 null RO -50 null LE -30 4 LE -20 3 LE -10 2 I only want the RO dataset to be combined with the most recent submission.
