Forum Discussion
Advocate IIcustom column created in data view not appearing in edit query view
If you tell us a little more about the columns you are trying to concatenate then we can help you with the code you need to do this.
What are the names of the columns and what are their data type?
if they are both text then it is pretty straight forward to do this. Just go to the Add Column pane and press Add Custom Column. Then give you column a name and in the Available columns locate the first column you want in your concatenated column and double click it then add a & and find the 2nd column and double click it and select okay.
The final result could look something like this: = [column1] & [Column2]
if you want some kind of delimiter between the 2 columns you can add this like this [column1] & "-" & [Column2]
if any of the values are a number then you need to format this as a text in the formula you can do this with the Number.ToText function (and you need to right it like this - M is case sensitive so number.totext will return an error) - so lets say column1 is a number then the formula will be like this: Number.ToText([column1]) & "-" & [Column2]
I hope this helps you a little... if you need help with M then this page have all the functions and shows examples of how to use them.
Advocate IIeven though it solved my present requirement, columns added with DAX in data view should be available in query view. Otherwise, many cases it is no use creating new columns in data view and again creating the same in query view that is not as superior or user awareness as DAX
Solution SageIf would advise anyone that is new to Power BI to make all their ETL work in Query Editor which is made for this purpose and then keep DAX for adding measures and handling relationships in the data model. I would never add new columns to my model with DAX I would not change names of columns in DAX - this is maybe because when working with Power Query and Power Pivot in Excel it would break your model and destroy your data load if you made small changes to your table in Power Pivot instead of Power Query, so I just made it a rule of mine to always do those kind of things in Power Editor (Power Query).
There is a perfectly good reason why columns added by DAX is not present in the Query Editor - everything you do in DAX happens after the Query Editor is done with it's work. You need to see these as 2 different steps - first the Query Editor will do its work and then DAX modeling happens on top of it. Someone please correct me if I am wrong about this.
- SatyaVaitla
I liked the reply but i disagree with logic. In iterative work, every time you will not go to the first part to start a sequential process. whatever we do in the subsequent process should not be waste when you re visit the initial part to incorporate the feed back.
Even in power BI, if i rename a column in data view, the same is applied in power query. and the same should be with my new columns with dax also.
i am sure this will be there in the future power BI.
- ImkeF
Community Champion
Just a guess: If DAX was just for Power BI, we wouldn't have calculated columns with DAX in there.
But as it shall be compatible with (old) Excel and SSAS tabular, we have them (yes, there should be a warning sign, that they can only used in DAX unlike the query/M ones).
I would be very happy (for other reasons) if it wasn't a one-way-street, but very much doubt that this will change.
