Forum Discussion

Rich_P's avatar
Rich_P
Helper II
6 years ago
Solved

FillDown with a Calculation

I'm not quite sure the Subject is descriptive enough, so I'll try to explain with images. I have a data feed that displays Subtotals on the first record of a group of records and I need to display t...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    This code will handle the task in one query. It shows how you can join from one step into another.

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MaterialCode", type text}, {"ProdSequence", type text}, {"Operation", type text}, {"Material", type any}, {"Qty", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Header", each if [Qty] = null then null else [Index]),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Header"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Header"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Filled Down",{"Header"},#"Grouped Rows",{"Header"},"Grouped Rows",JoinKind.LeftOuter),
    AddQtyAdjust = Table.AddColumn(#"Merged Queries", "QtyAdjust", each [Qty]/[Grouped Rows][Count]{0}),
    #"Filled Down1" = Table.FillDown(AddQtyAdjust,{"Material", "QtyAdjust"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Filled Down1",{"MaterialCode", "ProdSequence", "Operation", "Material", "QtyAdjust"})
in
    #"Removed Other Columns"

     

     

     

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi Rich_P ,


Sorry for that, We cannot understand your data model clearly, Could you please show the exact expected result based on the Tables that you have shared? Please don't have any Confidential Information or Real data in your reply.


Best regards,

 

  • Rich_P's avatar
    Rich_P
    Helper II
    6 years ago

    Thanks for looking at this. I couldn't figure out how to get my illustration images included in my post. And then it was marked as SPAM so I figured it wouldn't be seen anyway.

     

    I have updated the Excel file behind the link to explain what I am looking to do.

     

    Hopefully it will make it easier to understand.

     

    Thanks.

     

    Rich P

    • Jimmy801's avatar
      Jimmy801
      Community Champion
      6 years ago
      Hello Rich_P

      Quite tricky request. I like them.
      But to prevent any exaggerated effort...
      How big is the database? What it's the real goal of having this? Would that be the only way to reach the goal? Benefits?
      Hope you understand this. I did once a huge programming only to be told..." ooh too complicated.. i did a replace in Excel"
      Have a nice time
      Jimmy
      • Rich_P's avatar
        Rich_P
        Helper II
        6 years ago

        Very practical response! This is NOT  a must-have solution by any stretch. I was able to do what I needed in a manner that I thought was ameturish at best. I was really hoping to see how some of you professionals would tackle the same problem. 

         

        I had visions of nested let/in stubs, or possibly a separate function to get the Divisor.

         

        I can almost 'see it' in my head. But I struggle with details of these concepts in spite of the hours of reading and YouTube videos.

         

        Please don't waste any time on this if it's too difficult. It was a one-time task (though it turns out I had to run it again this morning!) I thought would be a great example that I could wrap my head around.

         

        Thanks for the response anyway! I appreciate it.

         

        Cheers.

         

        Rich P