Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
"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.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks @MFelix,
Yes, that is actually easy but if you look closely, I don't think that would work...
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHere's how I envisioned it but don't know how to implement:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |