Repeating values in cells of columns (horizontally) based on a value in the column next to it

[Haleem]

I want something the results as shown in the 2nd table.

if columns are empty, it should be repeating values horizontally from the previous cell on the left side of it. 

[mahoneypat · Accepted solution]

You could try to transpose, fill down, transpose to get your desired result.  Here is an example query with similar data.  Note it also requires a step potentially to replace blanks with null to enable fill down.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXICYhCK1UHwnZEFcMi7KMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Level1 = _t, Level2 = _t, Level3 = _t, Level4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Level1", type text}, {"Level2", type text}, {"Level3", type text}, {"Level4", type text}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Replaced Value" = Table.ReplaceValue(#"Transposed Table","",null,Replacer.ReplaceValue,{"Column2", "Column3", "Column4", "Column5"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Column2", "Column3", "Column4", "Column5"}),
    #"Transposed Table1" = Table.Transpose(#"Filled Down"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Level1", type text}, {"Level2", type text}, {"Level3", type text}, {"Level4", type text}})
in
    #"Changed Type1"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat