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
CarlVisser
New Member

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
konstantinos
Memorable Member
Memorable Member

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

View solution in original post

1 REPLY 1
konstantinos
Memorable Member
Memorable Member

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
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.