Create a new table from columns from existing tables

[rashid0146]

Hi All
 
I have two columns, one is customer ID and 2nd is Customer Parent. I need a 3rd column containg values from first and 2nd as shown below.
 
Customer ID        Customer Parent ID        3rd Column (I Want to Create in Power BI)
0001                     0010                                  0010
0002                                                               0002
0003                     0010                                  0010
0004                                                               0004
0005                     0011                                  0011
0006                                                               0006
0007                                                               0007

I was provided by with this solution

Column3 = IF(ISBLANK([Customer Parent ID]),[Customer ID],[Customer Parent ID])

I applied above formula Column3 = IF(ISBLANK([Customer Parent ID]),[Customer ID],[Customer Parent ID]) but it is not picking the value for blak field. I mean it is giving the following results shown in 3rd column. One thing to note that source of data is SQL server. I guess ISBLANK function si not recognizing the Blank fields in Customer Parent ID table as blank that is why returning the results below.

Customer ID        Customer Parent ID        3rd Column (I Want to Create in Power BI)
0001                     0010                                  0010
0002                                                              
0003                     0010                                  0010
0004                                                              
0005                     0011                                  0011
0006                                                              
0007                                                              

Please help me in getting out of this

Best Regards

Rashid Anwar

[ImkeF · Accepted solution]

I'd recommend to clean your data before loading into the datamodel:

In the query editor, select the column "Customer Parent ID" and go to Transform -> Text Column -> Format -> Clean

That will remove any non-printable characters from your data that could cause the problem.

If the "blank-to-be"-field don't show up with a "null" -value (in italics), then they are not really blank and your DAX-functions will not work properly. In that case try the following:

Check your column again, go to Transform -> Any Column -> Replace Values and replace "nothing" by "null" like this:

[v-chuncz-msft · Accepted solution]

@rashid0146,

Use DAX below instead.

Column3 =
IF ( [Customer Parent ID] = "", [Customer ID], [Customer Parent ID] )

[ImkeF · Accepted solution]

I'd recommend to clean your data before loading into the datamodel:

In the query editor, select the column "Customer Parent ID" and go to Transform -> Text Column -> Format -> Clean

That will remove any non-printable characters from your data that could cause the problem.

If the "blank-to-be"-field don't show up with a "null" -value (in italics), then they are not really blank and your DAX-functions will not work properly. In that case try the following:

Check your column again, go to Transform -> Any Column -> Replace Values and replace "nothing" by "null" like this:

[fhill]

Can you convert the columns to whole numbers instead of text?  This might help remove whatever 'non blank' value is confusing your query?