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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
kumsha1
Post Patron
Post Patron

FillDown for each group

Hi,

 

I did a group by columns and FillDown on a conditional column, however the FillDown is not happening for each group...Can someone help with powerquery to FillDown for each group (SHIPMENT_NUMBER). TIA for your help !

 

kumsha1_0-1605683241613.png

 

#"Grouped Rows" = Table.Group(Source, {"TS_SHIPMENT_NUMBER", "TRANSHIPPERSHORTNAME", "TS_SHIP_NAME", "TS_STARTDATE_UTC"}, {{"LINES", each _, type table [TRANSHIPPERSHORTNAME=nullable text, FROM_DATE=nullable datetime, TS_END_DATE_UTC=datetime, COMMENT=nullable text, TS_SHIP_NAME=nullable text, TS_STARTDATE_UTC=datetime, TS_SHIPMENT_NUMBER=nullable text, TRANSHIPPERNAME=text, HATCH=nullable text, BARGE_NO=nullable number, PROGRESSIVE_TON_START=nullable number, PROGRESSIVE_TONNES=nullable number]}}),
#"Expanded LINES" = Table.ExpandTableColumn(#"Grouped Rows", "LINES", {"FROM_DATE", "TS_END_DATE_UTC", "COMMENT", "TRANSHIPPERNAME", "HATCH", "BARGE_NO", "PROGRESSIVE_TON_START", "PROGRESSIVE_TONNES"}, {"FROM_DATE", "TS_END_DATE_UTC", "COMMENT", "TRANSHIPPERNAME", "HATCH", "BARGE_NO", "PROGRESSIVE_TON_START", "PROGRESSIVE_TONNES"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded LINES", "LINES", each if [PROGRESSIVE_TONNES] = null then [PROGRESSIVE_TON_START] else [PROGRESSIVE_TONNES]),
#"Replaced Value" = Table.ReplaceValue(#"Added Conditional Column",0,null,Replacer.ReplaceValue,{"LINES"}),
#"Filled Down" = Table.FillDown(#"Replaced Value",{"LINES"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each [TS_STARTDATE_UTC] = #datetime(2020, 10, 26, 0, 0, 0))
in
#"Filtered Rows"

5 REPLIES 5
Quentin
Helper III
Helper III

Hello over there,

Have you managed to make this? 
This is exactly the thing i want indeed 🙂 
Thank you in advance

v-yingjl
Community Support
Community Support

Hi @kumsha1 ,

Based on your description and data sample, not certain what is your expected output. Which column you want to fill down based on each group? Could you please consider sharing more details about this issue for further discussion?

 

Best Regards,
Community Support Team _ Yingjie Li

Jimmy801
Community Champion
Community Champion

Hello @kumsha1 

 

I can't see the data source but I suppose that the cell value is "null" and not null. Fill-Down works only with datatype null. So you can do a replacevalue to replace "null" with null.

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Hi @Jimmy801 ,

 

There is no issue with null. FillDown is happening for entire table and i want this to happen for each group of the data.

Jimmy801
Community Champion
Community Champion

Hello @kumsha1 

 

sorry, I cannot follow you. Can you share some data, some screenshots and what exactly you are missing?

 

BR

 

Jimmy

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors