cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CarlVisser Occasional Visitor
Occasional Visitor

Replace blank values with the value of the row above

Hi all, I recieve a CSV in with different headings and sub headings. 

the are a varrying number of headings and a varrying number of subheadings for each heading

column1           column2 

 

heading1           

subheading1       data

subheading2       data

subheading3       data 

heading2

subheading4       data

subheading5       data

subheading6       data 

subheading7       data 

 

I need to extract the headings and append them as the first column of each row

 

column1    column2             column3 

          

heading1   subheading1       data

heading1   subheading2       data

heading1   subheading3       data 

heading2   subheading4       data

heading2   subheading5       data

heading2   subheading6       data 

heading2   subheading7       data 

In the edit query stage i have tried to do this by:

#"Add Heading Column" = Table.AddColumn(Source, "Heading", each
    				if [Column2] = ""
    				then [Column1]
    				else ""),

 

column1    column2             column3 

          

heading1   subheading1       data

                  subheading2       data

                  subheading3       data 

heading2   subheading4       data

                  subheading5       data

                  subheading6       data

                  subheading7       data  

 

I then tried to replace the blank values with the row aboves value. Im struggling to find out how to reference this.

#"Replaced Value" = Table.ReplaceValue(#"Add Heading Column","",{Previous Rows Value],Replacer.ReplaceValue,{"Heading"})

Is there a way to refernce the previous rows value like this?

Is there possibly a better way to do this ? 

Any help would be much appreciated.

 

1 ACCEPTED SOLUTION

Accepted Solutions
konstantinos Senior Member
Senior Member

Re: Replace blank values with the value of the row above

Not exact how to refer to the previous row but at least it works for the expected result.

 

Add a custom column 

= Table.AddColumn(#"Changed Type", "Custom", each if [Column 2] = "" then [Comumn 1] else null)

1.PNG

 

 

 

Then select the custom column and Fill down from transform ribbon

 

2.PNG

 

 

Remove the blanks in the data column 2

 

3.PNG

 

 

 

You end up with the table you needed

 

Full M code

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8khNTMnMS1cwVNJRUorViVYKLk0CsVMSSxJhfCNkPkyDEZIGYzQNJnB+LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Comumn 1" = _t, #"Column 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Comumn 1", type text}, {"Column 2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column 2] = "" then [Comumn 1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column 2] = "data"))
in
    #"Filtered Rows"

 

 

Konstantinos Ioannou
1 REPLY 1
konstantinos Senior Member
Senior Member

Re: Replace blank values with the value of the row above

Not exact how to refer to the previous row but at least it works for the expected result.

 

Add a custom column 

= Table.AddColumn(#"Changed Type", "Custom", each if [Column 2] = "" then [Comumn 1] else null)

1.PNG

 

 

 

Then select the custom column and Fill down from transform ribbon

 

2.PNG

 

 

Remove the blanks in the data column 2

 

3.PNG

 

 

 

You end up with the table you needed

 

Full M code

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8khNTMnMS1cwVNJRUorViVYKLk0CsVMSSxJhfCNkPkyDEZIGYzQNJnB+LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Comumn 1" = _t, #"Column 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Comumn 1", type text}, {"Column 2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column 2] = "" then [Comumn 1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column 2] = "data"))
in
    #"Filtered Rows"

 

 

Konstantinos Ioannou

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 133 members 1,590 guests
Please welcome our newest community members: