custom column created in data view not appearing in edit query view

[SatyaVaitla]

i created a custom column with DAX. This is to combine 2 columns to create a new column. My second table also having these two columns and there also i created a similar custom column. now i combine these two tables through a bridge of this unique column.

But the custom column is not appearing in edit query window where i am supposed to delete other columns ( and remove duplicates of combination of the two fields which i concatenated so that i will be having a unique field column).

so what is the solution?

if i can have composite key relation in model view, i can avoid this work. But modeling view is not allowing me to select two fields to give relation.

[Baskar]

Hi Satya,

Whatever u created in DAX its not apear in Query Editor Screen. Sorry 

[Anonymous]

@Baskar if you create some columns/measures in DAX, then make a change in the query editor and apply it, will the columns/measures in DAX still be there?

[sdjensen]

You should try to add you new column in the query editor instead of using DAX. I think when you add a column using dax this happens after the query editor, hence you cannot see this column in the query editor.

[SatyaVaitla]

but in query editor, there is no contextual help to write the formula during addition of new column. Also my concatenate function is not working here

[sdjensen · Accepted solution]

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.

[SatyaVaitla]

even 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

[sdjensen]

If 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]

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.

[Baskar]

Can u please explain your requirement clearly.

Let me know...