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
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
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
Sorry, I don't understand why what I suggested doesn't work with your "filtering mechanism".
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.