fILL This blank space

[RJ2597]

Hi want to fill this external_id as a deleted and store name is unknow in font of amont coloum like 131

[v-yanjiang-msft · Accepted solution]

Hi @RJ2597 ,

According to your description, in my understanding, you have a table like this:

For the External ID column, if it is blank and the Amount column is 131, replace the blank value with "deleted".

For the Store Name column, if it is blank and the Amount column is 131, replace the blank value with "unknown".

Here's my solution.

1.In DAX.

Create two calculated column.

External ID Column =
IF ( [External ID] = BLANK () && [Amount] = 131, "deleted", [External ID] )

Store Name Column =
IF ( [Store Name] = BLANK () && [Amount] = 131, "unknown", [Store Name] )

Result:

2.If you want to transform the original column instead of create a new column, you can do it in Power Query.

Add two steps in Advanced Editor.

 #"Replace External ID"=Table.ReplaceValue(#"Changed Type",each [External ID], each if [External ID]=""and [Amount]=131 then "deleted" else [External ID],Replacer.ReplaceValue,{"External ID"}),
 #"Replace Store Name"=Table.ReplaceValue(#"Replace External ID",each [Store Name], each if [Store Name]=""and [Amount]=131 then "unknown" else [Store Name],Replacer.ReplaceValue,{"Store Name"})

Result:

Here's the whole M syntax, you can copy-paste in a blank query to see the details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtIBIUNjQ6VYnWglY1MLYyMTE1OgkJGBgbmuY1JqUUpqap6Ca0VJUSJIoSlUnZmJmYUZHnVGcPOA0BCnOmOwMhMDI5AyC5zKzKHKLC3MzcwtCZlmamJqYWBogttxBkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"External ID" = _t, #"Store Name" = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Store Name", type text}, {"Amount", Int64.Type}}),
    #"Replace External ID"=Table.ReplaceValue(#"Changed Type",each [External ID], each if [External ID]=""and [Amount]=131 then "deleted" else [External ID],Replacer.ReplaceValue,{"External ID"}),
    #"Replace Store Name"=Table.ReplaceValue(#"Replace External ID",each [Store Name], each if [Store Name]=""and [Amount]=131 then "unknown" else [Store Name],Replacer.ReplaceValue,{"Store Name"})
in
    #"Replace Store Name"

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[amitchandak]

@RJ2597 , You can do that in power query replace null with 131

or

do not give anything in the first box and give 131 in the second box

Power Query Replace Value: https://youtu.be/hkZhZbR7Kmk

[RJ2597]

Sorry my question is i want to fill exteranl id and store name where i got the blank