Forum Discussion

nirvana_moksh's avatar
nirvana_moksh
Impactful Individual
7 years ago

Fill with Previous Value

Hello All,   Below is the sample data that I have at hand. The data in large consists of Head --> Branch --> Branch Section which can be seen as the hierarchy even. What I am trying to achieve for ...
Need Help
Show and Tell
nirvana_moksh's avatar
nirvana_moksh
Impactful Individual
7 years ago

v-danhe-msft - But this won't be optimal in regards to when I integrate this to the whole data set, as that includes 1k+ branches and multiple Branch Sections as well. Also, wont filling from previous value cause wrong filling of data, for instance if last row of Branch 1 has a value and then Branch 2 starts fresh it will inherit the value of Branch 1.

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
7 years ago

nirvana_moksh

 

This is one way in Power Query.

Basically we have to do a "GroupBy" branch step first and then apply the 'FillDown" function

 

Please see attached file as well for the steps

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZDNCoMwEIRfRXL2sDvZ1Hi0XjwVLL2J7/8a3Y1o2/WnEDIKH8OXmabQcajD/dk9+qGyz5ddeiDppjFyBeImzLWiOEYNwwVm/5yINCKXGOMfXFA4Q+UL9a5xdV1ai2u+RpMQNuETVkyYSmfmBptwPhY2PDrhfTPcYifDwi3GS/HPwL7V3sa5bTXSfl9Py8dh3XZ+Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [HEAD = _t, BRANCH = _t, #"BRANCH SECTION" = _t, #"AMOUNT 1" = _t, #"AMOUNT 2" = _t, #"QUARTER YEAR" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"HEAD", type text}, {"BRANCH", type text}, {"BRANCH SECTION", type text}, {"AMOUNT 1", Int64.Type}, {"AMOUNT 2", Int64.Type}, {"QUARTER YEAR", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"BRANCH"}, {{"All_Rows", each Table.FillDown(_,{"AMOUNT 1","AMOUNT 2"}), type table}}),
    #"Expanded All_Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All_Rows", {"HEAD", "BRANCH SECTION", "AMOUNT 1", "AMOUNT 2", "QUARTER YEAR"}, {"HEAD", "BRANCH SECTION", "AMOUNT 1", "AMOUNT 2", "QUARTER YEAR"})
in
    #"Expanded All_Rows"