Fill Data in Blank Rows (Power Query Editor)

[venug20]

Hi Every one,

I have sample data like below table.. i want to fill gaps in power bi...

Thanks in Advance...

Company	City
LifeLock	Tempe
LifeLock
ChosenList.com	Scottsdale
Ch osenList.com
Facebook	Palo Alto
Facebook
Facebook
Geni
Geni	West Hollywood
Sla cker	San Diego
Slacker
Technorati
Technorati	San Francisco
Mahalo	Santa Monica
Mahalo
Veoh	San Diego
Veoh
Jingle Networks	Menlo Park
Jingle Networks
Prosper	San Francisco
Prosper
Jajah
Jajah	Mountain View
Ustream	Mountain View
Ustream
Aggreg ate Knowledge	San Mateo
Aggregate Knowledge
Sphere
Sphere	San Francisco
MeeVee	Burlingame
MeeVee

[Zubair_Muhammad]

@venug20

Is this your expected result

Company	City
LifeLock	Tempe
LifeLock	Tempe
ChosenList.com	Scottsdale
ChosenList.com	Scottsdale
Facebook	Palo Alto
Facebook	Palo Alto
Facebook	Palo Alto
Geni	West Hollywood
Geni	West Hollywood
Slacker	San Diego
Slacker	San Diego
Technorati	San Francisco
Technorati	San Francisco
Mahalo	Santa Monica
Mahalo	Santa Monica
Veoh	San Diego
Veoh	San Diego
Jingle Networks	Menlo Park
Jingle Networks	Menlo Park
Prosper	San Francisco
Prosper	San Francisco
Jajah	Mountain View
Jajah	Mountain View
Ustream	Mountain View
Ustream	Mountain View
Aggregate Knowledge	San Mateo
Aggregate Knowledge	San Mateo
Sphere	San Francisco
Sphere	San Francisco
MeeVee	Burlingame
MeeVee	Burlingame

[venug20]

@Zubair_Muhammad

Yes, Exactly i expected same like which you have filled data...

[Zubair_Muhammad]

@venug20

I used this

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"City", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Trimmed Text" = Table.TransformColumns(#"Added Index",{{"Company", Text.Trim, type text}, {"City", Text.Trim, type text}}),
    #"Sorted Rows" = Table.Sort(#"Trimmed Text",{{"Company", Order.Ascending}, {"City", Order.Descending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"City"}),
    #"Sorted Rows1" = Table.Sort(#"Filled Down",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Index"})
in
    #"Removed Columns"

[Zubair_Muhammad · Accepted solution]

@venug20

Excel file attached as well

[Zubair_Muhammad]

@venug20

Please see if this helps you

https://www.excelcampus.com/library/fill-down-blank-null-cells-power-query/

[venug20]

Hi Zubair,

My city should be fill based on "Country". Not like "Fill UP" or "Fill Down"....

Pls Can any one help on this....