How to fill Blank Row from left in POWER QUERY

[Saraaz21]

Dear All,

I have lots of blank cell I want fill these from left side date as per the shared images.

please help me if anyone want row data, I can share please help.

Thanks

Saraaz21@hotmail.com

[latimeria]

Hi @Saraaz21 ,

You can do it as proposed by @m_dekorte (The BICCOUNTANT) :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY07DgIxDETvknpl+ZP4cwA6OmhQtB01BXB/kTgIbYFk2U9jj6f3Qq932cooftwXyDNh33o53wadLtfR/+E8QTBpbQgE2jRnhBPVg4TggRpJEaJpZCbkuWVg9DlJjO0gEDhp/hGuvkxAEl+X1BVn4pn2U1popQkVbWTtHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column0 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Column0", "Column2", "Column3", "Column4", "Column5", "Column6"}),
    Mode = Table.FillDown,
    FillColumns = Table.ColumnNames(#"Replaced Value"),
    #"Added Custom" = Table.AddColumn(
        #"Replaced Value",
        "Custom",
        each Table.FromRows(
            {
                Function.Invoke(
                    Mode,
                    {
                        Table.FromColumns(
                            {Record.FieldValues(Record.SelectFields(_, FillColumns))}
                        ),
                        {"Column1"}
                    }
                )[Column1]
            },
            FillColumns
        )
    ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom", FillColumns),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", FillColumns),
    #"Reordered Columns" = Table.ReorderColumns(
        #"Expanded Custom",
        Table.ColumnNames(#"Replaced Value")
    )
in
    #"Reordered Columns"

Or by filling right the first column only:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY07DgIxDETvknpl+ZP4cwA6OmhQtB01BXB/kTgIbYFk2U9jj6f3Qq932cooftwXyDNh33o53wadLtfR/+E8QTBpbQgE2jRnhBPVg4TggRpJEaJpZCbkuWVg9DlJjO0gEDhp/hGuvkxAEl+X1BVn4pn2U1popQkVbWTtHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column0 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Column0", "Column2", "Column3", "Column4", "Column5", "Column6"}),
    FillColumns = Table.ColumnNames(#"Replaced Value"),
    FillDown = Record.FromList(
        Table.FillDown(
          Record.ToTable(#"Replaced Value"{0}), {"Value"}
        )[Value],
        FillColumns        
    ),
    #"Removed & replaced Top Rows" = Table.InsertRows(
        Table.Skip(#"Replaced Value",1),
        0,        
        {FillDown}
    )
in
    #"Removed & replaced Top Rows"

 Or simply transpose the table: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY07DgIxDETvknpl+ZP4cwA6OmhQtB01BXB/kTgIbYFk2U9jj6f3Qq932cooftwXyDNh33o53wadLtfR/+E8QTBpbQgE2jRnhBPVg4TggRpJEaJpZCbkuWVg9DlJjO0gEDhp/hGuvkxAEl+X1BVn4pn2U1popQkVbWTtHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column0 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Column0", "Column2", "Column3", "Column4", "Column5", "Column6"}),
    #"Transposed Table" = Table.Transpose(#"Replaced Value"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Transposed Table1" = Table.Transpose(#"Filled Down")
in
    #"Transposed Table1"

[Saraaz21]

Thank you so much. its ammazing.

[m_dekorte]

Hi @Saraaz21,

You can look into these:

excelcampus | Fill Across Blank Cells in Rows with Power Query 

The BICCOUNTANT | Fill values to the right in Power Query 

[Saraaz21]

Thank you so much bro ...