Replace values to null in matrix table

[sagar512]

I want to replace Unknowns with null without duplicating the columns in the matrix table

is there any way to achieve that?

[v-xiaotang]

Hi @sagar512 

Thanks for reaching out to us.

please try this, go to Power Query Editor, and select all columns then select replace values,

result

FYI: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg70UdIBk7E6MF5oXnZefnkeWATGRlcD5sUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","Unknown",null,Replacer.ReplaceValue,{"Column1", "Column2"})
in
    #"Replaced Value"

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[sagar512]

Thanks for your response.

However, I don't have an option of using a query editor as I am with PowerBI data sets (Direct query).

[v-xiaotang]

Hi @sagar512 

Thanks for your reply.

In this scenario, you need to replace unknown values to null in your datasource. Otherwise, PowerBI Desktop cannot modify your original data without adding columns., 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@sagar512 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.