Format Data

[Anonymous]

Hey guys, 

I have the UDER ID data item as 'DB10098989' in my data source. I am using direct query (import mode). How can I format that to get rid of 'DB' in front of it and have just the numeric part of it left?

Thanks, 

Sona

[amitchandak · Accepted solution]

@Anonymous , I checked out left/right/len is dax allowed function for column and measure in direct query. I tested it in direct query mode

Column = right([column],LEN([column])-2)

Tested in Sql Server direct query

Please refer the supported functions : https://docs.microsoft.com/en-us/analysis-services/tabular-models/dax-formula-compatibility-in-direc...

[v-xicai · Accepted solution]

Hi @Anonymous ,

You may go to Add column -> Custom column, create custom column like codes below.

= Table.AddColumn(Source, “Number”, each Text.Select([UDER ID], {“0”..”9″}))

Best Regards,

Amy 

Community Support Team _ Amy

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

[Anonymous]

It worked fine, Amy!

Thanks. I accepted it as a solution. 

[amitchandak · Accepted solution]

@Anonymous , I checked out left/right/len is dax allowed function for column and measure in direct query. I tested it in direct query mode

Column = right([column],LEN([column])-2)

Tested in Sql Server direct query

Please refer the supported functions : https://docs.microsoft.com/en-us/analysis-services/tabular-models/dax-formula-compatibility-in-direc...

[Anonymous]

Thanks a lot. It worked fine.

I accepted it as a solution. 

[lbendlin]

Where do you want to do the transformation, in Power Query or in DAX?

Will it always be DB followed by a number? (in that case you could replace DB with an empty string)

Will it always be two characters? in that case you can use substrings etc.

[Anonymous]

Hey @lbendlin , 

Thanks for a quick response. 

I want the change in the Power Query.

It will always be DB followed by a 8-digit number. 

Yes, it will always be two characters. 

Thanks, 

Sona

[lbendlin]

= Table.ReplaceValue(#"previous step","DB","",Replacer.ReplaceText,{"Column Name"})

[Anonymous]

hey @lbendlin , 

The 'Previous Step' part is giving me issues.