If the first row is equal to a name, replace the rest of values of that column with null

[pouyanebrahimi]

I have a table as below:

Test A	Test B	Test C
839	1870	1779
1556	613	884
1161	588	1962

I also have a "Date Table" as below:

Q means Quarter. 

Test A	Test B	Test C
02/5/2020	12/12/2019	05/07/2019

Today is June 4, 2020 (Quarter 2, 2020). If the date in "Date Table" is the Previous Quarter (right now, the previous quarter is Q1 2020), then I want to replace all the values of that column with "null" and right "Completed" on the first row. The result will be as below:

Test A	Test B	Test C
Completed	1870	1779
	613	884
	588	1962

Is there any way to do this in Power BI Query?

Any help is appreciated. 

[v-diye-msft · Accepted solution]

Hi @pouyanebrahimi 

Let me know if you'd like to get below results, but you need to add an index column for distinguishing. I do a trick here to hide the index column by special formatting. 

Pbix attached.

[pouyanebrahimi]

@v-diye-msft   I just oppened your file using my peronal computer, instead of Company computer. Thanks!

Would it be possible for you to explain a little bit your DAX codes that you used for making a measures? I am new in Power BI and get myself familarize with M not DAX, I guess I should start DAX as well 🙂 

MeasureforA = var a = MAXX(ALL('Table'),[Test A])

Return

IF(QUARTER(MAX('Table (2)'[Test A]))=QUARTER(TODAY())-1,IF(MAX('Table'[Test A])=a,"Complete",BLANK()),MAX('Table'[Test A]))

Can we expand this method a bit to cover the following thing as well?

	Test A	Test B	Test C
Q1 2020	1500	1200	648
Q4 2019	1900	500	795
Q3 2019	1850	55	1684
Q2 2019	999	755	888
Q1 2019	1900	1826	2000
Q4 2018	1400	628	1050

Date table: 

Test A	Test B	Test C
Q1 2020	Q2 2019	Q4 2019

Then the result should be:

	Test A	Test B	Test C
Q1 2020	Completed	1200	648
Q4 2019		500	Completed
Q3 2019		55
Q2 2019		Completed
Q1 2019
Q4 2018

[pouyanebrahimi]

@v-diye-msft Thanks for the answer. However, I cannot open your Pbix file. Would it be possible for you to take screenshots of different steps and share it with me please?

I found one problem in your answer. The first and second rows for Test B and C are exchanged. 

[parry2k]

@pouyanebrahimi sorry your question is not clear to me, could you please bit more. Cannot make out how you data is linked to the dates and what to make null and what you want in final output.

[pouyanebrahimi]

@parry2k Two tables are not connected yet! But we can connect them by appending or merging. 

The point is, if the date "Date Table" is within the previous quarter of the year, we do not need additional information from the first table. 

Example 1:

For test A, the "Date Table" is Q1-2020, so, we do not need additional information for the Column A of First Table. In other word, In table 1, Column A, we should only mention "Completed."

Example 2:

For the test B, in "Date Table", the dates are not in previous quarter (they are not Q1-2020), so we will keep their information as it is. 

As the result table shows, the table 1, will be the same results table for test B and Test C. The only differenc ebetween table 1 and result table is in column 1 which is for Test A.