Forum Discussion

Phoebe_Zhang's avatar
Phoebe_Zhang
Helper I
6 years ago
Solved

how to edit cell value directly in query editor?

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 colu...
Need Help
  • AnkitBI's avatar
    AnkitBI
    6 years ago

    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.

AnkitBI's avatar
AnkitBI
Solution Sage
6 years ago

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's avatar
Phoebe_Zhang
Helper I
6 years ago

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? 

  • AnkitBI's avatar
    AnkitBI
    Solution Sage
    6 years ago

    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's avatar
      Phoebe_Zhang
      Helper I
      6 years ago

      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's avatar
        Jimmy801
        Community Champion
        6 years ago

        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

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    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