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
v-danhe-msft's avatar
v-danhe-msft
Microsoft Employee
7 years ago

Hi nirvana_moksh,

Based on my test, you could refer to below steps in query editor:

1.Copy [AMOUNT 1] and fill down the copy columns:

2.Create a custom column with below code:

if [AMOUNT 1]=null and [BRANCH SECTION]="T1" then null else [#"AMOUNT 1 - Copy"]

You could also download the pbix file to have a view.

 

Regards,

Daniel He

  • 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"