Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Rich_P
Helper II
Helper II

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 the line item detail amount for each record in that group.

 

The distribution will always be linear, for example, if the subtotal is 100, and there are 10 lines, then each line would be 1.

 

I worked out a clunky way to do this using a couple of separate queries and merging them (one with the data, the other with the number to divide by) and creating a new column with the result that I fill down. But this seems so inelegant compared to the incredible M examples I have seem people write. I really want to up my game with this stuff and I'm hoping this will really jump start me.

 

Maybe this image will better describe what I'm trying to do:

 

I have a small Excel file with a single Query that returns the Divisor needed to act on the subtotal. But that's as far as I could get.

 

I KNOW it can be done in one query, I just have no idea where to begin.

 

Thanks,

 

Rich P

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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"

 

 

 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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"

 

 

 

MC - 

This is exaclty what I was hoping for. Such a simple, elegant solution. And a great example of how to leverage results from previous steps.

 

Thanks for taking the time to do this.

 

Happy Holidays!


RichP

v-lid-msft
Community Support
Community Support

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
Community Champion
Community Champion

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

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.