Edit a "NEW TABLE"created using DAX in query editor

Question

I have existing table which has 30 columns & > 10 Rows. I have a data in one column each row (cell) with structure eg. [123,234,455]. In current table I can split 123,234,455 & append it under one another using edit query option. But with current data structure it will create repetition of all the 30 columns. So I want to subset the data. I used New Table using summarize & pulled only 3 columns which I need. But now it does not allow me to edit it in edit query.

Need help.

I created NEW TABLE with only 3 columns, but it does not allow me to edit it in edit query to split the information. Check section 4 below for more details.

Current data

Name    Code
Tom [123,345,346]
Don [234,543,908]

Expected output

Name    Code
Tom 123
Tom 345
Tom 346
Don 234
Don 543
Don 908

Answer 1

The Thumb Rule is - If Created by DAX, then M cannot be used over it to make any further changes.

When you create a table using a DAX Function, which you have done now (SUMMARIZE function), you will not see them in the edit query pane.

But DAX Can still solve this requirement, I have used your New Table as InputTable,

Create these 3 calculated columns inside the InputTable.

Cleaned Code1 = 
VAR removing_Square_brackets = SUBSTITUTE(Input_Table[Code],"[","")
Var required_code = PATHITEM(SUBSTITUTE(removing_Square_brackets, ",", "|"), 1)
return required_code

Cleaned Code2 = 
VAR removing_Square_brackets = SUBSTITUTE(Input_Table[Code],"[","")
Var required_code = PATHITEM(SUBSTITUTE(removing_Square_brackets, ",", "|"), 2)
return required_code

Cleaned Code3 = 
VAR removing_Square_brackets = SUBSTITUTE(Input_Table[Code],"]","")
Var required_code = PATHITEM(SUBSTITUTE(removing_Square_brackets, ",", "|"), 3)
return required_code

So your table will look like this,

And then now create a new table again with this DAX,

Final_Out_Table = UNION(
SELECTCOLUMNS(Input_Table, "Name",Input_Table[Name], "Code", Input_Table[Cleaned Code1]),
SELECTCOLUMNS(Input_Table, "Name",Input_Table[Name], "Code", Input_Table[Cleaned Code2]),
SELECTCOLUMNS(Input_Table, "Name",Input_Table[Name], "Code", Input_Table[Cleaned Code3])
)

And that gives an output table like this,

Kindly accept the solution, if it solves your problem.

Answer 2

It seems you are confusing Edit Query, which uses Power Query (with the M language), and DAX.

Edit Query will use Power Query to get and transform data. You can split a column like your Code column using the by Delimiter option.

Name    Code
Tom [123,345,346]
Don [234,543,908]

Select the Code column and click the Split command, split by delimiter and select the comma as the delimiter.

After that, you can select the Name column and use the Unpivot (other columns) to arrive at your desired result.

Note that this is NOT DAX at all. DAX is used to analyze data with functions that aggregate and summarize data. See here for an explanation.