Forum Discussion
Create a new table from columns from existing tables
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
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:
Use DAX below instead.
Column3 = IF ( [Customer Parent ID] = "", [Customer ID], [Customer Parent ID] )
3 Replies
- ImkeF
Community Champion
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
Resident Rockstar
Can you convert the columns to whole numbers instead of text? This might help remove whatever 'non blank' value is confusing your query?
v-chuncz-msftCommunity Support
Use DAX below instead.
Column3 = IF ( [Customer Parent ID] = "", [Customer ID], [Customer Parent ID] )
