CODEWITHSUNDEEP
powerbipowerquery
Koycho Ganchev
Koycho Ganchev

Reputation: 9

Replace null values using key column in power query/power bi

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?

enter image description here

Thanks for the support!

Upvotes: 0

Views: 133

Answers (2)

horseyride
horseyride

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"

enter image description here

Upvotes: 0

mkRabbani
mkRabbani

Reputation: 16918

You can select the column and any of the option up or down to fill as shown in the below image.

enter image description here

Find more details here: https://learn.microsoft.com/en-us/power-query/fill-values-column

Upvotes: 0

Related Questions