Data in form of Text & Number, trying to remove just the Text using direct query.

[maxs3]

Hello,

I have a column called "L_n" and data within the column is repeated in the form of "Item12314".

I need to remove the "Item" and just store "12314" in the column.

This can be done if I directly import, and transform the data, but I cannot seem to figure out how to do this through direct query.

Thank you!

[maxs3]

Hello,

I have a column called "L_n" and data within the column is repeated in the form of "Item12314".

I need to remove the "Item" and just store "12314" in the column.

This can be done if I directly import, and transform the data, but I cannot seem to figure out how to do this through direct query.

Thank you!

[maxs3 · Accepted solution]

This has been solved by using the function "New Column" instead of "New Measure"

[OwenAuger]

Hi @maxs3 

Are you querying SQL Server?

I did some tests myself with DirectQuery on SQL Server 2008, and I was able to get both of these methods working with DirectQuery:

• Power Query: Right-click column header > Replace Values > Put Item in 1st box and leave 2nd box blank
  Creates code like:
  = Table.ReplaceValue(PreviousStep,"Item","",Replacer.ReplaceText,{"L_n"})
• DAX: Create calculated column using the SUBSTITUTE function:
  CalcColumn = SUBSTITUTE ( YourTable[L_n], "Item", "" )

Do either of these work for you?

Regards,

Owen