Forum Discussion
Group by Rows then FillUp & FillDown for multiple columns
- 5 years ago
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"}))
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!!
- shaowu4595 years agoResolver II
Glad we could help😋