Convert a specific cell into a column Power Query

0
votes

I want to populate a new column with a specific cell using Power Query exactly like this: from this -->to this

This task has been taught here: https://exceleratorbi.com.au/convert-a-cell-value-into-a-column-with-power-query/

but I start with a structured table within the worksheet and so the Power Query only reads the table, leaving out the specific cells that I need (which are B2 and B4), as displayed here. Excel Query

So how can I do the same thing?

1
powerquery
Convert the cell to a named range – Ricardo Diaz

1 Answers

0
votes

Here is a complete example, starting with a worksheet like this:

worksheet

Click on the specific cell containing the value with which you want to populate a new column (B2), enter a name in the name box (cellCategory), and press Enter. Right-click the cell and click on Get Data from Table/Range... which opens the Power Query Editor.

category


Open the Advanced Editor, delete everything, enter this line of code and click on Done:

Excel.CurrentWorkbook(){[Name="cellCategory"]}[Content][Column1]{0}

advanced_editor

Named ranges that are loaded in Power Query are automatically transformed to a table. In the line above, [Column1]{0} accesses the value located in Column1 at row index 0 of that table which contains a single cell. So now you have a query that returns the content of that cell.

If your main table isn't yet loaded to Power Query, return to the worksheet, right-click on your table and click on Get Data from Table/Range....

Now, as shown in Step 5 of the linked tutorial, go to the ribbon tab Add Column, click on the button Custom Column, and add a new column named Category filled with the cellCategory query value by entering cellCategory and clicking on OK:

add_column


Here is the result:

result