Fill in BUS Name column from a header

[samahiji]

Hi,

I have the table below in Excel that schedules different buses as per their rout. the column BUS has to be filled with bus name extracted from a header on top of each ROUT (NEXT BUS -) using Power Query.

NEXT BUS - ABC 123 (24-HR)
ROUTE	AREA	CODE	S	BUS
9001	G13	C	Y	ABC 123
9002	E13	O	Y	ABC 123
9012	F12	O	Y	ABC 123
9003	D12	C	Y	ABC 123
NEXT BUS - ABC 456 (24-HR)
ROUTE	AREA	CODE	S	BUS
9109	C21	C	Y	ABC 456
9110	E16	C	Y	ABC 456
NEXT BUS - ABC 789 (24-HR)
ROUTE	AREA	CODE	S	BUS
9212	E14	O	Y	ABC 789
9223	C6	O	Y	ABC 789
9200	C7	C	Y	ABC 789

[AlienSx · Accepted solution]

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

[Anonymous · Accepted solution]

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.

[Omid_Motamedise]

you should use the custom name in excel starting from the row before each table and then use the following formula to access to the buss name.

Source[Column1]{0}

[AlienSx · Accepted solution]

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