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.
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
Solved! Go to 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"
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"
After further analysis, I have found out there are some dates ending with 02 instead of 22
As shown in below snapshot:
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.
Your method it worked, i am still trying to understand the steps that you followed
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
@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
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),
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.