Remove (Asterisk "*" And Letter "A" from Date Row)

[2019]

I am having a scenario where I have a table that contain a column as dates (Start), sometimes these dates end with either a letter (“A” or “*”) therefore I want to delete it from the Start dates

I have come up with a formula in Power Query as:

// This Code to Remove "*" and "A" from the Start date row
    #"Custom Start Date" = Table.AddColumn(#"Promoted Headers", "Custom.Start", each let 
            x = Text.End([Start],1),
            y = Text.From(Text.RemoveRange([Start],Text.Length([Start])-1)),
            result =    if x = "A" then y
                   else if x = "*" then  y
                     else Text.From([Start])
        in 
            result),

And it works when the dates end with (“A” or “*”) but there is an error if the date does not end with these letters

I have attached both the Data Source and PBIX file

[AnkitKukreja · Accepted solution]

Hi! @2019 

As there is a saying that there are zillions of ways to solve the problem in PBI. I am not very good with Power Query, but I used a column from the example and got the result. You can copy the below code in a blank query and test.

let
Source = Excel.Workbook(File.Contents("C:\Users\v-akukreja\Downloads\12 Week Data.xlsx"), null, true),
Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Activity ID", type text}, {"Start", type text}, {"Finish", type date}}),
#"Added Custom Column" = Table.AddColumn(#"Changed Type", "Custom", each let splitStart = Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c))([Start]) in Text.Combine({Text.Combine(List.Transform({[Start]}, each Text.Start(_, 7)), "-"), Text.Middle(splitStart{1}?, 5), Text.Middle(splitStart{2}?, 3, 1)}), type text),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom Column",{{"Custom", type date}})
in
#"Changed Type1"

[2019]

@AnkitKukreja 

After further analysis, I have found out there are some dates ending with 02 instead of 22

As shown in below snapshot:

[AnkitKukreja]

Hi! @2019 

I see the same data in the custom column as available in your Start column. I am attaching the pbix for your ref.

[2019]

@AnkitKukreja 

Your method it worked, i am still trying to understand the steps that you followed

[2019]

Thank you for your reply, as matter of fact the current problem if the date is correct and does not end with (“*” or “A”) is where I keep getting the error messages

[Dinesh_Suranga]

@2019 ,

HI,

It does not show me any error in your query.

Have there a space befor the letter A or *?

May I know the error message?

Thank you

[2019]

@Dinesh_Suranga 

There is a space before letter "A"

Attached is a screenshot of the error i have:

[Dinesh_Suranga]

@2019 

In power query space count as one letter. 

So you have to remove last two letters.

Try following code.

// This Code to Remove "*" and "A" from the Start date row
    #"Custom Start Date" = Table.AddColumn(#"Promoted Headers", "Custom.Start", each let 
            x = Text.End([Start],1),
            y = Text.From(Text.RemoveRange([Start],Text.Length([Start])-2,2)),
            result =    if x = "A" then y
                   else if x = "*" then  y
                     else Text.From([Start])
        in 
            result),