A better way to extract Subheading numbers using power query-CodePudding

I am attempting to extract section heading numbers from a column in excel using power query.

I have already achieved this by matching with an existing list. However, I wonder if there is a better way to achieve this in fewer steps.

M Code:

let
    
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    #"Trimmed Text1" = Table.TransformColumns(Source,{{"Column1", PowerTrim, type text}}),
    SectionNumbers = Table.AddColumn(#"Trimmed Text1", "SectionNumber",  (x) => Text.Combine(Table.SelectRows(SectionNumbers, each Text.Contains(x[Column1],[SectionNumbers], Comparer.OrdinalIgnoreCase))[SectionNumbers],", ")),
    #"Split Column by Delimiter2" = Table.SplitColumn(SectionNumbers, "SectionNumber", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.None, true), {"SectionNumber.1", "SectionNumber.2"}),
    #"Added Custom1" = Table.AddColumn(#"Split Column by Delimiter2", "Custom", each if [SectionNumber.2] = null then [SectionNumber.1] else [SectionNumber.2]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Column1", "Custom"})
in
    #"Removed Other Columns"

The Section numbers being matched to can be generated using:

SectionNumbers

let
    Source = {1..16},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each {1..9}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each "."),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom1", {{"Custom", type text}}, "en-GB"),{"Custom", "Custom.1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Column1", type text}}, "en-GB"),{"Column1", "Merged"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"SectionNumbers")
in
    #"Merged Columns1"

Essentially I would like a way of extracting any decimal at the start of a cell, either 15.0. or 15.0, or even 15.0.1 etc.

I have considered using regex i.e. \d \.\d [.] which should work however I have many rows and find that regex sometimes is computationally intensive in this case, so it takes much longer to load than the Above M Code.

CodePudding user response：

Another power query method

Since you know your section numbers you could:

Generate a (buffered) list of all the section numbers
see if the first space-separated part of the string in column 1 exists in the Section Number list.

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
//create list of all serial numbers

    SerialNumbers = List.Buffer(
        let
            Part1 = List.Transform({1..16}, each Text.From(_) & "."),
            Part2 =List.Transform({1..10}, each Text.From(_) & "."),
            sn = List.Accumulate(Part1,{}, (state, current)=>   state &  
                List.Generate(
                ()=>[s=current & Part2{0}, idx=0],
                each [idx] < List.Count(Part2),
                 each [s=current & Part2{[idx] 1}, idx=[idx] 1],
                each [s]))
        in
            sn),


    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
        let 
            x = Text.Split([Column1]," "){0}
        in 
            if List.Contains(SerialNumbers,x) then x else null, type text)
in
    #"Added Custom"

CodePudding user response：

How about

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.PositionOfAny([Column1], {"0".."9"})>=0  then Text.BeforeDelimiter(Text.From([Column1])," ")  else null)
in #"Added Custom"