Reply
ferby2023
Frequent Visitor

Very Specific Conditional Fill Down

Hi guys, i think the picture explains itself:

ferby2023_3-1723048589333.png


Basically I am trying to fill down based on an ID column.. i tried lot of thing, even aggregation but i could find out how to solve, any ideais on how to do this?

The values in column "VALUE" are very random. it comes in different months..

1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

Hi @ferby2023, check this:

 

Result

dufoq3_0-1723049602851.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc4xDsAgDEPRu2RmIKZAu3fpGRD3v0aViko2A8O3FPTGsOd2SxbPZvoTmkXz0KxxkGloccJDjyMeTv3h0vS89aZz4kHxUDwUD8Vj4SmbZtcMtlcaAg4ePnqRZdMt/HwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Tag = _t, Month = _t, Value = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"Value", type number}}),
    GroupedRows = Table.Group(ChangedType, {"Tag"}, {{"All", each Table.FillDown(_, {"Value"}), type table}}),
    Combined = Table.Combine(GroupedRows[All])
in
    Combined

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

2 REPLIES 2
dufoq3
Super User
Super User

Hi @ferby2023, check this:

 

Result

dufoq3_0-1723049602851.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc4xDsAgDEPRu2RmIKZAu3fpGRD3v0aViko2A8O3FPTGsOd2SxbPZvoTmkXz0KxxkGloccJDjyMeTv3h0vS89aZz4kHxUDwUD8Vj4SmbZtcMtlcaAg4ePnqRZdMt/HwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Tag = _t, Month = _t, Value = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"Value", type number}}),
    GroupedRows = Table.Group(ChangedType, {"Tag"}, {{"All", each Table.FillDown(_, {"Value"}), type table}}),
    Combined = Table.Combine(GroupedRows[All])
in
    Combined

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

thanks for helping.. I was able to paste the code, but unfortunately when I clicked on Save the query never stopped. 

Waiting for other queries... for at least 40 min.

My ID tag is very very very big, because it is a concatenation of many columns.

EDIT: after +- 1 hour it worked! 

@dufoq3  many thanks.. !

avatar user

Helpful resources

Announcements
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!

FebPBI_Carousel

Power BI Monthly Update - February 2025

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

Feb2025 NL Carousel

Fabric Community Update - February 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)