how to edit cell value directly in query editor?

[Phoebe_Zhang]

dear expert,

i have a column with year month value like'201912', i want to add a new column with the first date of year month with date format as result '12/01/2019".

i know we can add custmer column with formular, however, how can i type the value directly in cell like excel ? that when i direct input 12/01/2019 in the first cell then impact all the cells in column automaticly.

thank you.

[AnkitBI]

Hi @Phoebe_Zhang 

Unlike Excel Power BI/Power Query works on complete Column. Once you create a Custom column , the formula will be applied to all the Cells of the base column instead of single cell.

Hope I have answered your question.

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.

[Phoebe_Zhang]

hi there,

i want to type the value directly in cell itself instead of using formula, and after mannuall input two cells value in cloumn, all the additional cells change accordling with typing "Enter" button. which is same as working in excel,

would you plese kindly advise? 

[Jimmy801]

Hello @Phoebe_Zhang 

to have one table, were you can simple add in one screen a new column and edit will never work. Or you use Excel as data source and you edit it there and refresh your query, or you have a additional column in a different step (only this column). Here an example of what I mean

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDXMzDUMzJQitUBciyQOYZ6BkbIMsYwjqGBHpAP4sQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    Additional   = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDXMzDUMzJQitUBciyQOYZ6BkbIMsZwjqmegQmYEwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"First day of year" = _t]),
    CombineBoth = Table.FromColumns
    (
        Table.ToColumns(Source)& Table.ToColumns(Additional),
        Table.ColumnNames(Source)&Table.ColumnNames(Additional)
    )
in
    CombineBoth

When you pasting this to the advanced editor, you can edit both columns by clicking on this symbol 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[AnkitBI · Accepted solution]

Hi @Phoebe_Zhang 

Drag and extend formula to all cell is not possible with Power Query. What you can try is to use "Column from Example". It will automatically create formula required based on sample values. Keep in mind it will require 3-4 sample values to provide the expected result.

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.

[Phoebe_Zhang]

thanks,

but i want to add the column and change the data based on filed period, when i use the method you mentioned, only one fixed value appeared. there is no reladtion with field "Period".

can you please help?

[Jimmy801]

Hello @Phoebe_Zhang 

as I already mentioned, in  my oppinion there is no way to do this in the editor. You can create both column manualy. But adding data on a existing query won't work. What if the sort order of the data query is changing? If you would use Excel as working platform, there would be a way to do something like this on a spreadsheet, bot no on power query.

Jimmy

[Phoebe_Zhang]

thans, i think i got the solution~~thank you very much~