Defining a variable in a custom column?

[Anonymous]

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?

[v-piga-msft]

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

[Anonymous]

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.

[Anonymous · Accepted solution]

If you work on "Queries" than it's power query and so M language, but you are writing Dax code. So that function should be a calculated column in the editor rather than query.

[Anonymous]

@Anonymous  Yes, sorry if I was unclear, but I was using that formula in a Calculated Column within that table/query.

You're saying that if I use that data in the editor and create a new measure that will work?