Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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.
@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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |