Power Query | COALESCE Table and Replace all first row values in Table

[cflynn_29]

I have a Table and all of the first row has values that i would like to promote but Column "Data.Column7" has a bunch of nulls before the first value and with the data structure the way it is i would like to COALESCE the table and grab all of those first non-null values and then replace all first row values in the Table with those COALESCE value wheter its null or not and the Promote the first row to a header.

Any assitance would be most welcomed 

Thank you

[v-yanjiang-msft · Accepted solution]

Hi @cflynn_29 ,

According to your description, here's my solution.

1.Add an index column.

2.Add a custom column.

Custom = if[Index]=0 then List.First(List.RemoveItems(#"Added Index"[Column3],{""})) else [Column3]

Then the title reaches the first row.

Remove Index and Column3, get the expected result.

Here's the code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsksyUlV0oHTSrE60UogGso0BDJMYRwjIMMMrhgkYgzkmcOkTYAMCyC2VIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if[Index]=0 then List.First(List.RemoveItems(#"Added Index"[Column3],{""})) else [Column3]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column3", "Index"})
in
    #"Removed Columns"

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

Instead of promoting headers, you could instead just rename the columns directly.

Try this:

let
    Source = Table.FromRows({{"Title1","Title2",null}, {1,2,null}, {3,4,"Title3"}, {"Title", "Title", null}}),
    FirstNonNulls = List.Transform(
        Table.ColumnNames(Source),
        each List.First(List.RemoveNulls(Table.Column(Source, _)))
    ),
    RenameCols = Table.RenameColumns(Source, List.Zip({Table.ColumnNames(Source), FirstNonNulls}))
in
    RenameCols

[cflynn_29]

I am using those "Titles" that are promoted as a filtering mechanism, if the Column Names dont have "Title" in it they get filtered. That way when data is added it becomes automatic. The reason the Column 7 has information so far down just the nature of how the data is being entered. 

But if can get that First non-null value you in each column and move it to the first row in the same column then its all good

[AlexisOlson]

Sorry, I don't understand why what I suggested doesn't work with your "filtering mechanism".