locked
Replace date with "" in title column beginning with date using power query RRS feed

  • Question

  • Hi,

    In Title column my title starts with  "20181005 XYZ ABC - FY18"

    this bold text is nothing but refresh date and refresh can happen any time as it's manual. So issue is i want it to remove automatically instead showing it in data after refresh & going back to query then replace. please let me know answer if it's possible?

    ID Title

     1   20181005 XYX ABC - FY18

     2   20180406 abcd sdfsd - FY18

     3   20171010 sdfgsd egwhjn - FY17

    Thanks   


    • Edited by KailasS698 Wednesday, August 1, 2018 3:35 AM
    Tuesday, July 31, 2018 11:55 AM

Answers

  • let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYkulWJ1oJWMgywSITcE8UyDLDIgNDZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"20181005 XYZ ABC - FY18" = _t, OtherColumn = _t, #"20170802 XYZ ABC - FY17" = _t]),
        AllColumnNames = Table.ColumnNames( Source ),
        OldColumnNames = List.Select(AllColumnNames, each try Number.From( Text.Start( _, 8) ) >0 otherwise null),
        NewColumnNames = List.Transform( OldColumnNames, each Text.End(_, Text.Length(_)-9) ),
        ListToPass = List.Zip( {OldColumnNames, NewColumnNames}),
        Custom1 = Source,
        #"Renamed Columns" = Table.RenameColumns(Custom1,ListToPass)
    in
        #"Renamed Columns"
    There are a few other ways to accomplish this, but here's a way for you to see a step by step of what's going on and what needs to happen in order to accomplish your desired output.
    Tuesday, July 31, 2018 1:33 PM

All replies

  • let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYkulWJ1oJWMgywSITcE8UyDLDIgNDZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"20181005 XYZ ABC - FY18" = _t, OtherColumn = _t, #"20170802 XYZ ABC - FY17" = _t]),
        AllColumnNames = Table.ColumnNames( Source ),
        OldColumnNames = List.Select(AllColumnNames, each try Number.From( Text.Start( _, 8) ) >0 otherwise null),
        NewColumnNames = List.Transform( OldColumnNames, each Text.End(_, Text.Length(_)-9) ),
        ListToPass = List.Zip( {OldColumnNames, NewColumnNames}),
        Custom1 = Source,
        #"Renamed Columns" = Table.RenameColumns(Custom1,ListToPass)
    in
        #"Renamed Columns"
    There are a few other ways to accomplish this, but here's a way for you to see a step by step of what's going on and what needs to happen in order to accomplish your desired output.
    Tuesday, July 31, 2018 1:33 PM
  • Thanks Miguel for your reply, but it's not working as my column name is Title in that rows are present like below:

    ID Title

     1   20181005 XYX ABC - FY18

     2   20180406 abcd sdfsd - FY18

     3   20171010 sdfgsd egwhjn - FY17

    please refer above example:

    In Title column my title starts with  "20181005 XYZ ABC - FY18"

    this bold text is nothing but refresh date and refresh can happen any time as it's manual. So issue is i want it to remove automatically instead showing it in data after refresh & going back to query then replace. please let me know answer if it's possible?


    Kailas Swami

    Wednesday, August 1, 2018 3:34 AM
  • You can add the following step to your code:

    TransfromedTitleValues = Table.TransformColumns(<PreviousStepName>, {{"Title", each Text.AfterDelimiter(_, " ", 0), type text}})
    

    • Proposed as answer by Miguel Escobar Sunday, August 19, 2018 5:16 AM
    Wednesday, August 1, 2018 1:23 PM