Need to filter out "" values so columns can be used as values?

[CapnShanty]

First off, I hate this program more than any program I have ever used in my life or career. It is the opposite of user friendly, and basic quality of life features are just simply not there. I am constantly having to work around, rather than with, this idiotic crap. I will continue to lobby my organization to get rid of it. 

That being said, today's issue with power bi is that I have 60 month columns, let's ignore that power bi oh so cleverly sorts them to where Month 7 is above month 60, and that I can't shift select columns I had to put all 60 manually in a hierarchy to be able to manipulate them all at once.

I have "" values as the blanks in these columns where there are no data points. This means clever power bi refuses to let me use these fields as data fields. How do I get it to deal with these?

[apelkhaz]

To elaborate on what synergised said - go into the Transform tab in the Query Editor, select your column or columns that contain the "" values, then hit "Replace Values"

[v-frfei-msft · Accepted solution]

Hi @CapnShanty,

As @synergised said, you can go into the Transform tab in the Query Editor, select your column or columns that contain the "" values, then hit "Replace Values". You can refer to the pictures as below.

Regards,

Frank

[v-frfei-msft]

Hi @CapnShanty,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,
Frank

[synergised]

Just curious what tool you would use instead of Power BI?

From your problem statement, my first thought is maybe you are not thinking about your data multi-dimensionally.  Instead going with flat / wide for your data.  By saying you have 60 month columns, is that 5 years of data?

Month 7 is above month 60 - this is common everywhere when sorting using a string value.  We put sort order fields on all of our measure tables... so then we can right click on a measure and then use the associated sort order to sort it numerically.  There are multiple examples of now to sort by month out here on the forum.

"" values as the blanks in these columns where there are no data points - you can go into the Power Query Editor and fix up the data if necessary and replace the "" with zero.  Then you can change the data type of the field.