Forum Discussion

sagar512's avatar
sagar512
Icon for Helper I rankHelper I
3 years ago

Replace values to null in matrix table

I want to replace Unknowns with null without duplicating the columns in the matrix table is there any way to achieve that?    
Need Help
v-xiaotang's avatar
v-xiaotang
Icon for Community Support rankCommunity Support
3 years ago

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's avatar
    sagar512
    Icon for Helper I rankHelper I
    3 years ago

    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's avatar
      v-xiaotang
      Icon for Community Support rankCommunity Support
      3 years ago

      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.