only fill down on row in power query

[woshi_eads]

Hi All,

my data is like 

Column1	Column2	Coumn3	col4	col4	col5	col6
b	c	a	b	a	s	1
	c	a	n	c	d	2
					f

the output should be like this

Column1	Column2	Coumn3	col4	col4	col5	col6
b	c	a	b	a	s	1
b	c	a	n	c	d	2
	c	a	n	c	f	2
					f

Please note I have a lot columns in my scenario, using if is not possible here like another postSolved: Fill up only one row - Microsoft Power BI Community

Tks in advance and happy lunar new year 🙂

[Ahmedx]

Sample PBIX file attached
https://dropmefiles.com/JMSJC

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlLSUUoG4kQgToLSxUBsqBSrE62kgCSbB2WnALERXBYdpwGxUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Coumn3 = _t, col4 = _t, col4.1 = _t, col5 = _t, col6 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Coumn3", type text}, {"col4", type text}, {"col4.1", type text}, {"col5", type text}, {"col6", Int64.Type}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type any}}),
    Trans = Table.Transpose(#"Changed Type1"),
    #"Changed Type2" = Table.TransformColumnTypes(Trans,{{"Column3", type text}}),
    #"Replaced 1" = Table.ReplaceValue(#"Changed Type2"," ",(x)=> x[Column2],Replacer.ReplaceText,{"Column3"}),
    #"Replaced 2" = Table.ReplaceValue(#"Replaced 1"," ",null,Replacer.ReplaceValue,{"Column4"}),
    #"Added Custom" = Table.AddColumn(#"Replaced 2", "Custom", each [Column4]),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom",null,(x)=> x[Column3],Replacer.ReplaceValue,{"Column4"}),
    #"Transposed Table" = Table.Transpose(#"Replaced Value"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Coumn3", type text}, {"col4", type text}, {"col4.1", type text}, {"col5", type text}, {"col6", Int64.Type}})
in
    #"Changed Type3"

[Ahmedx]

fixed the code a bit
https://dropmefiles.com/kTKac

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlLSUUoG4kQgToLSxUBsqBSrE62kgCSbB2WnALERXBYdpwGxUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Coumn3 = _t, col4 = _t, col4.1 = _t, col5 = _t, col6 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Coumn3", type text}, {"col4", type text}, {"col4.1", type text}, {"col5", type text}, {"col6", Int64.Type}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type any}}),
    Trans = Table.Transpose(#"Changed Type1"),
    #"Changed Type2" = Table.TransformColumnTypes(Trans,{{"Column3", type text}}),
    #"Replaced 1" = Table.ReplaceValue(#"Changed Type2"," ",(x)=> x[Column2],Replacer.ReplaceText,{"Column3"}),
    #"Replaced 2" = Table.ReplaceValue(#"Replaced 1"," ",null,Replacer.ReplaceValue,{"Column4"}),
    #"Added Custom" = Table.AddColumn(#"Replaced 2", "Custom", each [Column4]),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom",null,(x)=> x[Column3],Replacer.ReplaceValue,{"Column4"}),
    #"Transposed Table" = Table.Transpose(#"Replaced Value"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Coumn3", type text}, {"col4", type text}, {"col4.1", type text}, {"col5", type text}, {"col6", Int64.Type}})
in
    #"Changed Type3"

[Thennarasu_R]

Hi @woshi_eads 
you have options in querry editor choose fill down option . It will automatically updated the value.

Your should have the Numeric format. Then You can use. if have Text It won't Work.

Refference Pic:

Thanks,
Thennarasu

[NikhilChenna]

Hi  @woshi_eads, 

I think you can find your solution in this below links,

https://www.youtube.com/watch?v=90EYX7pzVlE

https://www.youtube.com/watch?v=Hc3d8rMSXcQ

Regards, 
Nikhil Chenna 

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!