Group by Rows then FillUp & FillDown for multiple columns

[Anonymous]

Hello,

I'm trying to clean data on a project database where incremental information is added over time:

Project NameDateRegionStyleSize

CAM	1/21/2020	Africa		Large
CAM	6/10/2020		Modern
CAM	10/3/2020			Large
ABC	1/3/2020	North America	Modern
ABC	3/15/2020			Small
ABC	7/7/2020		Modern	Small
DON	2/7/2020
DON	3/24/2020	Asia	Classic
DON	4/23/2020			Small
DON	5/15/2020

With the desired output requiring FillUp + FillDown by Project Name

Project Name	Date	Region	Style	Size
CAM	1/21/2020	Africa	Modern	Large
CAM	6/10/2020	Africa	Modern	Large
CAM	10/3/2020	Africa	Modern	Large
ABC	1/3/2020	North America	Modern	Small
ABC	3/15/2020	North America	Modern	Small
ABC	7/7/2020	North America	Modern	Small
DON	2/7/2020	Asia	Classic	Small
DON	3/24/2020	Asia	Classic	Small
DON	4/23/2020	Asia	Classic	Small
DON	5/15/2020	Asia	Classic	Small

I've manged to source a grouped rows formula that allows for one fill action in a single table column:

#"Grouped Rows" = Table.Group(#"Changed Type", {"Project Name"}, {{"Project Name.1", each Table.FillDown(_,{"Region"}), type table [Project Name=text, Date=datetime, Region=text, Style=text, Size=text]}})

And would like to check if how to expand this to multiple columns and fill in both directions as well.

Other solutions also welcomed.

Thank you.

[Anonymous]

Thanks for the solutions @PhilipTreacy  and @shaowu459 

I checked that they both work for the sample data set I've given, and I apologise for for giving a poor sample, but @shaowu459 's solution is the ideal one when combined with Group by Rows (Replaced "Source" with "_") + Expanded Rows (see below).

The reason being if any of the columns are all null for a single Project (should have left one part blank in the initial request), then it wouldn't have been filled down with data from other Projects.

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Project Name"}, {{"Project Name.1", each Table.ReverseRows(Table.FillDown(Table.ReverseRows(Table.FillDown( _ ,{"Region","Style","Size"})),{"Region","Style","Size"})), type table [Project Name=text, Date=datetime, Region=text, Style=text, Size=text]}}),
#"Expanded Project Name.1" = Table.ExpandTableColumn(#"Grouped Rows", "Project Name.1", {"Date", "Region", "Style", "Size"}, {"Date", "Region", "Style", "Size"}),
in
#"Expanded Project Name.1"

So thanks very much for the guidance!!

[shaowu459]

Glad we could help😋

[shaowu459 · Accepted solution]

Hi @Anonymous , try this. Source is the table you upload to PQ editor.

= Table.ReverseRows(Table.FillDown(Table.ReverseRows(Table.FillDown(Source,{"Region","Style","Size"})),{"Region","Style","Size"}))

[PhilipTreacy]

Hi @Anonymous 

You can do this by sorting columns, making sure that the text you want to fill down is always at the top of the relevant column, whilst maintaining sorting on your Project Name column.

Here's a sample PBIX file and the query.

NOTE: If your columns contain empty strings "" then you need to replace them with null for this to work.  That's what I've done in the 3rd step of the query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcnb0VdJRMtQ3AiIDIwMg2zGtKDM5EcgAIp/EovRUpVgdmDozfUMDmDog8s1PSS3KA7ERSoAKjJGUoBji6OQMtgyuwC+/qCRDwTE3FWolqoEQ5cb6hqaoBgbnJubkIKkw1zfH5iiEMhd/PyDfCEUZzBKIHNBFJvAAKM4EucU5J7G4ODMZVR1QkTEux0BUmGI4Vyk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, Date = _t, Region = _t, Style = _t, Size = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Date", type text}, {"Region", type text}, {"Style", type text}, {"Size", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Region", "Style", "Size"}),
    #"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Project Name", Order.Ascending}, {"Region", Order.Descending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Region", "Style"}),
    #"Sorted Rows1" = Table.Sort(#"Filled Down",{{"Region", Order.Ascending}, {"Size", Order.Descending}}),
    #"Filled Down1" = Table.FillDown(#"Sorted Rows1",{"Size"})
in
    #"Filled Down1"

 Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.