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
Hi everyone,
I want to ask a help for solving my problem
I have a table like this
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
What M Code I should write to get my expectations?
Please help me to solve this problem
Thank you..
Solved! Go to 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
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.
Do you have a part of your m-code? Like the previous row and the correct names
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
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
65 | |
61 | |
21 | |
18 | |
12 |