cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Impactful Individual
Impactful Individual

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

Hi Community,

 

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

 

RenameCell11.PNG

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

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

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.

 

Column Title.png

 

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

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

8 REPLIES 8
Highlighted
Super User III
Super User III

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

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Impactful Individual
Impactful Individual

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

Thanks @MFelix,

 

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

 

RenameCell11_endresult.PNG

Highlighted
Super User III
Super User III

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

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Impactful Individual
Impactful Individual

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

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

 

RenameCell11_step-by-step.PNG

Highlighted
Microsoft
Microsoft

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

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? Smiley LOL

 

Regards

Highlighted
Impactful Individual
Impactful Individual

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

@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? Smiley LOL"

 

 

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.

Highlighted
Super User III
Super User III

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

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.

 

Column Title.png

 

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

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Highlighted
Impactful Individual
Impactful Individual

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

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

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors