Forum Discussion

cflynn_29's avatar
cflynn_29
Helper I
4 years ago
Solved

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

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

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

4 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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's avatar
      cflynn_29
      Helper I
      4 years ago

      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's avatar
        AlexisOlson
        Super User
        4 years ago

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

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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.