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?

 

 

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion

    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.

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support

    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

      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

        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.