Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Stan-Arabei
Frequent Visitor

Use NA (or N/A) where there is no data!

Hi:

    Does anyone know a way to use "N/A" where the data is missing?

 

pbi na before.png

 

 

pbi na after.png

 

1 ACCEPTED SOLUTION

@Stan-Arabei

 

On the modeling tab you click on New Table and then (for example) use this code:

Date = ADDCOLUMNS ( CALENDAR( DATE(2017,1,1), TODAY()), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "MonthNo", FORMAT ( [Date], "MM" ), "YearMonthNo", FORMAT ( [Date], "YYYY/MM" ), "YearMonth", FORMAT ( [Date], "YYYY/mmm" ), "MonthShort", FORMAT ( [Date], "mmm" ), "MonthLong", FORMAT ( [Date], "mmmm" ), "WeekNo", WEEKDAY ( [Date] ), "WeekDay", FORMAT ( [Date], "dddd" ), "WeekDayShort", FORMAT ( [Date], "dddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ))

 

Then, in the Relationships tab you connect the Date column with the Period column, then create the matrix.

View solution in original post

23 REPLIES 23
mikeborg82
Helper II
Helper II

You could do it in the query editor.  Just add an expression to find and replace null values with N/A.  

andris_
Resolver I
Resolver I

Yeah, the ISBLANK() function can be the solution in your case, like @JoHo_BI said, but it'd be great to know how your raw data look like in order to create the concrete IF() function. 

 

Regards,

Andris

here is link  to similar solution I provided on another post. Hope it is helpful.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

JoHo_BI
Responsive Resident
Responsive Resident

Hi Stan,

 

You can wrap your values in an '=IF(ISBLANK(...

 

So your final formula would look something similar to:

 

=IF(ISBLANK([Measure]),"N/A",[Measure])

 

Hope that helps!

Thanks for your solution:

The values are simply not there.
I would emagine, I'd have to create a table in Power BI with blank values and then utilize your solution of IFBLANK().

 

phase01.png

There are no blanks in my data.

The values are not there at all.

 

Before I convert blanks into any other value, like zero/NA/ or dash, there has to be some data.

 

However, as you can see in my raw data there is no values for 'EAST' during January and March.

You can create a Date/Calendar table, connect it to your main table's Period column, and use its Date column or Month column as the columns of the matrix, use Partners as the rows of the matrix, and create a Measure something like
Amounts = IF(ISBLANK(SUM([Amount]))=TRUE();"N/A";SUM([Amount])), and use this as the values of the matrix.

 

Regards,

Andris

Thank you, Andris:

    Please elaborate on how I can create the Date/Calendar table

@Stan-Arabei

 

On the modeling tab you click on New Table and then (for example) use this code:

Date = ADDCOLUMNS ( CALENDAR( DATE(2017,1,1), TODAY()), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "MonthNo", FORMAT ( [Date], "MM" ), "YearMonthNo", FORMAT ( [Date], "YYYY/MM" ), "YearMonth", FORMAT ( [Date], "YYYY/mmm" ), "MonthShort", FORMAT ( [Date], "mmm" ), "MonthLong", FORMAT ( [Date], "mmmm" ), "WeekNo", WEEKDAY ( [Date] ), "WeekDay", FORMAT ( [Date], "dddd" ), "WeekDayShort", FORMAT ( [Date], "dddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ))

 

Then, in the Relationships tab you connect the Date column with the Period column, then create the matrix.

 

@andris_ = Genius!!   THANK YOU!

 

display2.png

 

 

 

@Stan-Arabei, I'm glad if I could help you. 🙂

 

You can filter out the empty months, or delete them in query editor, or even can change the parameter in the Date table from today, if it's necessary.

 

Regards,

Andris

@Stan-Arabei although you have solution in place which is correct way to do it but I'm still not sure why formula which I provided will not work and it is working on my end.

 

Are you adding it as a new measure or column? It needt to be  a measure



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

I used a 'New Measure'

sorry for this silly question, does your table name is also Table6 as you showed in formula? Are you on matrix using Total Amount measure?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Yes. My Table name is also Table6: i renamed it to avoid any differences between your solution and my setup.

 

I did use a Matrix and the 'Total Amount' measure.

 

I don't really see how PBI would display values for the variables that don't exist (in this case, "N/A") unless they are created in a separate table or are already a part of original dataset.

It will work since in measure DAX expression we are telling what to show when value is blank and it is common scenario, it should work and it is 110% working on my end. I'm trying to find out what is the root cause it is not working for you.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

There is no Value. The Value does not exist.

It's not Blank and it's not zero. The entry is simply not there; like there is no entry for year 2019, for example. unless I create it in a separate table.

 

    PBI cannot aggregate values that don't exist.

here is the image of table6 which I created based on your sample data:

 

table6.PNG

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi Parry: it doesn't seem to work.

 

display.png

that is fine, did you tried what is suggested



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.