Replace Null with a Value and Informatin from another Column

[cmaloyb]

I have a Table Like so in Power Query

Location	Jan	Feb	Mar	Apr
A	1 A	null	null	1 A
B	null	1 B	1 B	null
C	null	null	1 C	1 C

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

[v-yiruan-msft]

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

[Greg_Deckler · Accepted solution]

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"