How to translate/read color blank cell from excel in power bi ?

[Anonymous]

Hi There ,

Good day ! Appreciate if you could shed some light on this case. 

I have an excel file with many date columns (80++) and some cells in this file are just filled with colours (a blank cell with grey colour) . I want to be able to read these cells in power bi , so I dont considered these colour cell during date calculation.

Background :
- I rely on color cell , so I am aware that user have already review this column and they hv no data to input.
- So I will exclude this color cell during my date calculation.

Example 

Expected result
Program	Number of blank cell	Remark
A	0
B	0	Becoz blank cell is colored and its mean user confirm no date needed to input.
C	1	Blank cell is not color; user has yet to review this column.

Help !!
1. I was trying to request user to input n/a . but that was not allow in power bi with 2 data type with same column.
Any method to read/translate color cell in power bi which load from an excel file ?

2. Any suggestion so that user can do some remark/comment that can be detect by power bi ( considering 80-100 date column in data source ) ?

Thank you in advance 🙂 

[v-yuaj-msft]

Hi @Anonymous ,

@Anonymous 's suggestion is so great.

Based on your description, I think you should modify something before import the table into Power BI Desktop because currently Power BI cannot distinguish between colored null values and non-colored null values. It will return the following output.

So you can try some steps as follows. (The replacement value should be a date value.)

Then you can create some measures or columns. 

for example, I create a measure as follows.

measure = 

var x1=SUMMARIZE('Sheet3',Sheet3[Program],"date",MAXX('Sheet3',Sheet3[D_A]))

var x2=SUMMARIZE('Sheet3',Sheet3[Program],"date",MAXX('Sheet3',Sheet3[D_B]))

var x3=SUMMARIZE('Sheet3',Sheet3[Program],"date",MAXX('Sheet3',Sheet3[D_C]))

var x4=UNION(UNION(x1,x2),x3)

return

COUNTROWS(FILTER(x4,[date] in {BLANK(),DATEVALUE("1/1/1900")}))+0

My test result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yuaj-msft , 

Thanks a lot for the suggestion , it will be very useful for my future problem solving. 

Unfortunately , this method might not be feasible due to it is contradicting with another data formatting in excel that is aim to help user filter necessary cell. 

I have posts another topic in power query forum..

https://community.powerbi.com/t5/Power-Query/How-to-link-data-source-including-color-cell-in-new-wor... 

It would be great , if you have any suggestion to use power query to link data source(including color cell) in new worksheet ? 

Plan to copy to new worksheet and apply the function to count color cell. 

Once again , thank for your reply. 🙂

[Anonymous · Accepted solution]

Hi,

I can think about one way to achieve this...

In your excel file, create a macro in VBA.

The macro and your sample data are shown in the image below...

Sub Macro1()
'
' Macro to replace colored cells with the date "1/1/1900"
'
  
   Dim i As Integer  'define an integer variable i
   Dim j As Integer  'define an integer variable j
   
   For i = 2 To 4  'loops through row numbers 2 to 4 in your data. change it as per your actual data file.
    For j = 2 To 4 'loops through coluumn numbers 2 to 4 in your data. change it as per your actual data file.
        
        'checks if the cell is colored. for no color, the color code is -4142
        If Cells(i, j).Interior.ColorIndex <> -4142 And Cells(i, j) = "" Then
            Cells(i, j).Value = "1/1/1900"  'if colored and blank, fill the date "1/1/1900"
        End If
    Next j
   Next i
End Sub

The same macro without the comments is given below...

Sub Macro1()
   Dim i As Integer
   Dim j As Integer
   For i = 2 To 4
   For j = 2 To 4
        If Cells(i, j).Interior.ColorIndex <> -4142 And Cells(i, j) = "" Then
            Cells(i, j).Value = "1/1/1900"
        End If
    Next j
   Next i
End Sub

If you run the macro, all colored blank cells will be filled with the date 1/1/1900.

Now you can import this excel file to power bi and exclude the date 1/1/1900 from your calculations.

But the uncolored cells with no dates will be shown as "null" value in the date column in power bi 

You will be able to differentiate between "null" values and "1/1/1900" in your date field and accordingly modify your DAX code to handle the scenario.

Disclaimer: This is on the assumption that it is unlikely to have the date 1/1/1900 in your date columns that are not colored and dates filled by the user. If you feel that there is a probability of the user actually entering the date 1/1/1900 then you have to choose some other date like '31/12/9999' or something like that.

[Anonymous]

@Anonymous 

Thanks a lot for the suggestion and demo, really appreciate it ! 

As suggested , i hv asked my team member to help develop a macro for this counting issue and it works perfectly . 

Thanks again and have a great day !