PowerBI D365 Report. I need a populate a text column based on a number column

[mgibson44]

Hello guys, 

I'm trying to build a PowerBI report that is extracting data from D365. I have a column that is returning a numerical value based on the ID number but in D365 that is a text field. I would like to add a columun that will populate the text name based on the ID number that in the related column. What is the best way to do this?

Thank you. 

[mgibson44]

Hello guys, 

I'm trying to build a PowerBI report that is extracting data from D365. I have a column that is returning a numerical value based on the ID number but in D365 that is a text field. I would like to add a columun that will populate the text name based on the ID number that in the related column. What is the best way to do this?

Thank you. 

[v-shex-msft · Accepted solution]

Hi @mgibson44,

You can try to use lookupvalue to create new calculate column based known id if they not contains multiple matches results. Otherwise you need use calculate and concatenate function to combine correspond result.

Sample formula for calculate column:

formula = LOOKUPVALUE ( Table[ColumnName], Table[ID], [ID] )

formula2
    = CALCULATE (
        CONCATENATEX ( VALUES ( 'Table'[ColumnName] ), [ColumnName], "," ),
        'filter conditons'
    )

Regards,

Xiaoxin Sheng