Trying to create new column by combining a string + column value

[teaguejs]

Hi,

I am trying to create a new column by adding "String123 - " to the beginning of the value from another column.

[tackytechtom]

Hi @teaguejs,

I have not worked with Direct Query yet, so I tried to read up on it. It seems like it kinda depends on your data source, when you are using Power Query. Have you tried creating the new column in DAX? Does it return the same error message?

This seems a great resource that many other threads were referring to:

Using DirectQuery in Power BI - Power BI | Microsoft Docs

Solved: M for Calculated Column in Direct Query Mode - Microsoft Power BI Community

DirectQuery in Analysis Services 2016 - SQLBI

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[tackytechtom]

Hi @teaguejs ,

And here a solution in DAX:

Right click on the column and press new column. Here the DAX:

Custom = 
"String123 - " & 'Table'[Column] 

Generally, it is better to do such things in Power Query if possible, but since you posted it in here (Desktop), I wasn't sure which one you needed.

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[teaguejs]

I tried this DAX:

new_column =

"String123 - " & 'table_name'[column_name]

And got this error:
"A single value for column 'column_name' in table 'table_name' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

[teaguejs]

So I used "new column" rather than "new measure" and it didnd't error out initially, but it did error out when I tried to add that new field to a current table, so close enough for now

[teaguejs]

The error says:
OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression..

[tackytechtom · Accepted solution]

Hi @teaguejs ,

This seems really tricky because PBI tries to apply the code in a query straight from the data source and depending on the data source itself, it either works or doesn't. I'd suggest you fix that column in the source or you create a view to manipulate the string. Another solution could be to you just use Import mode 🙂 Here a pretty lively discussion about your last error message:

OLE DB or ODBC error: [Expression.Error] We couldn... - Microsoft Power BI Community

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[teaguejs]

I ended up just editing in a CONCAT sql statement in the direct query and that seemed to do the trick, thanks for all your help @tackytechtom 

[tackytechtom]

Hi @teaguejs 

Here a solution in Power Query:

Here the code

= "String123 - " & [Column]

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[teaguejs]

Unfortunately I am using "Direct Query" for the data and that step mentions "This step results in a query that is not supported in DirectQuery mode". Any ideas how to achieve the same result while still using direct query?