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
mdvillanueva
Regular Visitor

Replace null w/ 0 (whole table) & Deriving a calculated table from a pivot table (Total and Percent)

Hi guys, I know it may seem simple but I am new to using powerbi so please bare with me. I have a pivot column made in power bi. Now this contains 1million+ rows and thousands of columns. There are null cells and I wanted to be able to replace these nulls with 0. I can do it column by column but aint nobody got time for that!!! I have thousands of columns!!! 

 

I hope you can help me. Thank you in advance!

8 REPLIES 8
vanessafvg
Super User
Super User

@mdvillanueva how are the columns made?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Hello it was a pivot table actually. Looks like this try.PNG

@mdvillanueva ah ok got it, you could highlight all the columns and then do a blanket replace of null with 0.  Not sure what peformance hit that would give.  another way of handling it though is what do you want to do with the data, how are you planning on using it?   are you really going to be summing up each column separately?  when you create your measures you can also add an +0 on to the measure for those that are null which will force a 0 value 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




YES!! Actually i am planning to sum it up and do a separate summarized table where in the percentages are shown. I was used to using excel where in it automatically sums everything up and you can easily get calculated tables derived from the pivot table.

try1.PNG

Any idea how I can do this? 

 

Here is the sample output.

 

try2.PNG

@mdvillanueva you know you can do the same in power bi?  all you need to do is click on your measure(value) on the arrow and drop down  and say show value as 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Please explain a bit more I'm having a hard time doing it 😞

in your visual layer when you place your data, in the section where you place your fields look at your value field see it has  drop down arrow, you can use that to do the show value as..  dont know if that solves your problem?  otherwise it might be easier for you to share the pbix.

 

Capture.PNG

 

alternatively you dont need to pivot your data in power query you can do that in your matrix and in your measure you can just create a +0, but like i say might be easier to just share your pbix





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




I was able to make it as a matrix. I dont have a %GT Count in the value drop down as a choice. And the totals of the matrix is wrong. 😞 

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.