Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Need help to extract as per below example:
Date | Required Result |
E1 | E1 |
E2 | E2 |
E3 | E3 |
M2 | 2 |
EE.1 | 1 |
EE.2 | 2 |
EE.11 | 11 |
M11 | 11 |
If data is E1, E2 and E3, all will be extracted into the new column, if not only number will be extracted, some has delimiter some has not.
Thanks
Solved! Go to Solution.
Hi @CJ_96601 ,
Adjust to the below:
if Text.Contains([Date], ".0")
then Text.AfterDelimiter([Date], ".0")
else if Text.Contains([Date], ".")
then Text.AfterDelimiter([Date], ".")
else if Text.Start([Date],1) = "E" and not Text.Contains([Date], ".")
then Text.Start([Date],2)
else
Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Date]),each if Value.Is(Value.FromText(_), type number) then _ else null)))
Best Regards
Lucien
Hi @CJ_96601 ,
New custom column like below:
= Table.AddColumn(#"Added Custom", "Custom.1", each if not Text.Contains([Date], ".") then Text.Range([Date],Text.Length([Date])-1,1)
else if Text.Contains([Date], ".") and Text.Range([Date],1,1)="X" then "X"
else Text.Start([Date],1))
Best Regards
Lucien
You can use an expression like this
= if Text.Contains([Date], ".") then Text.AfterDelimiter([Date], ".") else if Text.Start([Date],1) = "E" and Text.Length([Date]) = 2 then [Date] else Text.End([Date],1)
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks, it works, but i made a mistake on the data source.
Please find correct data sample
Date | Required Result |
E1S | E1 |
E2W | E2 |
E3S | E3 |
M2E | 2 |
EE.1 | 1 |
EE.2 | 2 |
EE.11 | 11 |
M11W | 11 |
Hi @CJ_96601 ,
Test like this :
if Text.Contains([Date], ".")
then Text.AfterDelimiter([Date], ".")
else if Text.Start([Date],1) = "E" and not Text.Contains([Date], ".")
then Text.Start([Date],2)
else
Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Date]),each if Value.Is(Value.FromText(_), type number) then _ else null)))
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSVXSUQpKLSzNLEpNUQhKLS7NKVGK1YlWcjUMBsq4GkI4RuEgjhGEYwyWMQZzfI1cgRyohKueIZBjCOMYociApSByvoaG4VBeLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type text}, {"Required Result", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if Text.Contains([Date], ".")
then Text.AfterDelimiter([Date], ".")
else if Text.Start([Date],1) = "E" and not Text.Contains([Date], ".")
then Text.Start([Date],2)
else
Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Date]),each if Value.Is(Value.FromText(_), type number) then _ else null))))
in
#"Added Custom"
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
Thanks. I have some additional data format. You may refer to the last two items in the table
Date | Required Result |
E1 | E1 |
E2 | E2 |
E3 | E3 |
M2 | 2 |
EE.1 | 1 |
EE.2 | 2 |
EE.11 | 11 |
M11 | 11 |
EE.01 | 1 |
EE.03 | 3 |
Hi @CJ_96601 ,
A little adjust:
if Text.Contains([Date], ".0")
then Text.AfterDelimiter([Date], ".0")
else if Text.Start([Date],1) = "E" and not Text.Contains([Date], ".")
then Text.Start([Date],2)
else
Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Date]),each if Value.Is(Value.FromText(_), type number) then _ else null)))
Return :
Best Regards
Lucien
Thanks, it works well.
May i kindly ask one more adjustments. Please refer to last row.
Date | Required Result |
E1 | E1 |
E2 | E2 |
E3 | E3 |
M2 | 2 |
EE.1 | 1 |
EE.2 | 2 |
EE.11 | 11 |
M11 | 11 |
EE.01 | 1 |
EE.03 | 3 |
EX.A | A |
Hi @CJ_96601 ,
Adjust to the below:
if Text.Contains([Date], ".0")
then Text.AfterDelimiter([Date], ".0")
else if Text.Contains([Date], ".")
then Text.AfterDelimiter([Date], ".")
else if Text.Start([Date],1) = "E" and not Text.Contains([Date], ".")
then Text.Start([Date],2)
else
Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Date]),each if Value.Is(Value.FromText(_), type number) then _ else null)))
Best Regards
Lucien
Thanks.
May i seek your help as well to get the additional column as follows: (Required Result 1)
Date | Required Result | Required Result1 |
E1W | E1 | W |
E2E | E2 | E |
E3S | E3 | S |
M2E | 2 | E |
EE.1 | 1 | E |
WE.2 | 2 | W |
SA.11 | 11 | S |
M11S | 11 | S |
EE.01 | 1 | E |
SA.03 | 3 | S |
EX.A | A | X |
Required result 1
If there is no delimiter get the last letter (S; E; or W)
If there is delimeter and get the first letter (S,E, or W), if the second letter is X get the "X"
Thanks
Hi @CJ_96601 ,
New custom column like below:
= Table.AddColumn(#"Added Custom", "Custom.1", each if not Text.Contains([Date], ".") then Text.Range([Date],Text.Length([Date])-1,1)
else if Text.Contains([Date], ".") and Text.Range([Date],1,1)="X" then "X"
else Text.Start([Date],1))
Best Regards
Lucien
Thanks a lot..
User | Count |
---|---|
83 | |
69 | |
69 | |
66 | |
53 |
User | Count |
---|---|
94 | |
92 | |
91 | |
77 | |
70 |