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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
abehrmann
Helper II
Helper II

Replace null values with contents from another column

 

I am attempting to replace the null values with the values from completion note date in the far left. 

 

If possible i would like to use the exitsting completion date column instead of creating a new one. 

 

Any suggestions 

 

 

CompletionNotePIC.PNG 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

If a measure is sutible for your needs then a simple measure liek the following would work. Replace max with whatever aggregation you need

 

 

measure =
VAR _Column1 =
    MAX ( Column1 )
RETURN
    IF ( ISBLANK ( _Column1 ), MAX ( Column2 ), _Column1 )

If a measure is not sutible then the easiest way is to add a new column either in the model or in the Query Editor.

 

The DAX version would be 

 

Column3 =
IF ( ISBLANK ( Column1 ), Column2, Column1 )

The Power Query Version would be

 

 

if [Column1] = null then [Column2] else [Column1]

Then remove the old column. With Power Query you can do it in one step without adding a new column but it is not possible with the UI you need to use the advanced editor. I have included an example below, the key step is 

Table.ReplaceValue(#"Changed Type",null,each [Column1],Replacer.ReplaceValue,{"Column2"})

 

 

let
    Source = Table.FromRows({{1,null},{2,2},{3,3},{null,4},{5,null}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,each [Column1],Replacer.ReplaceValue,{"Column2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Column2", Int64.Type}})
in
    #"Changed Type1"

 

 

 

View solution in original post

1 REPLY 1
Anonymous
Not applicable

If a measure is sutible for your needs then a simple measure liek the following would work. Replace max with whatever aggregation you need

 

 

measure =
VAR _Column1 =
    MAX ( Column1 )
RETURN
    IF ( ISBLANK ( _Column1 ), MAX ( Column2 ), _Column1 )

If a measure is not sutible then the easiest way is to add a new column either in the model or in the Query Editor.

 

The DAX version would be 

 

Column3 =
IF ( ISBLANK ( Column1 ), Column2, Column1 )

The Power Query Version would be

 

 

if [Column1] = null then [Column2] else [Column1]

Then remove the old column. With Power Query you can do it in one step without adding a new column but it is not possible with the UI you need to use the advanced editor. I have included an example below, the key step is 

Table.ReplaceValue(#"Changed Type",null,each [Column1],Replacer.ReplaceValue,{"Column2"})

 

 

let
    Source = Table.FromRows({{1,null},{2,2},{3,3},{null,4},{5,null}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,each [Column1],Replacer.ReplaceValue,{"Column2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Column2", Int64.Type}})
in
    #"Changed Type1"

 

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.