Forum Discussion

cmaloyb's avatar
cmaloyb
Icon for Helper II rankHelper II
6 years ago
Solved

Replace Null with a Value and Informatin from another Column

I have a Table Like so in Power Query

LocationJanFebMarApr
A1 Anullnull1 A
Bnull1 B1 Bnull
Cnullnull1 C1 C

 

How can I replace the null value with a "0" and the respective Location? 

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    I did this:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUAJEQBGLH6kQrOcG4TnASLO6MpNIZSsbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Location = _t, Jan = _t, Feb = _t, Mar = _t, Apr = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Jan", type text}, {"Feb", type text}, {"Mar", type text}, {"Apr", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Location"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if [Value] = "" then "0 " & [Location] else [Value]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Value"}})
in
    #"Renamed Columns"

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    I did this:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUAJEQBGLH6kQrOcG4TnASLO6MpNIZSsbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Location = _t, Jan = _t, Feb = _t, Mar = _t, Apr = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Jan", type text}, {"Feb", type text}, {"Mar", type text}, {"Apr", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Location"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if [Value] = "" then "0 " & [Location] else [Value]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Value"}})
in
    #"Renamed Columns"
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi cmaloyb ，

    Here two additions for the post suggested by Greg_Deckler :

    1. Update the condition value in applied step "Added Custom" with null

    2. Add another step in last step for pivot columns:

     #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Attribute]), "Attribute", "Value")

    Best Regards

    Rena