Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cmaloyb
Helper II
Helper II

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? 

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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"

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

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")

pivot.JPG

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User
Super User

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"

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.