cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Why Custom Columns are missing from Edit Query view?

I'm created new column in data view with Modelling->New Column.

However when I click "Edit Query" I don't see new column created.

 

This is issue for me, because I would like to do Append in Edit Query view. New column created are now missing from appended table.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Why Custom Columns are missing from Edit Query view?

Hi @kenny_i ,

 

Currently, the calculated column or calculated table which is created using DAX function won't be displayed in Query Editor.

 

If you need to use a new column in Query Editor, you may go to Add column->Custom column, add a new custom column in Query Editor, see the similar case1 and case2. Then you may use Append feature to combine tables.

 

If you would like to create column using DAX and need to combine tables into one like Append feature do, you can use function RELATED ,LOOKUPVALUE or FIRSTNONBLANK to get new calculated column, then use UNION function to get combined table.

 

For example:

 

 

Column1=RELATED([one side field of relationship])

Column1= LOOKUPVALUE(tableA[goal field],  tableA[linked field],  tableB[linked field]))

Column1= CALCULATE (FIRSTNONBLANK ( tableA[goal field], 1 ),FILTER ( ALL ( tableA), tableA[linked field] = tableB[linked field] ))
 

New Table1= UNION(
SELECTCOLUMNS('Table1',"Name1",[Description],"Name2",[Amount]),
SELECTCOLUMNS('Table2',"Name1",[Description],"Name2",[Amount]),
SELECTCOLUMNS('Table3',"Name1",[Description],"Name2",[Amount]))
 
New Table1= UNION(Table1,Table2,Table3)

 

 

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.

View solution in original post

7 REPLIES 7
Highlighted
Super User I
Super User I

Re: Why Custom Columns are missing from Edit Query view?

Hi @kenny_i 

 

Columns created within Power BI (in the modelling view) are not visible in the Power Query editor.

 

See this article for an explanation: www.sqlbi.com/articles/comparing-dax-calculated-columns-with-power-query-computed-columns/ 

 

Best regards,

Martyn

 

If I answered your question, please help others by accepting it as a solution.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper IV
Helper IV

Re: Why Custom Columns are missing from Edit Query view?

Is it only option to create column with Power Query if I want to use the column in Append?

Highlighted
Super User I
Super User I

Re: Why Custom Columns are missing from Edit Query view?

@kenny_i 

 

Yes, the column must exisit in Power Query to be used with Append.

 

If it's not possible to create the column in Power Query, the you could create it in DAX and then create a calculated table using the UNION function to combine the 2 tables.

 

Best regards,

Martyn

 

If I answered your question, please help others by accepting it as a solution.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper IV
Helper IV

Re: Why Custom Columns are missing from Edit Query view?

I create calculated column in data view, but problem is that I don't see those DAX based columns in "Edit Query" and columns are missing when I do Append.

 

Who could help me? So I need to create column to table, which fetch value from another table. Then I need to append table to another table.

Highlighted
Post Partisan
Post Partisan

Re: Why Custom Columns are missing from Edit Query view?

 

  • Select any cell within your data range.

  • Go to Query Tools > Query > Edit Query.

  • In the Query Editor pane select Add Column > Column From Examples > choose From All Columns, or From Selection. For this example, we'll use the From all columns option.

     

    The Query Editor will open the Add Columns From Examples pane with a new, blank column on the right.

     
  • Enter a sample value for the new column data you want, then press Ctrl+Enter. In this case, we typed Montgomery, AL to join each capital with its respective state. Power Query will complete the rest of the column for you.

     

    If you need to make any edits to the new column, you can double-click any text field and edit it, then press Ctrl+Enter when you're done. Power Query will display the transformation steps above the data.

     
  • When you're done, press OK to confirm your actions, and Power Query will complete the transformation, returning to the Power Query Editor. You'll see the step added to the Applied Steps section in the Editor.

I hope this information helps!
Regards,

Lewis

 

Highlighted
Helper IV
Helper IV

Re: Why Custom Columns are missing from Edit Query view?

Let say that I have DAX based Custom Column named "MachineId". My problem is that I don't see it in "Edit Query" View. I would need to Append  the table next with other table.

 

Where I can add? I cannot find any selection.

 

NewColumn.png

 

 

 

 

Highlighted
Community Support
Community Support

Re: Why Custom Columns are missing from Edit Query view?

Hi @kenny_i ,

 

Currently, the calculated column or calculated table which is created using DAX function won't be displayed in Query Editor.

 

If you need to use a new column in Query Editor, you may go to Add column->Custom column, add a new custom column in Query Editor, see the similar case1 and case2. Then you may use Append feature to combine tables.

 

If you would like to create column using DAX and need to combine tables into one like Append feature do, you can use function RELATED ,LOOKUPVALUE or FIRSTNONBLANK to get new calculated column, then use UNION function to get combined table.

 

For example:

 

 

Column1=RELATED([one side field of relationship])

Column1= LOOKUPVALUE(tableA[goal field],  tableA[linked field],  tableB[linked field]))

Column1= CALCULATE (FIRSTNONBLANK ( tableA[goal field], 1 ),FILTER ( ALL ( tableA), tableA[linked field] = tableB[linked field] ))
 

New Table1= UNION(
SELECTCOLUMNS('Table1',"Name1",[Description],"Name2",[Amount]),
SELECTCOLUMNS('Table2',"Name1",[Description],"Name2",[Amount]),
SELECTCOLUMNS('Table3',"Name1",[Description],"Name2",[Amount]))
 
New Table1= UNION(Table1,Table2,Table3)

 

 

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.

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors