PowerQuery | Rename a Cell (Cell(1,1)

[ovetteabejuela]

Hi Community,

Another simple but tricky one right here(for me).

I want to promote those 2 top rows into headers... but look at column 1. How do I remove the date part or the whole ( : m/d/yy ) part before promoting it to a header.

[v-ljerr-msft]

Hi @ovetteabejuela,

I don't think there is an easy way to edit an individual value using Query Editor currently. Here is a similar thread for your reference.

By the way, may I know why you want to edit the cell value before promoting it into a header? Why not promote it into headers first, then just rename the promoted header name? 

Regards

[ovetteabejuela]

@v-ljerr-msft,

"By the way, may I know why you want to edit the cell value before promoting it into a header? Why not promote it into headers first, then just rename the promoted header name? "

This was an option for me, but how do I get rid of the : m/d/yy part? it's not always going to be 6/1/17 it could be something else everytime.

[MFelix · Accepted solution]

Hi @ovetteabejuela,

Taking into account that you only need the first row to be change follow this steps:

- Add Index column (if you use the automatic index will start at zero)

- Add custom column with following formula

 if [Index] = 0 then
   Text.Start([Column1],4)
else
  Text.End([Column1],Text.Length([Column1])-6)

The Text.Start with 4 letter will give you the date the Text.End is the full text lenght minus 6 character ("Date" = 4 + ": " = 2) 

- Then remove the Index and Column one

- Promote headers

See image below with the step by step and M code so you can test it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSbVSMDDUNzDTNzIwNFfSUQpKLcssTk1RitXBKm1qYIBXKhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index] = 0 then
Text.Start([Column1],4)
else
Text.End([Column1],Text.Length([Column1])-6)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1", "Index"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true])
in
    #"Promoted Headers"

Regards,

MFelix

[ovetteabejuela]

Thanks @MFelix for putting that together, worked very well.

[MFelix]

Hi @ovetteabejuela,

Use the Split columns option in the Tansform tab and then select By number of Characther in this case 4 then you will get two columns one with Date in all of the values and another with :m/d/yy them simply delete the last column.

Regards,

MFelix

[ovetteabejuela]

Thanks @MFelix,

Yes, that is actually easy but if you look closely, I don't think that would work... 

[MFelix]

Hi @ovetteabejuela,

Bu I don't understand what is the final result you want? You wrote "How do I remove the date part or the whole ( : m/d/yy )" what is the part you want to save in your column? the date part or the "Date:" part?

Sorry for making this questions but I getting confused in your messages

Regards,

MFelix

[ovetteabejuela]

Here's how I envisioned it but don't know how to implement: