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.
Hey guys,
i have this kinda data:
item |
1 |
1.1 |
1.2 |
1.2.1 |
1.2.2 |
1.3 |
1.3.1 |
1.3.2 |
1.3.3 |
1.3.4 |
1.3.4.1 |
1.3.4.2 |
1.3.4.2.1 |
1.3.4.2.1 |
2 |
2.1 |
i want to extract 1 number before the last "."(dot/point) (between the last two ".")
The 'new' column should be like this:
item | extracted number |
1 | null |
1.1 | 1 |
1.2 | 1 |
1.2.1 | 2 |
1.2.2 | 2 |
1.3 | 1 |
1.3.1 | 3 |
1.3.2 | 3 |
1.3.3 | 3 |
1.3.4 | 3 |
1.3.4.1 | 4 |
1.3.4.2 | 4 |
1.3.4.2.1 | 2 |
1.3.4.2.1 | 2 |
2 | null |
2.1 | 2 |
note: some items of it ends up with two characters... 22.1.1.10
how can i do this kinda thing?
Solved! Go to Solution.
@Anonymous ,
Did I answer your question? Mark my post as a solution!
Ricardo
Maybe:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyixJzVWK1YlWMoSQejDaCEYjROBixjAaLmeMkEOSNUGwkFSaIKk1QTIfzosFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"item", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "item", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"item.1", "item.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"item.1", type text}, {"item.2", Int64.Type}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type2", "item.1", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"item.1.1", "item.1.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"item.1.1", type text}, {"item.1.2", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type3", "Custom", each if [item.2] = null and [item.1.2] = null then [item.1.1] else if [item.1.2] = null then [item.2] else [item.1.2]),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}})
in
#"Changed Type4"
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 | |
100 | |
86 | |
64 |