Fill Down the Succeeding Rows

[Anonymous]

Here's what my data looks like.

Column A	Column B
DivisionA
EmployeeNameA	xxxx
	xxxx
	xxxx
	xxxx
EmployeeNameB	xxxx
	xxxx
EmployeeNameC	xxxx
	xxxx
	xxxx
DivisionB
EmployeeNameD	xxxx
	xxxx
	xxxx
EmployeeNameE	xxxx
	xxxx

Here's what my desired table should look like:

Column A	Column B	Column C
DivisionA	EmployeeNameA	xxxx
DivisionA	EmployeeNameA	xxxx
DivisionA	EmployeeNameA	xxxx
DivisionA	EmployeeNameA	xxxx
DivisionA	EmployeeNameB	xxxx
DivisionA	EmployeeNameB	xxxx
DivisionA	EmployeeNameC	xxxx
DivisionA	EmployeeNameC	xxxx
DivisionA	EmployeeNameC	xxxx
DivisionB	EmployeeNameD	xxxx
DivisionB	EmployeeNameD	xxxx
DivisionB	EmployeeNameD	xxxx
DivisionB	EmployeeNameE	xxxx
DivisionB	EmployeeNameE	xxxx

[v-yingjl · Accepted solution]

Hi @Anonymous ,

You can achieve it in Power Query, try this query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsksyyzOzM9zVNJRUorViVZyzS3Iya9MTfVLzE0FCVYAAViCGDayZidiFDkTYyrMjU7Y3OhCqrtcsSqKBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Column A", "Column B"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each if [Column B] = null then [Column A] else null, type text),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Column B"}),
    #"Filled Down" = Table.FillDown(#"Filled Up",{"Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"Custom", "Column A"}, {"Column B", "Column C"}, {"Column A", "Column B"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Column A", "Column B", "Column C"}),
    #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([Column B] <> "DivisionA" and [Column B] <> "DivisionB")),
    #"Filled Down1" = Table.FillDown(#"Filtered Rows",{"Column B"})
in
    #"Filled Down1"

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Fill down is possible in Power Query like excel, But this division row is a data clean up problem, I doubt an easy way