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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Mars3442
Helper I
Helper I

Replace null value with value from other column

Hi everyone,

I want to ask a help for solving my problem

I have a table like this

Mars3442_2-1699448230739.png

 

I need to replace the null value with these conditional :

Delay Reason= not null, the status = Delay Reason value

Without replace the cell that have been filled

 

My expectation table like this

Mars3442_3-1699448243111.png

 

What M Code I should write to get my expectations?

 

Please help me to solve this problem

 

Thank you..

1 ACCEPTED SOLUTION

Hi @Mars3442 

You can try the following code

 

=Table.ReplaceValue(the last  step name,e.g #"Changed Type",each [Status],each if [#"Source.Delay/Late Reason"]<>"" then [#"Source.Delay/Late Reason"] else [Status] ,Replacer.ReplaceValue,{"Status"})

 

Output

vxinruzhumsft_0-1700545066724.png

Or you can put the following code to advanced editor

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRcknNySxLLUpNUYrViVYKKUrMKy7IL0osyczPA8qCBWE0LkkkI2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Source.Delay/Late Reason" = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source.Delay/Late Reason", type text}, {"Status", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Status],each if [#"Source.Delay/Late Reason"]<>"" then [#"Source.Delay/Late Reason"] else [Status] ,Replacer.ReplaceValue,{"Status"})
in
    #"Replaced Value"

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
ChielFaber
Super User
Super User

Do you have a part of your m-code? Like the previous row and the correct names

ChielFaber
Super User
Super User

Try the following M-code. Add it in the advanced editor. Replace the text #"Previous_STEP" with the previous step name in the advanced editor.

 

Table.ReplaceValue(#"Previous_STEP", each [Status], each if [Delay Reason] <> null then [Delay Reason] else [Status],Replacer.ReplaceValue,{"Status"})

It still nothing change with my data


Actually my "delay reason" column name is "Source.Delay/Late Reason"

the M code error when I use that column name

what should I do?

Hi @Mars3442 

You can try the following code

 

=Table.ReplaceValue(the last  step name,e.g #"Changed Type",each [Status],each if [#"Source.Delay/Late Reason"]<>"" then [#"Source.Delay/Late Reason"] else [Status] ,Replacer.ReplaceValue,{"Status"})

 

Output

vxinruzhumsft_0-1700545066724.png

Or you can put the following code to advanced editor

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRcknNySxLLUpNUYrViVYKKUrMKy7IL0osyczPA8qCBWE0LkkkI2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Source.Delay/Late Reason" = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source.Delay/Late Reason", type text}, {"Status", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Status],each if [#"Source.Delay/Late Reason"]<>"" then [#"Source.Delay/Late Reason"] else [Status] ,Replacer.ReplaceValue,{"Status"})
in
    #"Replaced Value"

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi, @Mars3442 try this 

 

let
    Source = table_like_this,
    replace = 
        Table.ReplaceValue(
            Source, 
            "", 
            each [#"Source.Delay/Late Reason"],
            (value, old, new) => new ?? value,
            {"Status"}
        )
in
    replace

 

Why don't you simply add new column to check if  "Source.Delay/Late Reason" is null ? Then remove old Status column. That's easier and must be faster.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors