Reputation: 9
My question concerns power query editor of Excel and Power BI as well. is there a way to replace the null values and fill down/up the cells in with the value from the column?
Thanks for the support!
Upvotes: 0
Views: 133
Reputation: 21428
Try this
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"KEY"}, {{"data", each Table.FillUp(Table.FillDown(_,{"Quantity", "variable"}),{"Quantity", "variable"}), type table }}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"KEY"}),
List = List.Union(List.Transform(#"Removed Columns"[data], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "data", List,List),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Data",Table.ColumnNames(Source)),
#"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"cost", type number}, {"fixed", type number}, {"KEY", type number}, {"Quantity", type number}, {"variable", type number}})
in #"Changed Type"
Upvotes: 0
Reputation: 16918
You can select the column and any of the option up or down to fill as shown in the below image.
Find more details here: https://learn.microsoft.com/en-us/power-query/fill-values-column
Upvotes: 0