we cannot replace value null to type text

[Anonymous]

Looking for a simple solution

I am pivoting a table to create a column for each value

= Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Status]), "Status", "Count")

it is returning some null values, which I want to convert to Zeros so they will work properly in Averages in a Matrix

So I trieds using replace values null with 0 and it seems to work fine until I try to apply the changes.

The columsn it creates are called Availalbe, Implemented, In Flight, and Roadmap.

Any ideas?

[Anonymous]

Here is a screen shot. once pivoted, I can't get the nulls replaced with zeros to save back

[danextian]

Hi @Anonymous 

Did a test and replacing null in a column that is type number with zero works fine. The formula looks like this Table.ReplaceValue(#"PreviousStep",null,0,Replacer.ReplaceValue,{"Number"})

Try converting your number column to type text first, do a replace and convert it back to number after.  Alternatively, you may create a custom column with a formula similar to this if [Column] = null then 0 else [Column] and then delete the original column

[Anonymous]

thanks, I tried both of these approached and neither worked.

[danextian]

@Anonymous , Are you able to attached your pbix with the data source?

[Anonymous]

If I extract the data to a CSV and perform the same series of steps it works fine.  but not on my OData source.

[parry2k]

@Anonymous that's interesting. You should open a support ticket. I hardly uses/had a chance to use ODAta so cannot help much but this seems to be a bug that it works with one type of data but not other. good luck and post your solution here so that it can help others.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

I don't think I can share the data.

the problem occurs as soon as I pivot

= Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Status]), "Status", "Count")

[parry2k]

@Anonymous I think you need to share sample data/screen shots explaining what is not working, very hard to follow from your question what you are trying to solve here?

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490