How to replace value of cell based on value of other cell defined by other columns

[BB1993]

Hi,

I've been searching around but couldn't find any solution which would solve my problem completely.

Let me explain the issue using the below example table:

Column A	Column B	Column C
123	four	one
456	five	two
789	six	three
null	seven	four

I want to replace the null values in Column A by the Column A value of the row where Column B value = Column C value of my null row.

So in the example I want to replace the "null" in the last row by "123" as that value is in the row where Column B (four) = Column C of my null row (four).

It would be great if someone could help me.

Thanks!

[AlB]

Hi @BB1993 

Do you need this in DAX or Power Query

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[BB1993]

Hi,

I need it in Power Query.

Thanks!

[AlB · Accepted solution]

@BB1993 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRSssvLQJS+XmpSrE60UompmYgwcyyVCBVUp4PFjS3sATyijMrQGIZRakQpSCh1LLUPJghsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t, #"Column C" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", Int64.Type}, {"Column B", type text}, {"Column C", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column A] <> null then [Column A] else Table.SelectRows(#"Changed Type",(inner)=>inner[Column B]=[Column C])[Column A]{0}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column A"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Column B", "Column C"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "Column A"}})
in
    #"Renamed Columns"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[BB1993]

Hi @AlB 

Thank you, this worked!

Just for my total understanding: Can you explain what the "(inner) => inner[Column B]" means?

Cheers!

[AlB]

Hi @BB1993 

Sure. each is syntax sugar for (_)=>

We have two nested operations here 

Table.AddColumn(#"Changed Type", "Custom", each if [Column A] <> null then [Column A] else Table.SelectRows(#"Changed Type",(inner)=>inner[Column B]=[Column C]) [Column A]{0} )

If we used "each" on both, when in the inner operation (Table.SelectRows) we wouldn't be able to refer to the variables of the outer operation ([Column C] in this case). So we use another input variable name (inner) instead of "_" in this case. With that we can tell M whether we are referring to the inner or the outer variables. The reasoning is similar to the use of EARLIER() in DAX to refer to the previous row context.

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Sunnie]

What would be DAX formula if one of the columns was in a different table?

[AlB]

@Sunnie 

Please be more specific. I don't understand what you need

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Sunnie]

How to use two columns if both are in different tables, i posted a question earlier to forum, titled: 2 Tables: Value of column to be blank if the other column value is blank.

I need to have dax look for a blank value from the 1st table column 'STEP Projects' [PM/AppTransitionMtg] to then replace the date value in the 2nd table column 'STEP Resource Planning' [Start] to blank .  If no blank is found from the 1st table, then 2nd table column 'STEP Resource Planning' [Start] remains with its original value.