Split/Extract text from last delimiter - right to left

[Anonymous]

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?

[camargos88 · Accepted solution]

@Anonymous ,

Did I answer your question? Mark my post as a solution!
Ricardo

[Greg_Deckler]

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"