Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Fill down feature in query editor not working as expected

Hi,

 

My fill down feature is not working as I want it to

Feature IDUser StoryCommence DateWhat Power BI Fill Down will doWhat I Want
FID0012002/9/20212/9/20212/9/2021
FID001202 2/9/20212/9/2021
FID001203 2/9/20212/9/2021
FID0022002/10/20212/10/20212/10/2021
FID0029999 2/10/20212/10/2021
FID0029999 2/10/20212/10/2021
FID0029999 2/10/20212/10/2021
FID003203 2/10/2021-
FID003204 2/10/2021-
FID003205 2/10/2021-
FID003206 2/10/2021-

 

I have the expected column and what power bi is doing right now.

I just want the fill down transformation of Power BI to reset at each Feature ID

 

Need Help
  • lbendlin's avatar
    lbendlin
    5 years ago

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvN0MTAwVNJRMjIwAJH6lvpGBkaGSrE6KHJGQFIBXdAYVdAIyRRDAzRjQJKWQICphVhRY2x2QgRNsAmaYhM0gwjGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Feature ID" = _t, #"User Story" = _t, #"Commence Date" = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Feature ID"}, {{"Data", each _, type table [Feature ID=nullable text, User Story=nullable text, Commence Date=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Data]{0}[#"Commence Date"]),
    #"Expanded Data" = Table.ExpandTableColumn(#"Added Custom", "Data", {"User Story", "Commence Date"}, {"User Story", "Commence Date"})
in
    #"Expanded Data"

     

     

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvN0MTAwVNJRMjIwAJH6lvpGBkaGSrE6KHJGQFIBXdAYVdAIyRRDAzRjQJKWQICphVhRY2x2QgRNsAmaYhM0gwjGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Feature ID" = _t, #"User Story" = _t, #"Commence Date" = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Feature ID"}, {{"Data", each _, type table [Feature ID=nullable text, User Story=nullable text, Commence Date=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Data]{0}[#"Commence Date"]),
    #"Expanded Data" = Table.ExpandTableColumn(#"Added Custom", "Data", {"User Story", "Commence Date"}, {"User Story", "Commence Date"})
in
    #"Expanded Data"

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks

      Can you also explain this step:

      [Data]{0}[#"Commence Date"]

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    After collapsing the subsections they are all tables called "Data".  From each table we grab the field "Commence Date" in the first row  (  {0}  ).  We then use that information to emulate the "fill-down by subsection"  behavior that you wanted to achieve

     

    { x }  = get row x

    [ y ] = get column y