How do I combine the data of two columns and prioritise data from one?

[lovetolearn]

I have data spanning over 4 years from a lead generation system. At some point a data field called "State" was used and later they started using another field called "Province".

What I have - two columns with some data, some blank fields and sometimes a value in both columns.

I would like to create a third column where I combine the data from the first two columns, but in instances where both columns have data, I want the data from "Province" to be in the Combined column and discard the value from "State".

I know how to combine data, but I don't know how to end up with only the single values when there are two values per row.

[v-henryk-mstf · Accepted solution]

Hi @lovetolearn ,

You can try the formula like below:

Col = 
IF (
    'Table'[State] = BLANK ()
        && 'Table'[Provice] = BLANK (),
    BLANK (),
    IF (
        'Table'[Provice] <> BLANK ()
            && 'Table'[State] <> BLANK (),
        'Table'[State],
        IF (
            'Table'[State] = BLANK ()
                && 'Table'[Provice] <> BLANK (),
            'Table'[Provice],
            'Table'[State]
        )
    )
)

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[lovetolearn]

Thank you for this solution, it worked perfectly.

Do you know how I can accomplish this with an M Query so I can add it as an automatic step whenever I import updated data?

[amitchandak]

@lovetolearn ,

a new column in dax = coalesce([State], [province] )

In power query

if [State] = null then [province] else [province]

[lovetolearn]

@amitchandakThanks for the reply. The logic seems right but it doesn't quite work. This is my result: