Fill in BUS Name column from a header

from

 

 

 

 

 

 

 

 

let
    fx =(tbl) => Table.FromColumns(
        Table.ToColumns(tbl) & 
        {
            {null, "BUS"} & 
            List.Repeat(
                {Text.BetweenDelimiters(Table.FirstValue(tbl), "- ", " (")}, 
                List.Count(List.Select(tbl[Column1], (x) => x is number))
            )
        }
    ),
    Source = Excel.CurrentWorkbook(){[Name="buses"]}[Content],
    group = Table.Group(
        Source, 
        "Column1", 
        {"x", fx}, 
        GroupKind.Local, 
        (s, c) => Number.From(c is text and Text.StartsWith(c, "NEXT BUS"))
    ),
    result = Table.Combine(group[x])
in
    result

 

 

 

to

Hi,

Thanks for the solution AlienSx  and Omid_Motamedise  offered, and i want to offer some more information for user to refer to.

hello samahiji , you can create a blank query and put the following code to advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZDLCsIwEEV/ZchKoYWZaWzNso+oKwN9gCX0/3/DZFSadmcXk3DPhHC43qunfY3QTAPkUDctEBdwYp0/+rPKFHxHLZlXvZtGG0Ld2zpcretiGmRnECmEOxVxE2b+YQ7BCnYJpohvcqYY47tO8PoJJCIgZOesL+UhZ0ITGdPWmQjFufzPorqaQxZMn4r0tgtmqbLcUYxubbWqLW8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column1],"NEXT BUS") then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Replaced Value" = Table.ReplaceValue(#"Filled Down",each [Custom],each if Text.Contains([Column1],"NEXT BUS") or [Column1]=" " then "" else if [Column1]="ROUTE" then "BUS" else Text.BetweenDelimiters([Custom],"-","(") ,Replacer.ReplaceValue,{"Custom"}),
    #"Trimmed Text" = Table.TransformColumns(#"Replaced Value",{{"Custom", Text.Trim, type text}})
in
    #"Trimmed Text"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.