Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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"
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
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"
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |