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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TommyMossberg
Helper I
Helper I

New column - If value = null, use value on previous row.

I got data from my source in a csv formated like this.

 

Got thisGot this
4001Value
4002Value
 Value
4003Value
 Value
 Value
 Value
4004Value
 Value
 Value
 Value
 Value
 Value

 

In order to connect it to my other data I need it like this

Got thisWant thisGot this
40014001Value
40024002Value
 4002Value
40034003Value
 4003Value
 4003Value
 4003Value
40044004Value
 4004Value
 4004Value
 4004Value
 4004Value
 4004

Value

 

How do I create the calculated(?) column in the example above?

 

Thanks in advance!

1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @TommyMossberg 

Just use the Fill down feature. Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjEwMFTSUQpLzClNVYrVAQsYoQgooEsb45HGxwNqNSFTK05eLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Got this" = _t, #"Got this.1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Got this", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Got this]),
    #"Filled Down1" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down1",{"Got this", "Custom", "Got this.1"})
in
    #"Reordered Columns"

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

View solution in original post

1 REPLY 1
AlB
Super User
Super User

Hi @TommyMossberg 

Just use the Fill down feature. Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjEwMFTSUQpLzClNVYrVAQsYoQgooEsb45HGxwNqNSFTK05eLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Got this" = _t, #"Got this.1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Got this", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Got this]),
    #"Filled Down1" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down1",{"Got this", "Custom", "Got this.1"})
in
    #"Reordered Columns"

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors