Enter NULL value in manual table

[Anonymous]

I was looking at how the data was being shown when imported from a database and ran some tests by just starting a new file, clicking "Enter Data", entering a sample data set and then adding the same card as I had with the data from the database. There were a few weird things happeing (which I suspected and have now confirmed is to do with NULL values from the database), but I could not test my theory about the NULL values as I could not work out how to add a null value (NOT just a blank string) into a manual table! When working on SQL databases, I find it handy when something doesn't seem to work to just create a temporary BI file with a manual table with a few bits of basic data and see how it reacts. An important part of this is seeing how NULL values react so is there any way to enter a null value in a manual table?

So basically:

1) Open New Power BI file

2) Click "Enter Data"

3) Make table with 2 fields and a few rows

4) Create values in cells with some values being NULL (not a blank string)

[parry2k · Accepted solution]

@Anonymous create blank query and paste this code, Id column will have null

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hce7FYAgEATAXjYmEFHREPFXgrzzCjCx/9B9m82YISKgwIOhp1YpUfX5qjIwt0Q0YaQ2aaJ2KVOHNFOntFCXFDvyhfsP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Text", type text}})
in
    #"Changed Type"

[PradeepKasipuri]

@parry2k
It is working Thanks
It is actual null not a String Null

[parry2k · Accepted solution]

@Anonymous create blank query and paste this code, Id column will have null

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hce7FYAgEATAXjYmEFHREPFXgrzzCjCx/9B9m82YISKgwIOhp1YpUfX5qjIwt0Q0YaQ2aaJ2KVOHNFOntFCXFDvyhfsP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Text", type text}})
in
    #"Changed Type"

[Anonymous]

So there is no easy way to do this? Would be nice if when you were in the edit source mode (where you can just type values into the table) you could right click and set to null value or something like that. Or just some graphical way that you could create a null value so that when you are messing around with test data it would be quick to change values.

[parry2k · Accepted solution]

@Anonymous you can actually use replace function to update null value

[Anonymous]

I thought of that, but isn't that replacing the string "A" with a string "null" and not an actuall null?

[parry2k]

@Anonymous it will be actual null not string "null"

[Anonymous]

@parry2k 

Your right! Now I can just leave any cells blank in that colum I want and do a replace on that column (if I want blank strings I would just have to use a string thats nowhere else) with null. I like this solution as its a lot quicker and more flexible. Thanks for that.